Quick Question about Table Joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Quick Question about Table Joins

Post 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?
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Figured it out..

Post 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!
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post 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.
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Post 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.
Post Reply