How do I create a correct statement for MySQL JOIN?

Question

I have user table and payment is many to many relationships table.

SELECT user.uID,
       user.uName,
       user.uLocation,
       user.uBlock,
       user.uRoom,
       user.uStatus,
       payment.pID,
       payment.pDate,
       payment.pType
FROM   user
       INNER JOIN payment
         ON user.uID = payment.pID
LIMIT  0,100

With INNER JOIN results will be nothing.
With LEFT JOIN user display correct result but return NULL on payment table
With RIGHT JOIN payment display correct result but return NULL on user table

How do I get a correct result to display both? LEFT JOIN & RIGHT JOIN

score 2 · Accepted Answer · edited May 23 '17 at 11:49

Do you want FULL OUTER JOIN?

SELECT user.uID,
       user.uName,
       user.uLocation,
       user.uBlock,
       user.uRoom,
       user.uStatus,
       payment.pID,
       payment.pDate,
       payment.pType
FROM   user
       FULL OUTER JOIN payment
         ON user.uID = payment.pID
LIMIT  0,100

Full outer join

Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).

Wikipedia

For MySQL read this, you should use UNION

Sorry.. I have edited question tag. Looks like no `FULL OUTER JOIN` for MySQL. — user1286499, Apr 29 '12 at 19:29

score 1 · Answer 2 · answered Apr 29 '12 at 19:11

1

You're demanding that user id equals payment id. You're lucky if that returns an empty set, it could also return completely wrong data :) It should probably be:

user.uID = payment.uID

Or payment.userId, or something. Check the columns in the payments table.

answered Apr 29 '12 at 19:11

Andomar

232,371
49
380
404

Hmmm.. actually `pID` column will store the `user.uID` – user1286499 Apr 29 '12 at 19:34

How do I create a correct statement for MySQL JOIN?

2 Answers2