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?
Quick Question about Table Joins
Moderator: General Moderators
Figured it out..
UPDATE :
Figured it out on my own.
Used :
Works like a champ!
Thanks anyway!
Figured it out on my own.
Used :
Code: Select all
select table1.amount from table1, table2 where (table1.id != table2.id);Thanks anyway!
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:
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:
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.
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);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;See the MySQL documentation on LEFT JOIN syntax for more details.