469,315 Members | 1,573 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

5-table SELECT giving me problems

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
May 11 '07 #1
3 1547
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]
May 11 '07 #2
Rimsky
3
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
May 14 '07 #3
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
May 14 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Alain Hogue | last post: by
1 post views Thread by =?Utf-8?B?d2VzdGNvYXN0Y29kZQ==?= | last post: by
46 posts views Thread by benwizzle | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.