Stan Stan - 5 months ago 8
MySQL Question

Query between two tables but to show also if there is no data in one of the tables

I want to query one table then join second table but if there is no data in second table to show only what is in first table.

So there is table

orders
and table
payments
what I'm trying is something like this

SELECT orders. * , payments. *
FROM orders
INNER JOIN payments ON orders.user_id = payments.userID
WHERE orders.user_id =3
AND payments.processed = ''
OR payments.processed >= '0'


This query return some strange results.. Currently there are 3 records in table
orders
and 2 records in table
payments
for
user_id = 3
. It should return me 3 records at all because they are 3 in
orders
. But it is returning me each record from
orders
twice.. i.e. 6 results.

What I'm missing here?

Answer

This happens due to lack of parenthesis on the AND and OR's, AND has precedence over OR:

SELECT orders. * , payments. * 
FROM orders
INNER JOIN payments ON orders.user_id = payments.userID
WHERE orders.user_id =3
    AND (payments.processed =  ''
         OR payments.processed >=  '0')

Your query as it is being evaluated like this:

WHERE (orders.user_id =3 AND payments.processed =  '')
    OR(payments.processed >=  '0')

Another thing that can multiply the results is that each order has more then one payments .

Comments