Query Trouble - help required.

[G]

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.

[Felix Geerinckx]

On 11/05/2005, G wrote:

How can I return a list of all the programs GARY has NOT joined?

USE test;
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
programid VARCHAR(20) NOT NULL
);
INSERT INTO table1 (id, programid) VALUES
(NULL, 'Program1'),
(NULL, 'Program2'),
(NULL, 'Program3'),
(NULL, 'Program4'),
(NULL, 'Program5');

DROP TABLE IF EXISTS table2;
CREATE TABLE table2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL,
programid VARCHAR(20) NOT NULL
);
INSERT INTO table2 (id, username, programid) VALUES
(NULL, 'Gary', 'Program1'),
(NULL, 'Gary', 'Program3'),
(NULL, 'John', 'Program3'),
(NULL, 'James', 'Program5');

SELECT
table1.programid
FROM table1
LEFT JOIN table2 ON table2.programid = table1.programid
AND table2.username = 'Gary'
WHERE
table2.id IS NULL;

--
felix