Babydead Babydead - 4 months ago 8
SQL Question

MYSQL - Not getting results when adding empty foreign key table

So I have been having this issue with something.

I have a structure that states a store is in a city.

table Stores
store (pk)
city (fk)


Getting them works fine.
Then I add store_info

table store_info
store (pk)
adress
phone
description


so we get this query:

SELECT stores.ID, store_info.address, store_info.phone
FROM store_info, stores
WHERE stores.city = 1
AND stores.ID = store_info.storeID


Now, this is working perfectly fine.
However, the moment I add
FROM store_brands


SELECT stores.ID, store_info.address, store_info.phone
FROM store_info, stores, store_brands, brands
WHERE stores.city = 1
AND stores.ID = store_info.storeID


This last query fails to give me results, but the query does not fail.

The thing is, I store my stores apart from the brands, and I link the brands to the stores through means of a relations table (2 foreign keys)
It looks as simple as this:

store_brands
storeID (fk to stores)
brandID (fk to brands)


The whole point of the setup (as you may well be aware) is that I can gather all the brands through use of
GROUP_CONCAT
. The query technically works perfectly fine as long as the store has at least 1 relationship within the
store_brands
table


Now, what I want to do is, I want to get the store entirely regardless of whether or not it has linked brands. These same brands can also be linked through the chain (1 level up), so I want to gather both these instances, meaning I still want the store to show up even if it hasn't got any brands filled out.

I don't understand why there HAS to be a row in
store_brands
in order for a simple select query to have it show up. Can anybody help me with this? Am I making a big mistake within my structure?

Edit for clarity:
This is the entire query, by the way. It works fine, but again, only works if the store actually has brands linked to it: (the previous queries were debugs as to where it went wrong)

SELECT store_info.storeID, store_info.display_name, store_info.address, store_info.phone, GROUP_CONCAT(brands.display_name ORDER BY brands.name) AS brands
FROM store_info, brands, stores, store_brands
WHERE stores.city = 1
AND store_brands.store = stores.ID
AND brands.id = store_brands.brand
AND stores.ID = store_info.storeID
GROUP BY store_info.storeID
ORDER BY store_info.display_name


Update and answered

Thanks to the source provided by idg, I found out how I needed to replace my regular joins with left joins, and how this would indeed return NULL.

New, working query:

SELECT stores.ID, store_info.display_name, store_info.address, store_info.phone, GROUP_CONCAT(brands.display_name ORDER BY brands.name) AS brands
FROM store_info, stores
LEFT JOIN store_brands ON stores.ID = store_brands.store
LEFT JOIN brands ON store_brands.brand = brands.ID
WHERE stores.city = 1
AND stores.ID = store_info.storeID
GROUP BY store_info.storeID
ORDER BY store_info.display_name

ldg ldg
Answer

Use LEFT JOIN for cases where null values are allowed. Read up on how joins work here.