MySQL/PHP Left Join w/ missing data??

I have two MySQL tables productcolors and storecolors; productcolors has ColorNumber, ItemNumber & ColorName fields; storecolors has ColorNumber, ItemNumber, ItemTier & StoreNumber fields. I am trying to get all of the colors from productcolors w/ a specific ItemNumber w/ a Left Join of records from storecolors w/ the same ColorNumber, ItemNumber and a specific StoreNumber. The closest I've come is this:

but this does not account for the StoreNumber and when I have a record from productcolors which has no corresponding record in storecolors I get NULL values for all of the overlaping fields (like ColorNumber; displayed using ) even when the values exist in productcolors?? Please help.

It seems that your database isn't designed too well. In your 'storecolors' table you have a 'ColorNumber', but no 'ColorName'. Yet in 'productcolors' you have both. It sounds like you should have a 'colors' table with the colours, since you are indexing them with a 'ColorNumber'. Otherwise it seems strange to specify the name of the colour for products, but not for stores.

By the way, why does a store need a colour? I don't see the value of it. Why do you want to know the colour of a store?

I think you shuold start by deciding what it is you really want to store, then get the data definition right. Only then can you design queries and things.

Go back to the basics, your design seems wrong.

Another thing, use table aliases in your queries, it makes things much easier to read.

select pc.*, sc.*
from productcolors pc
left join storecolors sc
etc.

If your tables are designed properly, all you would need to do to find out what colors are available for a product is:

select distinct ColorNumber
from productcolors pc
where pc.ItemNumber = 'x';

The table productcolors (PC) holds colors available for a given product in a productcatalog table (this could be any # of colors), from this catalog items are selected for many *stores* each item in a store may have any number of the colors available for that product (defined in PC) these selected colors are stored in a table called storecolors (SC). I did not put ColorName in both PC and SC because it seemed to be an uneccessary duplication of data, in fact ColorName appears fine using the left join I defined, it is ColorNumber and ItemNumber (the fields in both PC and SC) that are coming back as NULL if there in no ColorNumber in SC to match the ColorNumber in PC. I just need ColorNumber to be retrieved from PC even if there are no corresponding records in SC. I would also like to limit the matching records in SC to only those with the correct StoreNumber.

I still wonder about your data definition. It seems strange that a store will strictly only sell products of certain colours. What if a customer of that store wants a different colour?

Anyway, thats another story.

I don't fully understand what you want to do. It sounds like you want to see what colours are available for a specific item? If this is the case, you don't need to join any tables.

select distinct ColorNumber
from productcolors
where ItemNumber = 'x';

Please elaborate on what you are trying to do.

TO ELABORATE: I have N number of company stores each set up through a distributor (us) each of these stores can be composed of any number of items (garments) selected from a master catalog of all of the garments available to these stores. Because each garment is only manufactured in certain sizes and colors, these limitaions are set in the catalog. As each item is added to a company store it has the option of offering any of the sizes or colors availaible on that item in the catalog. All of this works great, and I can add items with no problem. But, while setting up the page to edit an existing store item I have hit a snag. I compare the newly selected colors with the previously selected colors and create three lists: colors to add, colors to modify, and colors to delete, which then carry out their functions. This worked well in a test file with sample data (not from a DB query), but when I tried it in the real file it broke down. I tracked the problem to NULL ColorNumber data coming back when the corresponding Left Joined record was none exsistant. The ColorNumber is in orginal table that I am joining to, and I just need it to return this field whether or not there is a corresponding record in the Left Joined table.

What I am looking for is a way to get all of the fields (including ColorNumber) from tPC where the tPC.ItemNumber == $ItemVar and get all of the records from tSC where tPC.ColorNumber == tSC.ColorNumber AND tSC.StoreNumber == $StoreVar

I think I understand now.

You want all the records from tPC for a specific item. You also want all the records from tSC for that item and a specific store. Since there will never be more records in tSC of this type than tPC, you want to left join from tPC to tSC.

I am assuming that you will join also on the ItemNumber, although you haven't said this. I assume this because otherwise the query makes no sense.

select tPC.*, tSC.*
from ProductColors tPC
left join StoreColors tSC
on (tSC.ColorNumber = tPC.ColorNumber
and tSC.ItemNumber = tPC.ItemNumber
and tSC.StoreNumber = 'y')
where tPC.ItemNumber = 'x';

Notice how I include the StoreNumber constraint not in the where clause, but in the left join condition.

Generally the rule is that conditions that constrain the number of rows returned should be placed in the where clause. This should be followed, EXCEPT in a left join. The reason for this is that it seems the index is being missed in a left join where the where condition makes up part of the index.

Ideally, tSC would have an index on (StoreNumber, ItemNumber, ColorNumber). This index won't be used if you put the storenumber constraint in the where clause. This is only applicable in left joins. I can't say which versions are affected by this as I have only tested it on MySQL 4.0.12 Win32, but I imagine it is applicable to other versions too.

A bonus of this is that you can be sure that the StoreNumber is checked before the null fields are inserted, whereas you can't be too sure if it is placed in the where clause (at least I can't be sure, I am very untrusting with computer programs).

