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:

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.

    hi keith!
    it is always good to mention every field you want to get. but your problem is firstly that you have to say from
    which table you want to check the status.
    you do this by mentioning the table in front of the field: table.field
    something like this:

    SELECT community_events.field1, knowledge_base.field3, press_releases.field3 [...] 
FROM community_events, knowledge_base, press_releases 
WHERE community_events.status = 'active' and knowledge_base.spotlight = 'on' 
ORDER BY 'date_full' DESC

    also it is faster to use a INNER JOIN for this (you do an EQUI JOIN), i do not know your table structure,
    but this kind of query looks like this:

    SELECT community_events.field1
FROM community_events
INNER JOIN knowledge_base
ON community_events.fieldx = knowledge_base.fieldy
INNER JOIN press_releases 
ON knowledge_base.fieldz = press_releases.fieldz
WHERE community_events.status = 'active' and knowledge_base.spotlight = 'on' 
ORDER BY 'date_full' DESC

      If I did understand things right, you want to retrieve the same kind of data from 3 different tables, each of them havin a column "status" and a column "spotlight", and a column "date_full".

      I doubt that the proposed solution will work. I'd rather suggest the following: make a union of three different queries:

      SELECT field1, field2, date_full FROM community_events
      WHERE status = 'active' and spotlight = 'on'
      union
      SELECT field1, field2, date_full FROM knowledge_base
      WHERE status = 'active' and spotlight = 'on'
      union
      SELECT field1, field2, date_full FROM press_releases
      WHERE status = 'active' and spotlight = 'on'
      ORDER BY 'date_full' DESC

      Note that you can only UNION queries with fields of the same data-type (in the same order!!)

      Hope I got it right.

      JJ Mouris

        that is right. seems i missed the point...

        but i have an additional hint for this solution:

        If you don't use the keyword ALL for the UNION, all returned rows will be unique,
        as if you had done a DISTINCT for the total result set. If you specify ALL,
        you will get all matching rows from all the used SELECT statements.
        http://dev.mysql.com/doc/mysql/en/UNION.html

          Write a Reply...