Brent Baisley wrote:
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.
How would it do that? So far as I know, the left join does not differ from the regular join in this way. Both will produce duplicate rows (based on table1.item_id) if there are multiple corresponding rows in table2. The difference between the two is the left join adds rows for the "missing" values in table2.
If you just want a list of item_ids with no duplicates, you can use DISTINCT or GROUP BY, like this:
SELECT DISTINCT table1.item_id FROM table1, table2 WHERE table1.item_id=table2.item_id
or
SELECT table1.item_id FROM table1, table2 WHERE table1.item_id=table2.item_id GROUP BY table1.item_id
Michael
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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
