JOIN LOGIC [message #398904] |
Sun, 19 April 2009 06:36  |
hooharhar
Messages: 28 Registered: April 2009
|
Junior Member |
|
|
Hi,
Im trying to get all items that havent ever been ordered. So Im trying to get all Part Numbers from the StockItem table that dont exist in the OrderLine table.
Select si."PartNo"
FROM StockItem si
left JOIN OrderLine ol
ON si."PartNo" = ol."PartNo";
I know that a left join brings back all the rows from the left table whether there is a match or not but how should I only get the rows if there isn't a match?
Any help appreciated in understanding:)
|
|
|
|
|
Re: JOIN LOGIC [message #398907 is a reply to message #398904] |
Sun, 19 April 2009 08:47   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | how should I only get the rows if there isn't a match?
|
If the question is to get the rows that don't match in the other table:
select id from table2
minus
select id from table1
Regards
Michel
[Updated on: Sun, 19 April 2009 08:48] Report message to a moderator
|
|
|
Re: JOIN LOGIC [message #398908 is a reply to message #398904] |
Sun, 19 April 2009 08:51   |
hooharhar
Messages: 28 Registered: April 2009
|
Junior Member |
|
|
So if StockItem has PartNo's 1,2,3,4,5 and OrderLine only has 1 and 2. then I want to return the remaining values from StockItem which will be 3 4 and 5.
I dont quite understand the minus idea
Thanks for helping
|
|
|
|
|
|
Re: JOIN LOGIC [message #398912 is a reply to message #398904] |
Sun, 19 April 2009 12:13   |
hooharhar
Messages: 28 Registered: April 2009
|
Junior Member |
|
|
This is what I did
Select "PartNo" as "Part Number"
FROM StockItem
MINUS
Select "PartNo"
FROM OrderLine;
this is my result
This is not what I want because I also want the Description column of the StockItem table to be displayed too. For example:
Part Number Description
27 text
29 text
38 text
These are my tables
CREATE TABLE "ORDERLINE"
( "LineNo" NUMBER(8,0),
"OrderNo" NUMBER(8,0),
"PartNo" NUMBER(8,0),
"OrderStatusID" NUMBER(8,0),
"Quantity" NUMBER(5,0) NOT NULL ENABLE,
CONSTRAINT "PK_ORDERLINE" PRIMARY KEY ("LineNo", "OrderNo") ENABLE
)
/
CREATE TABLE "STOCKITEM"
( "PartNo" NUMBER(8,0),
"Description" VARCHAR2(4000),
"Bin" NUMBER(6,0) NOT NULL ENABLE,
"QuantityIn" NUMBER(5,0) NOT NULL ENABLE,
"Category" VARCHAR2(15) NOT NULL ENABLE,
"VatAmount" NUMBER(3,1) NOT NULL ENABLE,
"UnitPrice" NUMBER(10,2) NOT NULL ENABLE,
"Price" NUMBER(10,2) NOT NULL ENABLE,
CONSTRAINT "CHK_PRICES" CHECK ("UnitPrice"<"Price") ENABLE,
CONSTRAINT "PK_PARTNOONSTOCKITEM" PRIMARY KEY ("PartNo") ENABLE
)
/
Sorry about my other posts, I hope this is better.
Thanks.
|
|
|
|
|
|