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 | | | 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] > --- 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: 20/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
