[RESOLVED] Find distinct values for same column on two different tables? (Merge 2 tables)

[cbj4074]

Hi, everyone,

I am trying to merge two tables together, and only want to keep records that don't exist in both tables.

This is my currenct approach (which is obviously PAINFULLY slow and inefficient):

//Select all of the "permanent" order data.
$q = 'SELECT order_id FROM orders';
$r = mysql_query($q) or die(mysql_error());

//From the results, form a query to select only new records from the "temporary" order data.
$q1 = 'SELECT * FROM orders_temp WHERE order_id != 0';
while ($row = mysql_fetch_assoc($r)) {
	$q1 .= ' AND order_id != ' . $row['order_id'];
}
$r1 = mysql_query($q1) or die(mysql_error());

//
while ($row1 = mysql_fetch_assoc($r1)) {
	$q3 = 'INSERT INTO orders (
	order_id,
	invoice_num,
	subtotal,
	email
	) VALUES (
	' . $row1['order_id'] . ',
	"' . $row1['invoice_num'] . '",
	' . $row1['subtotal'] . ',
	"' . $row1['email'] . '"
	)';
	$r3 = mysql_query($q3) or die(mysql_error());
}

Thanks for any help!

[tomhath]

Since mysql doesn't have a FULL OUTER JOIN, you need to use a UNION with two LEFT JOINs to get the same effect. This will give you the rows that are only in one or the other, you can use an INNER JOIN to get the rows that are in both (or get rid of the WHERE clauses here):

select o.order_id 
from orders o
left outer join orders_temp t 
on t.order_id = o.order_id
where t.order_id is null
UNION
select t.order_id 
from orders_temp t
left outer join orders o 
on t.order_id = o.order_id
where o.order_id is null

[jheitz]

There is another way of doint this:

SELECT * FORM orders WHERE NOT EXISTS (SELECT 1 FORM orders_temp WHERE orders.Order_ID = orders_tem.Order_ID)

Not 100% sure if it will be faster or slower than Tomhath's suggestion, you would need to try it. If you do, please let us know which one is quicker (for future reference).

[cbj4074]

Thank you!!!

tomhath, your query worked perfectly. Man, I spent like 3 hours trying to figure out what you wrote in 10 lines of SQL. I think I just need to hit the books hard and study more SQL.

jheitz, thank you for your reply, but I wasn't able to get your method to work, so I couldn't test to see which method is faster (your query just returned 0 results). tomhath's method ploughed through over 1,000 records instantly (at least as perceived by a human), for whatever that's worth.

Thanks again! =)

[jheitz]

No surprise really, I missinterpreted what you were trying to do. My approach would return all records in ONE table that don't exist in the other. If no records where returned it would imply all records that exist in orders also exist in orders_temp.

In any case, I am quite sure that Tomhath's standard join approach will be as quick if not quicker (all be it a bit more easy to decypher if your SQL is not that strong).