RE: Select from one table where ID not in another table
Use LEFT JOIN SELECT t1.*, t2.id FROM table1 as t1 LEFT JOIN table2 as t2 ON t1.id=t2.id WHERE t2 IS NULL; -- something like that -- ---Original Message- --From: Martin Moss [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 02, 2003 1:50 PM --To: [EMAIL PROTECTED] --Subject: Select from one table where ID not in another table -- --All, -- --Am wondering if it's possible to do a query that does something like --this:- -- --SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 --WHERE table1.id DOESN'T EXIST IN table2.id; -- -- --Regards -- --Marty -- -- - --Outgoing mail is certified Virus Free. --Checked by AVG anti-virus system (http://www.grisoft.com). --Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
select t1.* from table1 t1 LEFT JOIN table2 t2 on t1.id=t2.id WHERE t2.id IS NULL you can print out table2 values if you want, but they will all be NULL.. provided that table2.id and table1.id are the matches you are trying to find. Kelley Martin Moss wrote: All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
Hi Marty, Yes, a query like this: SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON (t2.id=t1.id) WHERE t2.id IS NULL This assumes that table2.id is defined as NOT NULL. See also: http://www.mysql.com/doc/en/JOIN.html Hope that helps. Matt - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 3:54 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
I'm not sure if I've described the exact results I want very well, but thanks to everyone for your help so far, hope you can bear with me a little longer. Below are the table Descriptions Table1:- +---+---+--+-+-+ + | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+ + | TransactionID | int(11) | | PRI | NULL| auto_increment | | ReconciliationD| int(11) | YES | | 0 | | x| AccountID | int(11) | | MUL | 0 | | y| AccountNumber | varchar(255) | | | 0 | | z| Created | datetime | | | -00-00 00:00:00 | | u| Updated | timestamp(14) | YES | | NULL| | v| Value | decimal(20,2) | | | 0.00| | w| Date | datetime | | | -00-00 00:00:00 | | t| DatabaseID| int(11) | | | 0 | | +---+---+--+-+-+ + Table1 Data 1,8,x,y,z,u,v,w,t(x,y,z,u,v,w,t are irrelavant fields to the join) 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t 6,9,x,y,z,u,v,w,t 7,9,x,y,z,u,v,w,t 8,9,x,y,z,u,v,w,t 9,9,x,y,z,u,v,w,t 10,9,x,y,z,u,v,w,t Table2:- +-+---+--+-+ -+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+ -+---+ | ReconciledTransactionID | int(11) | | PRI | 0 | | | TransactionID | int(11) | | UNI | 0 | | | ReconciliationID| int(11) | | PRI | 0 | | +-+---+--+-+ -+---+ Table2 Data 1,1,8 2,2,8 So far I have this query:- SELECT tr.* FROM Table1 tr LEFT JOIN Table2 recTran ON recTran.ReconciliationID = tr.ReconciliationID WHERE tr.Rec onciliationID = '8' AND tr.TransactionID recTran.TransactionID; So Expected results should be:- 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t This Works ok, HOWEVER, if Table2 Has no Data, the query returns NO results. What I want it to return is:- 1,8,x,y,z,u,v,w,t 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t any Takers? Regards Marty - Original Message - From: Jason Ramsey [EMAIL PROTECTED] To: Martin Moss [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:27 PM Subject: RE: Select from one table where ID not in another table Well, in order for that to work, you will need to do an explicit JOIN somewhere or else the IS NULL or NOT NULL won't work. You might try... SELECT table1.*, table2.id FROM table1 LEFT JOIN table2 ON table1.ortherkeyid = table2.otherkeyid WHERE table1.otherkeyid = '7236523' AND (table2.otherkeyid IS NULL or table2.otherkeyid IS NOT NULL) -Original Message- From: Martin Moss [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:01 PM To: Martin Moss; [EMAIL PROTECTED] Subject: Re: Select from one table where ID not in another table Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General
Re: Select from one table where ID not in another table
SELECT tr.* FROM Table1 tr LEFT JOIN Table2 recTran ON tr.ReconciliationID=recTran.ReconciliationID WHERE (tr.ReconciliationID = '8' AND tr.TransactionID recTran.TransactionID) || recTran.ReconciledTransactionID IS NULL; Should work, I'm always not sure about the joins and which table should go on which side of the = since you want to look at all values in the tr table, I always put it on the left side of the = on the join, but I'm not sure it makes any difference... Kelley Martin Moss wrote: I'm not sure if I've described the exact results I want very well, but thanks to everyone for your help so far, hope you can bear with me a little longer. Below are the table Descriptions Table1:- +---+---+--+-+-+ + | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+ + | TransactionID | int(11) | | PRI | NULL| auto_increment | | ReconciliationD| int(11) | YES | | 0 | | x| AccountID | int(11) | | MUL | 0 | | y| AccountNumber | varchar(255) | | | 0 | | z| Created | datetime | | | -00-00 00:00:00 | | u| Updated | timestamp(14) | YES | | NULL| | v| Value | decimal(20,2) | | | 0.00| | w| Date | datetime | | | -00-00 00:00:00 | | t| DatabaseID| int(11) | | | 0 | | +---+---+--+-+-+ + Table1 Data 1,8,x,y,z,u,v,w,t(x,y,z,u,v,w,t are irrelavant fields to the join) 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t 6,9,x,y,z,u,v,w,t 7,9,x,y,z,u,v,w,t 8,9,x,y,z,u,v,w,t 9,9,x,y,z,u,v,w,t 10,9,x,y,z,u,v,w,t Table2:- +-+---+--+-+ -+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+ -+---+ | ReconciledTransactionID | int(11) | | PRI | 0 | | | TransactionID | int(11) | | UNI | 0 | | | ReconciliationID| int(11) | | PRI | 0 | | +-+---+--+-+ -+---+ Table2 Data 1,1,8 2,2,8 So far I have this query:- SELECT tr.* FROM Table1 tr LEFT JOIN Table2 recTran ON recTran.ReconciliationID = tr.ReconciliationID WHERE tr.Rec onciliationID = '8' AND tr.TransactionID recTran.TransactionID; So Expected results should be:- 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t This Works ok, HOWEVER, if Table2 Has no Data, the query returns NO results. What I want it to return is:- 1,8,x,y,z,u,v,w,t 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t any Takers? Regards Marty - Original Message - From: Jason Ramsey [EMAIL PROTECTED] To: Martin Moss [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:27 PM Subject: RE: Select from one table where ID not in another table Well, in order for that to work, you will need to do an explicit JOIN somewhere or else the IS NULL or NOT NULL won't work. You might try... SELECT table1.*, table2.id FROM table1 LEFT JOIN table2 ON table1.ortherkeyid = table2.otherkeyid WHERE table1.otherkeyid = '7236523' AND (table2.otherkeyid IS NULL or table2.otherkeyid IS NOT NULL) -Original Message- From: Martin Moss [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:01 PM To: Martin Moss; [EMAIL PROTECTED] Subject: Re: Select from one table where ID not in another table Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL
Re: Select from one table where ID not in another table - Solved
Finally worked it out, here's the query I'm using SELECT table2.TransactionID,table1.* FROM Table1 table1 LEFT OUTER JOIN Table2 table2 ON table2.TransactionID = table1.Transa ctionID WHERE table1.ReconciliationID = '8' HAVING table2.TransactionID IS NULL; Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:47 PM Subject: Re: Select from one table where ID not in another table I'm not sure if I've described the exact results I want very well, but thanks to everyone for your help so far, hope you can bear with me a little longer. Below are the table Descriptions Table1:- +---+---+--+-+-+ + | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+ + | TransactionID | int(11) | | PRI | NULL| auto_increment | | ReconciliationD| int(11) | YES | | 0 | | x| AccountID | int(11) | | MUL | 0 | | y| AccountNumber | varchar(255) | | | 0 | | z| Created | datetime | | | -00-00 00:00:00 | | u| Updated | timestamp(14) | YES | | NULL| | v| Value | decimal(20,2) | | | 0.00| | w| Date | datetime | | | -00-00 00:00:00 | | t| DatabaseID| int(11) | | | 0 | | +---+---+--+-+-+ + Table1 Data 1,8,x,y,z,u,v,w,t(x,y,z,u,v,w,t are irrelavant fields to the join) 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t 6,9,x,y,z,u,v,w,t 7,9,x,y,z,u,v,w,t 8,9,x,y,z,u,v,w,t 9,9,x,y,z,u,v,w,t 10,9,x,y,z,u,v,w,t Table2:- +-+---+--+-+ -+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+ -+---+ | ReconciledTransactionID | int(11) | | PRI | 0 | | | TransactionID | int(11) | | UNI | 0 | | | ReconciliationID| int(11) | | PRI | 0 | | +-+---+--+-+ -+---+ Table2 Data 1,1,8 2,2,8 So far I have this query:- SELECT tr.* FROM Table1 tr LEFT JOIN Table2 recTran ON recTran.ReconciliationID = tr.ReconciliationID WHERE tr.Rec onciliationID = '8' AND tr.TransactionID recTran.TransactionID; So Expected results should be:- 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t This Works ok, HOWEVER, if Table2 Has no Data, the query returns NO results. What I want it to return is:- 1,8,x,y,z,u,v,w,t 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t any Takers? Regards Marty - Original Message - From: Jason Ramsey [EMAIL PROTECTED] To: Martin Moss [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:27 PM Subject: RE: Select from one table where ID not in another table Well, in order for that to work, you will need to do an explicit JOIN somewhere or else the IS NULL or NOT NULL won't work. You might try... SELECT table1.*, table2.id FROM table1 LEFT JOIN table2 ON table1.ortherkeyid = table2.otherkeyid WHERE table1.otherkeyid = '7236523' AND (table2.otherkeyid IS NULL or table2.otherkeyid IS NOT NULL) -Original Message- From: Martin Moss [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:01 PM To: Martin Moss; [EMAIL PROTECTED] Subject: Re: Select from one table where ID not in another table Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List