Is the join query correct?

Question

I have multiple tables that I want to join to get a specific result.

Tables are:

ad_categories, ad_image, ad_data, ad_location

I need a row output for every one of the following rows in the ad_categories table, regardless if there exists data for any of these categories for a specific location when using the WHERE conditional.

mysql> SELECT type FROM ad_categories;
+---------------+
| type          |
+---------------+
| restaurants   |
| fitness       |
| funactivities |
| shopping      |
| homes         |
| men           |
+---------------+

For example:

mysql> SELECT alias, type, sha, originalname FROM ad_categories LEFT JOIN ad_data ON ad_data.cid=ad_categories.id LEFT JOIN ad_image ON ad_image.pid=ad_data.id LEFT JOIN ad_location ON ad_location.id=ad_data.lid GROUP BY type;
+--------+---------------+------------------------------------------+---------------------------------+
| alias  | type          | sha                                      | originalname                    |
+--------+---------------+------------------------------------------+---------------------------------+
| malibu | fitness       | ad8b277202f4ded274274744b3fa28f34e9f1c21 | thai_body_works.jpg             |
| malibu | funactivities | 6a226df8ff827aa020b077e9e0d48e4701ae2fca | rosenthal-the_malibu_estate.jpg |
| NULL   | homes         | NULL                                     | NULL                            |
| NULL   | men           | NULL                                     | NULL                            |
| malibu | restaurants   | 98f357dfa5bdb2eb1d480dc0e8b7156b1eecac31 | moonshadows.jpg                 |
| malibu | shopping      | 1b2ef538691569842b9f9fb6c3816673f334205a | malibu_surf_shack.jpg           |
+--------+---------------+------------------------------------------+---------------------------------+
6 rows in set (0.00 sec)

Lists all the category types I need, but I haven't specified the location which is what I need to do. I want only one row per type, per location specified. If no data exists for the category type for that location, fill in null values for the columns other than type.

mysql> SELECT alias, type, sha, originalname FROM ad_categories LEFT JOIN ad_data ON ad_data.cid=ad_categories.id JOIN ad_image ON ad_image.pid=ad_data.id LEFT JOIN ad_location ON ad_location.id=ad_data.lid WHERE ad_location.alias='malibu' GROUP BY type;
+--------+---------------+------------------------------------------+---------------------------------+
| alias  | type          | sha                                      | originalname                    |
+--------+---------------+------------------------------------------+---------------------------------+
| malibu | fitness       | ad8b277202f4ded274274744b3fa28f34e9f1c21 | thai_body_works.jpg             |
| malibu | funactivities | 6a226df8ff827aa020b077e9e0d48e4701ae2fca | rosenthal-the_malibu_estate.jpg |
| malibu | restaurants   | 98f357dfa5bdb2eb1d480dc0e8b7156b1eecac31 | moonshadows.jpg                 |
| malibu | shopping      | 1b2ef538691569842b9f9fb6c3816673f334205a | malibu_surf_shack.jpg           |
+--------+---------------+------------------------------------------+---------------------------------+
4 rows in set (0.00 sec)

I am not a pro SQL ninja. Can someone correct me if I am building my SQL query incorrectly or educate me with a few clues on how to properly accomplish what I want to do? Thanks.

Answer 1

You need to use an "outer join".

Answer 2

Use:

SELECT alias, type, sha, originalname
  FROM (SELECT alias, 
               type, 
               sha, 
               originalname,
               CASE WHEN @type = type THEN @rownum := @rownum + 1 ELSE @rownum := 1 END AS rank,
               @type := type
          FROM AD_CATEGORIES c 
     LEFT JOIN AD_DATA d ON d.cid = c.id 
     LEFT JOIN AD_LOCATION l ON l.id = d.lid 
                            AND l.alias = ?
     LEFT JOIN AD_IMAGE i ON i.pid = d.id 
          JOIN (SELECT @rownum := 0, @type := '') r
      ORDER BY type, l.id DESC) x
 WHERE x.rank = 1

The only thing I don't have is what column in, I'm assuming, AD_LOCATION that you'd specify "malibu"/etc as the location. The ORDER BY is important, to make sure that non-NULL alias/locations are ranked before NULL ones...

Breakdown

This:

JOIN (SELECT @rownum := 0, @type := '') r

...allows you to define & initialize variables without needing to use a separate SET command.

These:

CASE
   WHEN @type = type THEN @rownum := @rownum + 1  -- increments rownum when type matches
   ELSE @rownum := 1 -- resets rownum to 1 when @type != type
END AS rank,
@type := type -- need this to set @type for the next rows comparison

See the stuff after the "-- " for line-by-line commenting.

ORDER BY type, l.id DESC

The ORDER BY is necessary to keep the type values in order, otherwise the rank value won't as we'd like. The second part of the ORDER BY ensures that the location values that aren't NULL come up first, if there are any based on the filtration.

Finally, the outside query only gets the rows whose rank value is 1...

Answer 3

Left this alone for a while, came back to it. Here's the answer I needed, thanks to a coworker at a new job:

SELECT 
    alias, type, sha, originalname 
FROM 
    ad_categories 
LEFT JOIN 
    ad_location ON ad_location.alias = '?' 
LEFT JOIN 
    ad_data ON ad_data.cid=ad_categories.id AND ad_location.id=ad_data.lid 
LEFT JOIN 
    ad_image ON ad_image.pid=ad_data.id GROUP BY type;

Thanks coworker ;)