Page 1 of 1
Quick Question about Table Joins
Posted: Mon Mar 17, 2003 12:17 pm
by Etherguy
I have a quick question about using "join" in mysql. I understand how it works and what not, but what I would like to do is display only the values that do not match. Example :
Table1:
jid
name
amount
Table2:
jid
name
amount
Table 1 has data from lets say funds borrowed, and table 2 has payments made against the borrowed. What I would like to do is, get a list of outstanding funds from table one, obviously sorting out the funds already payed back.
Anyone have a clue how I might go about this?
Figured it out..
Posted: Mon Mar 17, 2003 2:26 pm
by Etherguy
UPDATE :
Figured it out on my own.
Used :
Code: Select all
select table1.amount from table1, table2 where (table1.id != table2.id);
Works like a champ!
Thanks anyway!
Posted: Mon Mar 17, 2003 4:35 pm
by Rob the R
I would make sure to test your solution under different circumstances. You may get unintended results from the query you posted. For example, lets say the tables have the following data:
TABLE1 (jid, name, amount):
1, Fred, $10
2, George, $20
3, Ethel, $30
TABLE2 (jid, name, amount):
1, Fred, $10
3, Ethel, $30
The query:
Code: Select all
select table1.name, table2.name, table1.amount, table2.amount
from table1, table2
where (table1.jid != table2.jid);
will return the following rows (not necessarily in this order):
Fred, Ethel, $10, $30
George, Fred, $20, $10
George, Ethel, $20, $30
Ethel, Fred, $30, $10
These are returned because they satisfy your where clause: the values of JID are indeed not equal. This is obviously not what you want. Instead, you should use a left join:
Code: Select all
select table1.name, table1.amount
from table1
left join table2 on table1.jid = table2.jid
where table2.jid is NULL;
This will only match up rows from both tables where the JID values are equal, but allow rows in TABLE1 to not find a match. The where clause in this case weeds out the ones that do match, leaving only the unmatched rows in TABLE1.
See the
MySQL documentation on LEFT JOIN syntax for more details.
Posted: Wed Mar 19, 2003 9:16 am
by Etherguy
I actually checked the name against the jid to weed out the ones I did not want.
But thanks for the suggestion on the left join.