Help with a query.

Hello, I have TWO tables.
TABLE1
--
ID ProgramID
01 Program1
02 Program2
03 Program3
04 Program4
...
15 Program15
TABLE2
--
ID Username ProgramID
01 Gary Program1
02 John Program2
03 James Program15
04 Gary Program30
05 Gary Program16
Now, we can see in TABLE2 that Gary has joined Program1, Program16 and
Program30, but he has not joined any of the others. How can I return a list
of all the programs GARY has NOT joined? I basically need to return ALL
rows from Table1, WHERE Username <> Gary in Table2.
Not sure if I should be using Subqueries our Joins.
Any help appreciated!!
Gary.SELECT t1.ProgramID
FROM Table1 t1
LEFT OUTER JOIN table2 t2
ON t1.ProgramID = t2.ProgramID
AND t2.UserName = 'Gary'
WHERE t2.ProgramID IS NULL
Jacco Schalkwijk
SQL Server MVP
"G" <G@.G.COM> wrote in message
news:Uotge.30046$G8.6851@.text.news.blueyonder.co.uk...
> Hello, I have TWO tables.
> TABLE1
> --
> ID ProgramID
> 01 Program1
> 02 Program2
> 03 Program3
> 04 Program4
> ...
> 15 Program15
>
> TABLE2
> --
> ID Username ProgramID
> 01 Gary Program1
> 02 John Program2
> 03 James Program15
> 04 Gary Program30
> 05 Gary Program16
>
> Now, we can see in TABLE2 that Gary has joined Program1, Program16 and
> Program30, but he has not joined any of the others. How can I return a
> list
> of all the programs GARY has NOT joined? I basically need to return ALL
> rows from Table1, WHERE Username <> Gary in Table2.
> Not sure if I should be using Subqueries our Joins.
> Any help appreciated!!
> Gary.
>
>|||Hi
You can wriite the query this way:
SELECT * FROM TABLE1 WHERE TABLE1.ID
NOT IN ( SELECT TABLE2.ID FROM TABLE2 WHERE Username = 'Gary')
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"G" wrote:

> Hello, I have TWO tables.
> TABLE1
> --
> ID ProgramID
> 01 Program1
> 02 Program2
> 03 Program3
> 04 Program4
> ...
> 15 Program15
>
> TABLE2
> --
> ID Username ProgramID
> 01 Gary Program1
> 02 John Program2
> 03 James Program15
> 04 Gary Program30
> 05 Gary Program16
>
> Now, we can see in TABLE2 that Gary has joined Program1, Program16 and
> Program30, but he has not joined any of the others. How can I return a li
st
> of all the programs GARY has NOT joined? I basically need to return ALL
> rows from Table1, WHERE Username <> Gary in Table2.
> Not sure if I should be using Subqueries our Joins.
> Any help appreciated!!
> Gary.
>
>