Home » SQL & PL/SQL » SQL & PL/SQL » JOIN LOGIC
icon5.gif  JOIN LOGIC [message #398904] Sun, 19 April 2009 06:36 Go to next message
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 #398905 is a reply to message #398904] Sun, 19 April 2009 08:38 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Look for nulls in the OrderLine table
icon9.gif  Re: JOIN LOGIC [message #398906 is a reply to message #398904] Sun, 19 April 2009 08:39 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
There are no nulls in that table...
Re: JOIN LOGIC [message #398907 is a reply to message #398904] Sun, 19 April 2009 08:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #398909 is a reply to message #398904] Sun, 19 April 2009 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm#i2054381

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Re: JOIN LOGIC [message #398910 is a reply to message #398904] Sun, 19 April 2009 10:58 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
thankyou for the link. the MINUS gives me what I am looking for. however My two tables are not the same structure.

So I am now going to research getting the "PartNo" column from StockItem AND the "Description" column but "Description" column does not exist in OrderLine.

Many many thanks for your help, I didnt even know minus existed! Sad
Re: JOIN LOGIC [message #398911 is a reply to message #398904] Sun, 19 April 2009 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
Re: JOIN LOGIC [message #398912 is a reply to message #398904] Sun, 19 April 2009 12:13 Go to previous messageGo to next message
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


Part Number 
27 
29 
38 

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.
Re: JOIN LOGIC [message #398913 is a reply to message #398912] Sun, 19 April 2009 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can select partno and description from stockitem where partno in is IN the set returned by the previous query.

Regards
Michel
Re: JOIN LOGIC [message #398914 is a reply to message #398904] Sun, 19 April 2009 12:21 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Thankyou. I did
Select "PartNo" as "Part Number" ,"Description"
FROM StockItem
WHERE "PartNo" IN
(Select "PartNo" as "Part Number"
FROM StockItem
MINUS
Select "PartNo"
FROM OrderLine);

and it works.
Surprised
Re: JOIN LOGIC [message #398915 is a reply to message #398904] Sun, 19 April 2009 12:27 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT "PartNo" AS "Part Number", 
       "Description" 
FROM   stockitem 
WHERE  "PartNo" IN (SELECT "PartNo" AS "Part Number" 
                    FROM   stockitem 
                    MINUS 
                    SELECT "PartNo" 
                    FROM   orderline);


http://www.orafaq.com/utilities/sqlformatter.htm

[Updated on: Sun, 19 April 2009 12:27]

Report message to a moderator

Previous Topic: Entering php variables into SP
Next Topic: Trigger with :new and RAISE_APPLIATION_ERROR
Goto Forum:
  


Current Time: Wed Feb 19 03:15:30 CST 2025