[SQL] Help with Left Joins

[Pc_Madness]

*sigh* Can someone explain to me where I'm going wrong here. :( Basically its an order system. Products have Colours and electrical specs which define the voltage. Distributors have access to certain Products. I need to find all of the Products in a certain category. Now when you make an Order, an entry is put into OrdProd (a copy of the price, prod id and quantity etc). This means prices can change over time and not effect older orders. Obviously not all Products have been ordered, so there may not be a row in OrdProd...so for this I need a Left join on OrdProd yeah?

Atm the page is alittle slow loading so I was hoping I could cut out these two sub queries to improve performance abit (this query can return 100 odd rows in a single hit or can sometimes be run multiple times for about 10 rows each). I dunno, performance on my lowly little Macbook isn't too bad, but on the server it can be quite a few seconds so perhaps its entirely a server issue.

So, the SQL,

SELECT PROD_ID, DISTPROD_PRODNAME, PROD_EXTRA, COL_NAME, COL_RGBHEX, VOLT_NAME, DISTPROD_PRICE,
isnull((SELECT ORDPROD_QUANTITY FROM ORDPROD WHERE ORDPROD_PROD_ID = PRODUCT.PROD_ID AND ORDPROD_ORD_ID = @OrdID), 0) AS QUANTITY,
isnull((SELECT ORDPROD_PRICE FROM ORDPROD WHERE ORDPROD_PROD_ID = PRODUCT.PROD_ID AND ORDPROD_ORD_ID = @OrdID), 0) AS ORDPROD_PRICE 
FROM PRODUCT, DISTPAGE, DISTPROD, COLOUR, VOLTAGE, ELECTSPEC
WHERE DISTPAGE_DIST_ID = @DistID AND DISTPAGE_CAT_ID = PROD_CAT_ID
AND PROD_CAT_ID = @CatID
AND DISTPROD_DIST_ID = DISTPAGE_DIST_ID
AND DISTPROD_PROD_ID = PROD_ID
AND DISTPROD_STATUS = 'ACTIVE' 
AND PROD_COL_ID = COL_ID AND PROD_ELECTSPEC_ID = ELECTSPEC_ID 
AND ELECTSPEC_VOLT_ID = VOLT_ID
ORDER BY DISTPROD_PRODNAME

if I replace the top few lines with this,

SELECT PROD_ID, DISTPROD_PRODNAME, PROD_EXTRA, COL_NAME, COL_RGBHEX, VOLT_NAME, DISTPROD_PRICE
FROM PRODUCT, DISTPAGE, DISTPROD, COLOUR, VOLTAGE, ELECTSPEC
LEFT JOIN ORDPROD ON PROD_ID = ORDPROD_PROD_ID

SQL Express says,

Msg 207, Level 16, State 1, Line 4

Invalid column name 'PROD_ID'.

Am I making some noob mistake?

If I do PRODUCT.PROD_ID I get

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "PRODUCT.PROD_ID" could not be bound.

*grumble* Any ideas?

[monkey13]

My understanding is that you can only left join 2 tables. (Anyone confirm) So you might want to try doing the join with product and ordprod then joining the results with the other tables to get the extra info you need.

[BGM]

i have just tried to sort this out, but its difficult because i don't know the structure of your db.. could you post up a screen shot of the relationships or something? or even just a list of which fields are in each table....

i can help you out then :)

cheers

[Antaris Veteran]

Can you give us your table schemas?

[Pc_Madness]

  Antaris said:

Can you give us your table schemas?

Hey all, heres a screen of the tables in question, hopefully it helps some. Gunna have another crack at this and see where I get.

[Pc_Madness]

SELECT PROD_NAME, PROD_SUBNAME, COL_NAME, VOLT_NAME, ORDPROD_QUANTITY, ORDPROD_PRICE
FROM PRODUCT
INNER JOIN COLOUR ON PROD_COL_ID = COL_ID
INNER JOIN CATEGORY ON PROD_CAT_ID = CAT_ID
INNER JOIN ELECTSPEC ON ELECTSPEC_ID = PROD_ELECTSPEC_ID
INNER JOIN VOLTAGE ON ELECTSPEC_VOLT_ID = VOLT_ID
INNER JOIN DISTPROD ON (DISTPROD_PROD_ID = PROD_ID AND DISTPROD_DIST_ID = 2)
INNER JOIN DISTPAGE ON (DISTPAGE_DIST_ID = DISTPROD_DIST_ID AND DISTPAGE_CAT_ID = CAT_ID)
LEFT JOIN ORDPROD ON (ORDPROD_PROD_ID = PROD_ID AND ORDPROD_ORD_ID = 129)
WHERE CAT_ID = 17
ORDER BY ORDPROD_QUANTITY ASC

Pc_Madness is Win! How it is working, I don't know, all I know is that it is. :) Is there anyway to improve that at all? :) Are Inner Joins any faster than normal joins (the WHERE COL= COL kind)

[Antaris Veteran]

You should be able to run an execution plan on your statement and it will attempt to measure the speediness of the different stages of your statements.

I noticed you've now switched to INNER instead of LEFT joins? Is this part of the design, the INNER joins will be a lot more restrictive, because a single missing record in one of the tables could cause no results to be returned...

[BGM]

  Pc_Madness said:

Pc_Madness is Win! How it is working, I don't know, all I know is that it is. :) Is there anyway to improve that at all? :) Are Inner Joins any faster than normal joins (the WHERE COL= COL kind)

read this: http://www.w3schools.com/sql/sql_join.asp

ill see if i can improve it at all tonight when i get home..

if i were you, id fill your tables with some 'what-if' scenarios and then try your query to be sure you are getting back the results you want..

thats probably the best way to be sure :)

[Pc_Madness]

  Antaris said:

You should be able to run an execution plan on your statement and it will attempt to measure the speediness of the different stages of your statements.

I noticed you've now switched to INNER instead of LEFT joins? Is this part of the design, the INNER joins will be a lot more restrictive, because a single missing record in one of the tables could cause no results to be returned...

Yeah, thats what I ended up doing for my entire page in end, had one query which was at like 68% usage and throw in an Index and cut it down to like 25%. Never done indexs before so was quite surprised. :)

Sorry, perhaps I didn't explain that part correctly, the ORDPROD is optional, but all of the others are required.