on 07/01/2004 05:43 PM, Scott Haneda at [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
