Page 1 of 1

MySQL: Selection from multiple tables

Posted: Mon Oct 18, 2004 12:00 am
by curseofthe8ball
I've got three tables which all use a slightly different table structure but have a few similar columns. What I need to do is have a Query that looks through EACH of the three tables for rows that have a status of active and a spotlight of on. Then once I've got those rows, I need to order it via date_full.

I've never had to do a large query involving multiple tables before but I had originally thought the below would work, but unfortunately it doesn't:

Code: Select all

SELECT * FROM community_events, knowledge_base, press_releases 
WHERE status = 'active' and spotlight = 'on' 
ORDER BY 'date_full' DESC
I get the following error:

"#1052 - Column: 'STATUS' in where clause is ambiguous".

Obviously this is because the status (and spotlight) column is in each table. How do I get around this? I'd like someone to aid me in writing the query, but also some explanation of what the query does would be helpful in helping me learn a bit more about this.

Posted: Mon Oct 18, 2004 12:13 am
by feyd
what you are describing isn't done normally though joins. I'd look into [mysql_man]UNION[/mysql_man] ... and make sure to query the largest (in column count) table first, or you'll run into problems.