5-table SELECT giving me problems

[terminon]

New to the forum, and a relatively new with SQL, but until now I've been able to get everything to work just fine. I've spent hours trying to get this select statement to work right, and I've never quite gotten there -- it's probably an easy fix, and I'm just too close to the problem or too new at SQL to find it. This is on SQL Server 2005. Any help would be tremendously appreciated.

I am trying to do a join including 5 tables: one each for customers, equipment, equipment categories, equipment configurations, and configuration items. Here is my current code:

[PHP]SELECT
eq.id, eq.partnum, eq.description, eq.cost,
cat.name, v.name,
i.qty,
cf.name, cf.usr_id
FROM Equipment eq
INNER JOIN Equipment_Categories cat on cat.id = eq.cat_id
INNER JOIN Vendors v on v.id = eq.cust_id
INNER JOIN Equipment_Configurations cf on cf.id = 5
LEFT JOIN Equipment_Configurations_Items i on i.eq_id = eq.id
where i.cf_id = cf.id OR i.qty IS NULL[/PHP]

Our staff is able to create a "configuration" that lists what equipment we need from which vendor, and how many of each item. Each vendor can have multiple pieces of equipment which belong to any existing "category." The SELECT statement above is used to populate a page where they can edit the quantity of each item, but it only parly works.

I successfully retrieve the configuration, complete with name, equipment, vendors, and quantity for all equipment for a given configuration (here, configuration 5). I also retrieve all equipment that has NO quantity in ANY configuration. So if configuration A includes 9 of equipment X, conf. B includes 1 eq. Y, and no configuration has any of eq. Z, when I run this SELECT query to edit configuration A I am only returned 9 x equipment X, 0 x equipment Z -- equipment Y is not in the result set, so I can't have the user specify that they want to add any of that equipment to their configuration.

I've tried different kinds of joins to no avail. Of course, I can select all equipment and include its category and vendor, as well as the configuration name with this simple query:

[PHP]select eq.*, cf.name, v.name, cat.name from equipment eq
inner join equipment_configurations cf on cf.id = 5
inner join vendors v on v.id = eq.vendor_id
inner join equipment_categories cat on cat.id = eq.cat_id[/PHP]

The problem comes when I try to include the quantity of each item currently associated with that configuration. My brain's fried, and nothing I have tried so far works.

Is that all as clear as mud? Hopefully it makes sense to more than my own confused mind. I can give any other information needed to help get this fixed.

TIA.
-Ben

[terminon]

Sorry, in my attempt to clean up my SQL and make it more coherent for others, I made a mistake. The second join in my first select actually should be this:

[PHP]INNER JOIN Vendors v on v.id = eq.vendor_id[/PHP]

[Rimsky]

I am trying to do a join including 5 tables: one each for customers, equipment, equipment categories, equipment configurations, and configuration items. Here is my current code:

[PHP]SELECT
eq.id, eq.partnum, eq.description, eq.cost,
cat.name, v.name,
i.qty,
cf.name, cf.usr_id
FROM Equipment eq
INNER JOIN Equipment_Categories cat on cat.id = eq.cat_id
INNER JOIN Vendors v on v.id = eq.cust_id
INNER JOIN Equipment_Configurations cf on cf.id = 5
LEFT JOIN Equipment_Configurations_Items i on i.eq_id = eq.id
where i.cf_id = cf.id OR i.qty IS NULL[/PHP]

Is that all as clear as mud? Hopefully it makes sense to more than my own confused mind. I can give any other information needed to help get this fixed.

TIA.
-Ben

Ben,

Try to use LEFT OUTER JOIN\'s

[terminon]

Thanks Rimsky. LEFT OUTER JOIN didn't help me, but it did get me thinking and helped me come up with the solution. Turns out all I had to do was remove

where i.cf_id = cf.id OR i.qty IS NULL

and change the last join to

LEFT JOIN Equipment_Configurations_Items i on i.eq_id = eq.id AND i.cf_id = cf.id

Thanks again for getting me thinking right.

-Ben