From: Scott Haneda (listsnewgeo.com)
Date: Thu Jul 01 2004 - 19:57:52 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

on 07/01/2004 05:43 PM, Scott Haneda at listsnewgeo.com wrote:

> I am not entirely sure what I am after is possible, if someone could help me
> I sure would appreciate it. Mysql 4.0.18-standard
>
> I have 2 basic selects I am doing:
> SELECT id, image_title, 'images'
> FROM newsletter_images
>
> SELECT id, link_title, 'links'
> FROM newsletter_links
>
> In the end, I get 2 separate result sets, each with 3 items in it, and how
> ever many records there are. Notice I stuffed in the 2 strings in the
> select of 'images' and 'links' so I can know which are which.
>
> My list of results look something like this:
> For Images:
> [ [ "3", "this is a test", "images" ], [ "2", "lock", "images" ] ]
>
> For Links:
> [ [ "1", "linky", "links" ], [ "2", "this is a link title", "links" ] ]
>
> I want to merge those to result sets into one, which is rather simple to do
> on the server in script, I want to do it in SQL though, to save the second
> select from happening, I also want them ordered by the image_title and
> link_title, but grouped in a way that I get all the 'images' first, then all
> the 'links', so in the above example, I would end up with a result set that
> was like this:
>
> [ [ "2", "lock", "images" ], [ "3", "this is a test", "images" ], [ "1",
> "linky", "links" ], [ "2", "this is a link title", "links" ] ]

Someone pointed me to UNION, which I think will do this, so I came up with:
(select id, image_title, 'image' from newsletter_images ORDER BY
image_title) UNION (select id, link_title, 'links' from newsletter_links
ORDER BY link_title);

Not sure if I need a GROUP BY or if that is even possible in UNION, perhaps
the grouping happens just by the order of my union'd selects?
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
http://www.newgeo.com Fax: 313.557.5052
scottnewgeo.com Novato, CA U.S.A.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]