mysql join 2 tables where either 1 of 2 columns match a column in second table

[antonyfal]

Hi.

I have a join issue where i need to match either 1 of 2 columns of the first table to a specific column in the second table..

 

$query2 = mysql_query('SELECT DISTINCT contact.email,contact.work_email, users.id, users.real_name FROM contact INNER JOIN users ON contact.email = users.email', $ussrLink);
// here i need a OR option to match either contact.email = users.email (if it does not the i will try the work_email) OR contact.work_email = users.email
How can i achieve this..
 

[ginerjm]

So - did you actually try an OR option in the ON clause?

[Barand]

Alternatively you can use IN (...)

[antonyfal]

Hi thanks for replying..

The thing is i don't know how to write it with the OR or the IN clause.. I cant find an example of where either 1 of two columns match a single column on the other table..

 

I was hoping someone could write it so i could see the setup or layout of such a query..

 

basically im trying to avoid the following as it produces duplicates in the dropdown select list..

$msgdropall ='<optgroup label="Contacts online">';
$query2 = mysql_query('SELECT DISTINCT contact.email, users.id, users.real_name FROM contact INNER JOIN users ON contact.email=users.email WHERE contact.userid = '.$thisJRUser->id, $ussrLink);
if(mysql_num_rows($query2)>0){
while($row2=mysql_fetch_array($query2))  
{  
$msgdropct .='<option value="'.$row2['id'].'">'.$row2['real_name'].'</option>';
}   
} else {
$msgdropct .='';
}
$msgdropall .=$msgdropct;

$query3 = mysql_query('SELECT DISTINCT contact.work_email, users.id, users.real_name FROM contact INNER JOIN users ON contact.work_email=users.email WHERE contact.userid = '.$thisJRUser->id, $ussrLink);
if(mysql_num_rows($query3)>0){
while($row3=mysql_fetch_array($query2))  
{  
$msgdropdd .='<option value="'.$row3['id'].'">'.$row3['real_name'].'</option>';
}   
} else {
$msgdropdd .='';
}
$msgdropall .='</optgroup>';
$msgdropall .=$msgdropdd;


$output['MSGDROPALL']=$msgdropall;

 
 

[Barand]

OK here's the spoonfed versions

$sql = "SELECT DISTINCT contact.email, users.id, users.real_name
    FROM contact
        INNER JOIN users ON users.email IN (contact.email, contact.work_email)
    WHERE contact.userid = {$thisJRUser->id} ";

$sql = "SELECT DISTINCT contact.email, users.id, users.real_name
    FROM contact
        INNER JOIN users ON (users.email = contact.email) OR (users.email = contact.work_email)
    WHERE contact.userid = {$thisJRUser->id} ";

[antonyfal]

Ta Sen..

Thats exactly what i was looking for... Just off-hand, which is the better method? or does it not make a difference?

 

Regards

Antony

[Barand]

That's OK, Advanced.

 

I prefer IN method. It's simpler when you have multiple ORs, especially if mixed with ANDs

[antonyfal]

Hi Barand... (sorry about the Sen :-) )

I got a small spin on my initial query, the two methods above don't work on it.. Could you show me how to use them if i have two WHERE pointing to one Userid?

 

The app is a messaging app, i am trying to collect all the unique user id's from the table under the current users id.. The user1 and user2 columns are the sender and receiver columns, if i make the WHERE only user2 then i will only get users that the current user has sent messages to. I would also like to use the user2 because then i can get get users that have sent messages to the current user.. But i want the rows of users to be unique. My method bellow calls all the users although unique for each user1 and user2 call..

 

IE:

user1:

johnny

steve

user2

johnny

steve

 

i want:

johnny

steve 

 

 

in the table it will look like this:

row1: user1=62, user2=30

row2: user1= 29, user2-62

row3: user1=20, user2=62

row4: user1=62, user2= 12

 

all 62 in user1 column are the messages that 62 sent, all 62's in user2 are messages that 62 received.

I am using this information to populate a dropdown (<select name="sendto">options</select>)

$query = mysql_query("SELECT DISTINCT global_pm.user1,global_pm.user2,users.id,users.real_name FROM global_pm
        INNER JOIN users ON users.id IN (global_pm.user1, global_pm.user2)
        WHERE (global_pm.user2='".$User->id."' and global_pm.id2='1') OR (global_pm.user1='".$User->id."' and global_pm.id2='1')", $ussrLink);
 

[Barand]

It isn't the same problem so requires a different solution.

 

In your original post you were trying to join with a single user record based on two possible matching columns. In the second you are trying to join to two different user records. In this case you would join twice to the user table but use aliases to treat it as two logical tables.

SELECT pm.user1
, u1.real_name as sender
, pm.user2
, u2.real_name as receiver
FROM global_pm as pm
    INNER JOIN users as u1 ON pm.user1 = u1.id
    INNER JOIN users as u2 ON pm.user2 = u2.id

However, given that you want a single list of users, I would use a UNION in this instance

SELECT DISTINCT 
  pm.user1 as id
, u.real_name as username
FROM global_pm as pm
    INNER JOIN users as u ON pm.user1 = u.id

UNION

SELECT DISTINCT
  pm.user2 as id
, u.real_name as username
FROM global_pm as pm
    INNER JOIN users as u ON pm.user2 = u.id

ORDER BY username

[antonyfal]

Thanks a stack Barand..

 

This is very helpful and I now know how to use the UNION function..

 

Awesome..