CODEWITHSUNDEEP
mysql
user1286499
user1286499

Reputation: 227

How do I create a correct statement for MySQL JOIN?

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
  1. With INNER JOIN results will be nothing.
  2. With LEFT JOIN user display correct result but return NULL on payment table
  3. 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

Upvotes: 0

Views: 48

Answers (2)

gdoron
gdoron

Reputation: 150253

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

Upvotes: 2

Andomar
Andomar

Reputation: 238068

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.

Upvotes: 1

Related Questions