Hopefully this solves it for you. The only problem I see is that some of the columns have the same name, but this doesn't matter, since those columns have the same value anyway. You may opt to not select (tPC.*, tSC.*), but rather specify the columns individually.

Hope this helps, if this is not what you wanted, please tell me.

That Query works perfectly! Thanks. However I am still getting the holes in my data?? When I run the Query using phpMyAdmin I get the correct results, but not in my web page. I use the following PHP to get the records in my page: I then create an array with all of these colors to use throughout (while rendering the first of 3 groups of checkboxes...1 group for each pricing tier, because larger sizes 4XL, 5XL, etc. usual cost more and have different colors requiring a seperate pricing tier) Omiting the checkbox rendering code the array is created using this:This results in the following:
$list[0] = Array ( [ColorNumber] => 100028 [ColorName] => White )
$list[1] = Array ( [ColorNumber] => 100030 [ColorName] => Putty )
$list[2] = Array ( [ColorNumber] => 100031 [ColorName] => Blue )
$list[3] = Array ( [ColorNumber] => [ColorName] => Black ) ... etc.
Still when there is no corresponding record in the joined table (tSC) a no data is returning for ColorNumber (I tested it and it is actually a NULL value) I thought the problem was with my Query (which also worked when tested in phpMyAdmin, except for discriminating the different stores) Is there something wrong with my PHP? Am I just crazy...or too blonde? I think I'll try to take a zen moment now. Any help (w/ the coding) is much appreciated.

I think the problem is because there are two fields called ColorNumber, and you are using mysql_fetch_assoc.

What I think is happening is this. The order of your fields in the select clause are (tPC.*, tSC.*). The productcolors fileds are first. Now remember that both tables have a filed called ColorNumber.

When you get a row from this query that didn't match a tSC record, the first ColorNumber has the proper value, but the other one has NULL.

Whe you use mysql_fetch_assoc, I imagine it builds the array by taking the name of each field and assigning the value to $array['filedname']. What I think is happening is $array['ColorNumber'] is getting set to the proper value from the tPC field, but then it is getting changed to a NULL when the tSC version is parsed.

To fix this you mustn't select (tPC.*, tSC.*), but instead specify the fields individually, like (tPC.*, tSC.field1, tSC.field2, etc). Only select the tSC fields that don't exist in tPC, so that the tPC versions aren't changed to NULL.

Try that, hopefully it will fix it.

Your solution of listing out the individual fields didn't quite work, but you hit on what the problem actually was. This inspired me, so I listed out the fields but on the second ColorNumber (from tSC) I used "tSC.ColorNumber AS ColorNo" with the field names no longer the same all of my data comes through. Thanks for everything -vertigo-, you have been a huge help.