Welcome Guest, Not a member yet? Register   Sign In
Can't perform a UNION ALL MySQL query
#11

[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.


Messages In This Thread
Can't perform a UNION ALL MySQL query - by El Forum - 07-26-2009, 09:25 PM
Can't perform a UNION ALL MySQL query - by El Forum - 07-26-2009, 09:43 PM
Can't perform a UNION ALL MySQL query - by El Forum - 07-26-2009, 09:55 PM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 08:58 AM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 10:35 AM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 11:18 AM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 11:25 AM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 12:15 PM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 12:55 PM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 01:05 PM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 02:24 PM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 07:01 PM
Can't perform a UNION ALL MySQL query - by El Forum - 07-27-2009, 07:08 PM



Theme © iAndrew 2016 - Forum software by © MyBB