query help

I have Two tables. Products and ProductInfo.
All the Products(productId) have entries in ProductInfo (FK productId)
I want to find out products whose entries do not exist in ProductInfo
Pls hlpSELECT ProductID --, ...
FROM Products p
LEFT OUTER JOIN ProductInfo pi
ON p.ProductID = pi.ProductID
WHERE pi.ProductID IS NULL
-- or
SELECT ProductID --, ...
FROM Products
WHERE ProductID NOT IN
(
SELECT ProductID
FROM ProductInfo
)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||Hi
SELECT <column lists> FROM Products WHERE NOT EXISTS
(SELECT * FROM ProductInfo WHERE Products.ProductId=ProductInfo.Productid )
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
> I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||SELECT ...
FROM Product AS p
WHERE NOT EXISTS(SELECT * FROM ProductInfo AS pi WHERE pi.productID = p.Prod
uctId)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||Hi , Aaron
> SELECT ProductID --, ...
> FROM Products
> WHERE ProductID NOT IN
> (
> SELECT ProductID
> FROM ProductInfo
> )
>
I have my doubt about this query because if the OP has ProductId IS NULL
we may get back a wrong output .Am I right
BTW why did you drop an acoount (ng) ? :-)
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:ux6n8fLUFHA.1796@.TK2MSFTNGP15.phx.gbl...
> SELECT ProductID --, ...
> FROM Products p
> LEFT OUTER JOIN ProductInfo pi
> ON p.ProductID = pi.ProductID
> WHERE pi.ProductID IS NULL
> -- or
> SELECT ProductID --, ...
> FROM Products
> WHERE ProductID NOT IN
> (
> SELECT ProductID
> FROM ProductInfo
> )
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
> news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>|||> I have my doubt about this query because if the OP has ProductId IS NULL
> we may get back a wrong output .Am I right
Possibly, but it was defined as a foreign key, so I figured the chance of it
being NULL was pretty small.

> BTW why did you drop an acoount (ng) ? :-)
Because there are a couple of dimwits in here who don't seem to know when to
shut up. Not you, of course.
A|||Yep, I sow a few posts. Sometimes it is really sorry that this NG is
unmoderated , should be a moderator here.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:egwAZpLUFHA.2892@.TK2MSFTNGP14.phx.gbl...
NULL
> Possibly, but it was defined as a foreign key, so I figured the chance of
it
> being NULL was pretty small.
>
> Because there are a couple of dimwits in here who don't seem to know when
to
> shut up. Not you, of course.
> A
>|||I agree with AB. Some people in the newsgroup dont know how to respect the
SQL MVPs. They contradict them, argue with them for no apparent reason and
talk to them in a demeaning way. I am not a MVP but I personally think
everyone should respect all the SQL MVPs because of their tremendous
knowledge and experience which they share to perform better in our jobs.
They give great design solutions, great answers and then actually point the
flaws when we are doing something wrong.
I read all of MVP posts and I always follow and implement their suggestions.
I am hoping others would learn to respect them.
Thanks
M
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEwG7gLUFHA.3840@.tk2msftngp13.phx.gbl...
> SELECT ...
> FROM Product AS p
> WHERE NOT EXISTS(SELECT * FROM ProductInfo AS pi WHERE pi.productID =
p.ProductId)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
> news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>