CODEWITHSUNDEEP
mysqlsql
user969622
user969622

Reputation: 471

Not sure how to approach this with a mySQL query for two tables

I have two tables users and distance. In a page I need to list all users with a simple query such as select * from users where active=1 order by id desc.

Sometimes I need to output data from the distance table along with this query where the user ID field in users is matched in the distance table in EITHER of two columns, say userID_1 and userID_2. Also in the distance table either of the two mentioned columns must also match a specified id ($userID) as well in the where clause.

This is the best that I came up with:

select 
    a.*,
    b.distance 
from 
    users a,
    distance b 
where 
    ((b.userID_1='$userID' and a.id=b.userID_2) 
  or (a.id=b.userID_1 and b.userID_2='$userID')) 
 and a.active=1 
order by a.id desc

The only problem with this query is that if there is no entry in the distance table for the where clause to find a match, the query does not return anything at all. I still want it to return the row from the user table and return distance as null if there are no matches.

I cannot figure out if I need to use a JOIN, UNION, SUBQUERY or anything else for this situation.

Thanks.

Upvotes: 1

Views: 51

Answers (3)

grape_mao
grape_mao

Reputation: 1153

Try this:

select a.*, b.distance 
from users a 
left join distance b on (a.id=b.userID_1 or a.id=b.userID_2) and 
                        (b.userID_1 = '$userID' or b.userID_2 = '$userID') 
where a.active=1 
order by a.id desc

Upvotes: 0

No'am Newman
No'am Newman

Reputation: 6477

You need a left join between 'users' and 'distance'. As a result (pun not intended), you will always get the rows from the 'users' table along with any matching rows (if any) from 'distance'.

I notice that you are using the SQL-89 join syntax ("implicit joins") as opposed to SQL-92 join syntax ("explicit joins"). I wrote about this once.

I suggest that you change your query to

select a.*, b.distance 
from users a left join distance b 
on ((b.userID_1='$userID' and a.id=b.userID_2) 
or (a.id=b.userID_1 and b.userID_2='$userID')) 
where a.active=1 
order by a.id desc

Upvotes: 0

Jim Garrison
Jim Garrison

Reputation: 86774

Use a left join

select 
    a.*,
    b.distance 
from 
    users a
    left join distance b on
       (b.userID_1=? and a.id=b.userID_2) 
    or (b.userID_2=? and a.id=b.userID_1)
where 
    a.active=1 
order by a.id desc

and use a prepared statement. Substituting text into a query is vulnerable to SQL Injection attacks.

Upvotes: 1

Related Questions