Query Operator Help

[susanh]

Hi,

The end result that I am trying to get from the following query is just give me the ProjectID's that are in the Projects table BUT are not in the Task Table.

I am trying to do this with the not equal operator, but that does not seem to be giving the correct results. Can someone point me in the right direction?

Thanks
Sue

Query:
SELECT Projects.ProjectID, Projects.StartDate, Projects.ShipDate, Projects.DueDate, Projects.Customer, Projects.Unit, Projects.NonEESUnit,
Projects.Name, Projects.Description, Projects.Site, Projects.JobSiteContact
FROM Projects INNER JOIN
Tasks ON Tasks.ProjectID <> Projects.ProjectID
GROUP BY Projects.ProjectID, Projects.StartDate, Projects.ShipDate, Projects.DueDate, Projects.Customer, Projects.Unit, Projects.NonEESUnit,
Projects.Name, Projects.Description, Projects.Site, Projects.JobSiteContact
ORDER BY Projects.ProjectID

 

[bborissov]

For such queries don't use INNER JOIN, because INNER returns these records which are in BOTH tables, Try this:

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] Projects.ProjectID, Projects.StartDate, Projects.ShipDate, Projects.DueDate, Projects.Customer, Projects.Unit, Projects.NonEESUnit,
       Projects.Name, Projects.Description, Projects.Site, Projects.JobSiteContact
[COLOR=blue]FROM[/color] Projects
     [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Tasks [COLOR=blue]ON[/color] Tasks.ProjectID = Projects.ProjectID
[COLOR=blue]WHERE[/color] Tasks.ProjectID [COLOR=blue]IS[/color] NULL
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Projects.ProjectID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

3 ways

--LEFT JOIN
SELECT p.ProjectID, p.StartDate, p.ShipDate, p.DueDate, p.Customer, p.Unit, p.NonEESUnit,
       p.Name, p.Description, p.Site, p.JobSiteContact
FROM   Projects LEFT JOIN
                      Tasks ON t.ProjectID = p.ProjectID 
WHERE t.ProjectID IS NULL
GROUP BY p.ProjectID, p.StartDate, p.ShipDate, p.DueDate, p.Customer, p.Unit, p.NonEESUnit,
       p.Name, p.Description, p.Site, p.JobSiteContact
ORDER BY p.ProjectID

--NOT EXISTS
SELECT p.ProjectID, p.StartDate, p.ShipDate, p.DueDate, p.Customer, p.Unit, p.NonEESUnit,
       p.Name, p.Description, p.Site, p.JobSiteContact
FROM   Projects WHERE NOT EXISTS (SELECT * FROM
                      Tasks t WHERE t.ProjectID = p.ProjectID )
GROUP BY p.ProjectID, p.StartDate, p.ShipDate, p.DueDate, p.Customer, p.Unit, p.NonEESUnit,
       p.Name, p.Description, p.Site, p.JobSiteContact
ORDER BY p.ProjectID

--NOT IN
SELECT p.ProjectID, p.StartDate, p.ShipDate, p.DueDate, p.Customer, p.Unit, p.NonEESUnit,
       p.Name, p.Description, p.Site, p.JobSiteContact
FROM   Projects WHERE p.ProjectID NOT IN (SELECT t.ProjectID FROM
                      Tasks t  )
GROUP BY p.ProjectID, p.StartDate, p.ShipDate, p.DueDate, p.Customer, p.Unit, p.NonEESUnit,
       p.Name, p.Description, p.Site, p.JobSiteContact
ORDER BY p.ProjectID

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[susanh]

That was it! Thanks Guys.