+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL: aliased fields return value of just one field (/showthread.php?tid=5661)
MySQL: aliased fields return value of just one field - El Forum - 01-30-2008
[eluser]codex[/eluser]
Consider this query:
Code:
SELECT fc.*, COUNT( fp.forum_post_id ) AS total_posts,
COUNT( fp2.forum_post_id ) AS total_replies
FROM forum_categories AS fc
LEFT JOIN forum_posts AS fp ON fp.forum_cat_id = fc.forum_cat_id AND fp.post_parent_id = 0
LEFT JOIN forum_posts as fp2 ON fp2.forum_cat_id = fc.forum_cat_id AND fp2.post_parent_id != 0
GROUP BY fc.forum_cat_idtotal_posts and total_replies should yield different results, but instead I get the same result for both. Obviously there's something not quite right with this query, but I don't understand what. I have the 2 aliased differently, so what could be the problem?
Are AND's allowed in joins?
MySQL: aliased fields return value of just one field - El Forum - 01-30-2008
[eluser]BizComputing[/eluser]
I suspect your counts are total of fc recs.
Your joins are LEFT JOINS, so count will count NULL fp.forum_post_id's for non-hits
I seem to recall encountering this problem before, but for the life of me I can't remember how I resolved it.
MySQL: aliased fields return value of just one field - El Forum - 01-30-2008
[eluser]codex[/eluser]
[quote author="BizComputing" date="1201739850"]I suspect your counts are total of fc recs.
Your joins are LEFT JOINS, so count will count NULL fp.forum_post_id's for non-hits
I seem to recall encountering this problem before, but for the life of me I can't remember how I resolved it.[/quote]
I actually got it solved. I was doing an all wrong query. But thanks anyway!
(and on to the next problem)
MySQL: aliased fields return value of just one field - El Forum - 01-30-2008
[eluser]BizComputing[/eluser]
If you don't mind me asking, what was the query that worked?
I was thinking problem could have been solved with 1 join and the COUNT against a CASE WHEN
MySQL: aliased fields return value of just one field - El Forum - 01-30-2008
[eluser]codex[/eluser]
[quote author="BizComputing" date="1201741146"]If you don't mind me asking, what was the query that worked?
I was thinking problem could have been solved with 1 join and the COUNT against a CASE WHEN[/quote]
Well, I completely overlooked the fact that I had stored the total_replies in an own field, making the query I was looking for totally irrelevant. I just grabbed that field and that was it (basically). So in short: I can share the query with you, but I doubt you'll gain anything from it
