MonkeyClicker

I am having problems with some SQL I am writing. I have a table of rooms and a table of recordings and I want to return the last recording ID in each room. This is no big deal except when the system is initialized and there are no records in the recordings table. The query returns an empty result set. I have read about THETA JOINS and it seems to be what I am looking for but I cant find any examples of how to put this kind of SQL statement together in MYSQL.

Thanks

Please post your SQL to the thread.

Here is my sql query that dosent work.

Code: Select all

SELECT    rooms.id,
          MAX(recs.id) AS MAX_recs_id
FROM rooms
        INNER JOIN recs
              ON rooms.id = recs.room_id
GROUP BY rooms.id


Try this

Code: Select all

    SELECT    rooms.id,
              MAX(recs.id) AS MAX_recs_id
    FROM rooms
            LEFT OUTER JOIN recs
                  ON rooms.id = recs.room_id
    GROUP BY rooms.id


That works but it is returning <NULL> values if there are no records. How can I get it to return zero if there are no records?

Use this

Code: Select all

 
    SELECT    rooms.id,
              COALESCE(MAX(recs.id),0) AS MAX_recs_id
    FROM rooms
            INNER JOIN recs
                  ON rooms.id = recs.room_id
    GROUP BY rooms.id