MySQL: Selection from multiple tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

MySQL: Selection from multiple tables

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply