RE: Select from one table where ID not in another table

2003-09-02 Thread Dathan Vance Pattishall 

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

2003-09-02 Thread Martin Moss 
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

2003-09-02 Thread Martin Moss 
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

2003-09-02 Thread Kelley Lingerfelt 

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

2003-09-02 Thread Matt W 
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

2003-09-02 Thread Martin Moss 
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

2003-09-02 Thread Kelley Lingerfelt 
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

2003-09-02 Thread Martin Moss 
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