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 | | | 0000-00-00 00:00:00 | | u| Updated | timestamp(14) | YES | | NULL | | v| Value | decimal(20,2) | | | 0.00 | | w| Date | datetime | | | 0000-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 Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
