Complicated SQL Statement

[Vel]

Hi, I'm trying to build a complicated SQL statement and I'm not sure if what I want is even possible.

 

I have a table called products, another called optiongroup and a third called prodoptions.

 

Products contains a list of individual products available for sale.

Optiongroup contains a list of options that can be applied to products. A product can have several options and an option can be applied to several products.

Prodoptions records what optiongroups are attached to what products.

 

I am trying to run a query on the Products and Prodoptions tables to pull all products matching a set of criteria (name, price, type, etc.) and then I select one optiongroup. I want the query to show all products that match the initial criteria, and then if they have an optiongroup that matches the one selected to retrieve the details from Prodoptions, otherwise have the field be null.

 

So far I have tried this query:

SELECT pID, pName, poOptionGroup FROM products LEFT JOIN prodoptions ON products.pID = prodoptions.poProdID ORDER BY pName

Which will display every product fine, but returns a product multiple times if it has more than one optiongroup attached to it. E.g.:

pc001	#1 PC multimedia package	8
pc001	#1 PC multimedia package	6
pc001	#1 PC multimedia package	7
pc001	#1 PC multimedia package	9
scanner001	Flatbed scanner	NULL
GlassTopDiningTable	Glass Top Dining Table	NULL
GraniteDiningTable	Granite Top Cafe Dining Table	NULL
lprinter001	Laser Printer	NULL
palmtop001	Palmtop Computer	6
palmtop001	Palmtop Computer	7
portable001	Portable PC	10
RoundWoodTable	Round Wooden Table	NULL

 

I also tried:

SELECT pID, pName, poOptionGroup FROM products LEFT JOIN prodoptions ON products.pID = prodoptions.poProdID WHERE poOptionGroup = 6 ORDER BY pName

However that only displays products that have optiongroup with an ID of 6 attached to them, and not all products even if they don't have optiongroup 6 attached.

 

So can I do this and if so how?

[Zane]

I am trying to run a query on the Products and Prodoptions tables to pull all products matching a set of criteria (name, price, type, etc.) and then I select one optiongroup. I want the query to show all products that match the initial criteria, and then if they have an optiongroup that matches the one selected to retrieve the details from Prodoptions, otherwise have the field be null.

 

This may or may not be what you are looking for, but it should help you in the long run.

SELECT p.pID, p.pName, p.poOptionGroup 
FROM prodoptions po
INNER JOIN products p
ON p.pID = po.poProdID 
WHERE p.poOptionGroup = 6
ORDER BY p.pName

 

[Vel]

Hi Zane,

 

Thanks for the post but this has produced the exact opposite of what I was after. I need to display all rows from table p, regardless of whether they have a corresponding result in po or not, hence the left join. The problem is when they have multiple results in po they output multiple times in the list. I'm trying to limit it so that a product is only listed once in the results and that poOptionGroup returns NULL unless it matches a specified ID. But even if it doesn't have a matching ID under poOptionGroup the product still needs to output.

 

E.G:

Dataset (Products)

pID        pName

pc001        PC package #1

portable001        Portable PC #1

scanner001        Scanner #1

 

Dataset (optiongroup)

optGrpID        optGrpName

1        Processor

2        Harddrive

3        Network Card

4        Monitor

5        Resolutions

 

Dataset(prodoptions)

poID        poProdID        poOptionGroup

1        pc001        1

2        pc001        2

3        pc001        3

4        pc001        4

5        scanner001        5

 

 

The results I get are

pc001    1

pc001    2

pc001    3

pc001    4

portable001 NULL

scanner001 5

 

If I select poOptionGroup = 5 I want to get

pc001    NULL

portable001 NULL

scanner001 5

 

but instead I only get the scanner.

[Zane]

group by p.pID then

[Vel]

group by p.pID then

Sorry, what do you mean group by p.pID? That's an individual ID for a product and is used in the join to reference to poProdID (they are the same).

[Vel]

AHA! Just googled group by. How the hell have I never come across that before? Thank you, that was exactly what I was after. OK, well, not 100% what I was after but much, much closer. Is there a command I can use to now get it to show the specific poOptionGroup without excluding those that don't match?

[kickstart]

Hi

 

GROUP BY is for aggregate fields. Bit dodgy to use it to just get a unique row.

 

Your code checking for a po option group of 6 is having the issue you mention as the check for that field is in the WHERE clause. You can put it in the ON clause. This way is there is a row which has that option group you will get fields from the joined table, if not you will get nulls from the joined table.

 

SELECT pID, pName, poOptionGroup 
FROM products 
LEFT JOIN prodoptions 
ON products.pID = prodoptions.poProdID 
AND prodoptions.poOptionGroup = 6 
ORDER BY pName 

 

All the best

 

Keith

[Vel]

Thank you Kieth. That is exactly what I was after.