Re: What replaces IN?

[Brent Baisley]

My thinking on using a left join was that there was not a one to one 
relationship between the tables, otherwise the data would be in one 
table. So a regular join would produce duplicate records if there was a 
one to many relationship between table1 and table2. A left join would 
assure a distinct result set from table1.

On Feb 6, 2004, at 4:04 PM, Michael Stassen wrote:

This will work, but there's no reason to use a LEFT JOIN here.  With a 
LEFT JOIN, you get a row for each item_id in table1 that does not have 
a corresponding item_id in table2, with the table2 fields set to NULL. 
You then have to filter these out with your WHERE clause.  Just use a 
simple join:

  SELECT table1.* FROM table1, table2
  WHERE table1.item_id=table2.item_id
That way, you only get rows for each item_id that exists in both 
tables, which was the point, with no need to filter the extra stuff 
the LEFT JOIN would have created.

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]