Can't perform a UNION ALL MySQL query

Can't perform a UNION ALL MySQL query - Printable Version

+- 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: Can't perform a UNION ALL MySQL query (/showthread.php?tid=20958)

Pages: 1 2

Can't perform a UNION ALL MySQL query - El Forum - 07-27-2009

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

Can't perform a UNION ALL MySQL query - El Forum - 07-27-2009

[eluser]David Johansson[/eluser]
[quote author="Ci beginner" date="1248724703"][quote author="Colin Williams" date="1248684219"]You need to tell the DB class to not protect identifiers by supplying a third param of TRUE.

Code:
$this->db->where('UNION ALL "

        SELECT * FROM `blog` ORDER BY `id` DESC

        UNION ALL

        SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"', NULL, TRUE);

[/quote]

Oops, bad news... I've tried the code:

Code:
$this->db->select('blog_comentarios.post_id, blog.id');

  $this->db->from('blog_comentarios, blog');

$this->db->where('UNION ALL "

  SELECT * FROM `blog` ORDER BY `id` DESC

  UNION ALL

  SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"', NULL, TRUE);  

  $data['query'] = $this->db->get();

And same error here:

Quote:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALL " SELECT * FROM `blog` ORDER BY `id` DESC UNION ALL SELECT COUNT(*) FROM ' at line 3

SELECT `blog_comentarios`.`post_id`, `blog`.`id` FROM (`blog_comentarios`, `blog`) WHERE `UNION` ALL " SELECT * FROM `blog` ORDER BY `id` DESC UNION ALL SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"

What can I do? Thank you![/quote]

1: The third parameter must be false, not true.
2: Since active record is not that complicated it's probably better to use regular queries.
3: I don't think you should have the citation marks (") around the second query, maybe you should use brackets instead or it might even work without anything...

Can't perform a UNION ALL MySQL query - El Forum - 07-27-2009

[eluser]David Johansson[/eluser]
I'm not sure how you like your result, but it looks like your trying to make the following:

Code:
$this->db->query('(SELECT `post_id` as `id` FROM `blog_comentarios`) UNION ALL (SELECT `id` FROM `blog`) ORDER BY `id` DESC');