Need SQL to select from 2 tbl's WHERE NOT equal to each other

[JGKWORK]

Can someone please help, I'm looking for the SQl (for SQL server to do the following): (it should be easy but its not proving that way!)


2 tbls: News_Article_Pictures & News_Articles

The News_Article_Pictures tbl contains the following fields:

PictureID (int(4) (incremental)
Picture_Title (varchar (80))
Picture_Filename (varchar (80))
DateAdded nVarchar (12)
Picture_Description (text)

News_Articles tbl:

ArticleID (int(4) increment
ArticleTitle (varchar (80))
ArticleText text
PictureID (int (4)
ArticleDatePosted nVarchar (12)
ArticleKeywords (varchar (80))

My tables obviously link on PictureID, what I want to be able to do is return the PictureID's from the News_Article_Pictures tbl WHERE there is no match in the News_Articles tbl.

I have tried what I thought would be the SQL but it returns no records:

SELECT TOP 100 PERCENT dbo.News_Article_Pictures.PictureID, dbo.News_Article_Pictures.Picture_Title, dbo.News_Article_Pictures.Picture_FileName,
dbo.News_Article_Pictures.DateAdded, dbo.News_Article_Pictures.Picture_Description
FROM dbo.News_Article_Pictures INNER JOIN
dbo.News_Articles ON dbo.News_Article_Pictures.PictureID = dbo.News_Articles.PictureID AND
dbo.News_Article_Pictures.PictureID <> dbo.News_Articles.PictureID
ORDER BY dbo.News_Article_Pictures.PictureID

I have tried lots of different combinations, but nothing works what am i doing wrong?

Thanks alot..

 

[JohnDTampaBay]

Check this thread out. I believe it holds the answer to your question. Good luck!

thread183-762400

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[JGKWORK]

Thanks, have had a look at this and tried it but it ain't happening, any ideas? anyone?

 

[ks1392]

If I understand correctly, it looks like you can do this with a subquery. Try something like this:

SELECT PictureID FROM News_Article_Pictures
WHERE PictureID NOT IN (
SELECT PictureID FROM News_Articles
)

This is basically grabbing all the PictureIDs from the News_Articlas table and excluding them from your result set.

--Kerr

 

[JGKWORK]

Thanks alot, I'll give this a try and let u know how i get on!

 

[JGKWORK]

Thanks a bunch ks1392, that worked a treat. Why would'nt my simple query (above) work, it was generated the through SQL server query builder and all I added was a criteria of &quot;<> dbo.News_Articles.PictureID..... to any ideas?

Thanks again.

 

[SemperFiDownUnda]

lets look at your logic.

SELECT dbo.News_Article_Pictures.PictureID, 
       dbo.News_Article_Pictures.Picture_Title, 
       dbo.News_Article_Pictures.Picture_FileName, 
       dbo.News_Article_Pictures.DateAdded, 
       dbo.News_Article_Pictures.Picture_Description
  FROM dbo.News_Article_Pictures 
 INNER JOIN dbo.News_Articles 
    ON dbo.News_Article_Pictures.PictureID = dbo.News_Articles.PictureID 
   AND dbo.News_Article_Pictures.PictureID <> dbo.News_Articles.PictureID
 ORDER BY dbo.News_Article_Pictures.PictureID

lets focus in on the join conditions

    ON dbo.News_Article_Pictures.PictureID = dbo.News_Articles.PictureID 
   AND dbo.News_Article_Pictures.PictureID <> dbo.News_Articles.PictureID

You are basically saying here Join tableA to tableB when PictureID's are equal AND when they are not equal.

This is a cartisian join or cross join just written differently.

What exactly are you looking for? From your post you are looking for orphaned News_Article_Pictures (eg Pictures that have no article associated with them.)

so what you want is a LEFT OUTER JOIN because you want to get all records from the News_Article_Pictures JOIN it to News_Articles and only display the ones where News_Articles.PictureID IS NULL

The query is pretty easy once you understand JOINs so I'll refer you to the JOIN Fundamentals FAQ faq183-4785.





Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[JGKWORK]

Thank you, I feel humbled and kinda silly after looking at the logic!

 

[SemperFiDownUnda]

NP I've felt that way MANY times. I'm sure I haven't seen the last of that feeling either.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first