JOINING PROBLEMS

[marabou]

I have a join probelm with one of my statements.

The scenario is that each document has stock assigned to it and that stock may or may not have serial no.s assigned to it. I need to select the every model id which is on a document, along with the serial no.s assigned to the model id if there are any assigned.


select count (docstk.id)
from doc, docstk, stock,
where doc.id = 442 and
docstk.docid = doc.id and
docstk.stkid = stock.id

gives me the 26 stock ids I need from the tables. However I also need to select the serial no's which have been assigned to the stock id's within the document.

Using this query I get a cartesian product

select docstk.id, serial.serno
from doc, docstk, stock, serial, docser
where doc.id = 442 and
docstk.docid = doc.id and
docstk.stkid = stock.id and
serial.id = docser.serid

and using this query I only get the three models which have seril no's assigned to them (I also need to see the models which have no serial no.s assigned to them).

SELECT Serial.serno, Stock.code, Doc.id
FROM DocSer ,DocStk,Serial,Stock,Doc
WHERE Doc.id = 442
AND Doc.id = DocStk.DocID
AND DocStk.id = DocSer.DocStkID
AND DocSer.SerID = Serial.id
AND DocStk.StkID = Stock.id

I know I need to use some sort of inner/left(??) join on the tables but I am unsure of the syntax and getting nowhere fast.
I hope somebody can help me as I am really stuck and have an iminent deadline.

Thanks in advance

marabou

 

[rikin]

Try
AND DocStk.StkID (+) = Stock.id
or
AND DocStk.StkID = Stock.id (+)

if you are using oracle

 

[tlbroadbent]

You need an OUTER JOIN - probably LEFT JOIN. I prefer ANSI standard syntax. Try this query.

SELECT Serial.serno, Stock.code, Doc.id
FROM (((Doc
INNER JOIN DocStk ON Doc.id = DocStk.DocID)
INNER JOIN DocSer ON DocStk.id = DocSer.DocStkID)
INNER JOIN Stock ON DocStk.StkID = Stock.id)
LEFT JOIN Serial ON DocSer.SerID = Serial.id
WHERE Doc.id = 442

BTW: Which database are you using? Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[marabou]

I am using SQL SERVER 2000. I have managed to get a query working that gets this information out, however, I am now having trouble with the rest of the information that I need as again, my sql syntax isn't that good.

Has anyone any ideas, it would be greatly appreciated.

Here is the statement that works.

select doc.code, docstk.id, docser.serid
from docstk left join docser
on (docstk.id = docser.docstkid), doc
where doc.id = 442
and doc.id = docstk.docid

Here is a statement that gets the rest of the information that I need.

SELECT
DocStk.Qty, DocStk.LineID, DocStk.AQty, DocStk.LineType,
Serial.serno,
Stock.code,
Doc.code, Doc.sourceId
FROM
DocSer,DocStk,Serial,Stock,Doc
WHERE
DocSer.SerID = Serial.id AND
DocStk.StkID = Stock.id

I hope that somebody could help me.

Thanks in advance