OraFAQ Forum: Forms » NULL doesn't load any results

Home » Developer & Programmer » Forms » NULL doesn't load any results

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NULL doesn't load any results [message #163386]

Thu, 16 March 2006 10:18

sephina
Messages: 14
Registered: March 2006

Junior Member

Hi. I am trying to show any DVD's that have never been ordered using the statement shown below. It shows that "no rows selected" even though there are DVD's that have not yet been purchased.

SELECT dd.Title, dd.Selling_Price, dd.Number_in_Stock
FROM dvd_details dd
JOIN order_details od
ON dd.dvd_id = od.dvd_id
WHERE od.DVD_ID is NULL;

Report message to a moderator

Re: NULL doesn't load any results [message #163391 is a reply to message #163386]

Thu, 16 March 2006 10:39

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Check out NOT EXISTS.

Report message to a moderator

Re: NULL doesn't load any results [message #163392 is a reply to message #163386]

Thu, 16 March 2006 10:43

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Try

SELECT dd.Title, dd.Selling_Price, dd.Number_in_Stock
FROM dvd_details dd
left outer JOIN order_details od
ON dd.dvd_id = od.dvd_id
WHERE od.DVD_ID is NULL;

HTH
Jim

Report message to a moderator

Re: NULL doesn't load any results [message #163395 is a reply to message #163392]

Thu, 16 March 2006 10:51

sephina
Messages: 14
Registered: March 2006

Junior Member

THANK YOU!!! IT WORKS! Laughing

Report message to a moderator

Re: NULL doesn't load any results [message #163397 is a reply to message #163395]

Thu, 16 March 2006 10:53

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Not sure if you took my suggestion or Art's, but if it was mine, make sure you try Art's too, it could well be faster for you.

Jim

Report message to a moderator

Re: NULL doesn't load any results [message #163401 is a reply to message #163397]

Thu, 16 March 2006 11:00

sephina
Messages: 14
Registered: March 2006

Junior Member

I used yours (Jim) and it works okay so I think I'm gona try to solve my next problem. Thank you! Do you know how to use a query to run as a form that prompts the user for a value before bringing up the search results? Thanks again

Sephina

Report message to a moderator