[SOLVED] Grabbing from 2 tables duplicates the rows about 10 times

[envexlabs]

Hello,

 

Basically, i have 2 tables. events and specials.

 

These are 2 separate tables with no linking elements. I want to grab from both because i want to display a list of the 30 newest events and specials created, sorted by date.

 

Here is the table structures:

 

SPECIALS
-------------------------------
id
venue_id
title
more_info
created

EVENT
-------------------------------
id
venue_id
fb_eid
title
date
poster_thumb
poster_large
buy_link
more_info

 

The only thing these two tables share is the venue_id.

 

Here is the query:

SELECT * FROM events, specials 
WHERE events.venue_id 
IN (1,20)
AND specials.venue_id
IN (1,20)

 

I'm obviously not grasping this concept, or doing something completely wrong, but i can't seem to figure out what.

 

Can anyone shed any light on this subject?

 

Thanks!

[gizmola]

So what you want to do is a Union.  In order to get a union to work the result sets have to be the same on both queries.  Depending on what columns you need from both tables you may need to manufacture columns in your select statement. 

 

The main trick here,  is that you want to ORDER BY the date in question, but it seems that the two tables have different names for that column: one is 'created', and the other is 'date'.  So you'll need to alias the columns so that they have the same name in the union.

 

So something like this:

 

SELECT title, created FROM specials
UNION
SELECT title, date as created FROM events
ORDER BY created DESC
LIMIT 0, 30

[envexlabs]

Thanks,

 

I looked into union before, but it just looked like it was grabbing all the events and then the specials.

 

I'll give this a shot.

[envexlabs]

Worked like a charm!

 

Thanks.