[eluser]kgill[/eluser]
You want a join/subquery at that point, you're using unions the entirely wrong way. Unions are meant for combing the results of two selects into one bigger result set.
E.g. Suppose you had two tables, students and teachers and each table has the following columns: name, phone_num and address. If you wanted to publish a contact list, you'd need to query both the student table and the teacher table.
So:
Code:
// pseudo-code follows
$teacher_list = $db->query("select name, phone_num from teachers")
echo 'teachers:'
foreach ($teacher_list->result() as $teacher) {
echo $teacher->name . ' ' . $teacher->phone_num
}
$student_list = $db->query("select name, phone_num from students")
echo 'students:'
foreach ($student_list->result() as $student) {
echo $student->name . ' ' . $student->phone_num
}
// produces
teachers:
blah, blah 123-123-1234
blah, blah 123-123-1234
students:
blah, blah 123-123-1234
blah, blah 123-123-1234
blah, blah 123-123-1234
However, since you're working with the essentially the same information you can simplify things a bit and use a union.
Code:
// pseudo-code follows
$list = $db->query("select 'teachers' as type, name, phone_num from teachers
UNION
select 'students' as type, name, phone_num from students")
$type = ''
foreach ($list->result() as $person) {
if $person->type != $type {
echo $person->type .':'
$type = $person->type
}
echo $person->name . ' ' . $person->phone_num
}
// produces
teachers:
blah, blah 123-123-1234
blah, blah 123-123-1234
students:
blah, blah 123-123-1234
blah, blah 123-123-1234
blah, blah 123-123-1234
This is obviously a simplified example but you hopefully get the point - each method produces the same result but using a union you only make one query to the DB. Now that you understand what unions are for, let's work on your terminology - unions are what you see above, what you're really referring to are called joins not unions. Below is an example of how to accomplish what you want, don't just take it and adapt it, if you really want to be a better programmer - read up on joins as well as subqueries so that you understand why this works.
Code:
//How to do it all via SQL
select post_id, post_title, post_count
from
( // get the comment count for the top 5 posts
select post_id, count(comments) as post_count
from blog_posts, blog_comments
where blog_posts.post_id = blog_comments.post_id
group by blog_posts.post_id
limit 5
) as subq left join blog_posts on subq.post_id = blog_posts.post_id
// join the subquery back to the original table
// that way you can get other details from the blog table
// without worrying about if their lack of uniqueness will
// screw up the count
By the way the above query could still have been rewritten to do things as separate queries, you get the top 5 blog posts first and then loop through them and query the DB to get the comment counts for each post. Doing it that way is inefficient but its possible.
