Right join or left join

Databases
1

I’m trying this query but it wont work how I thought it should lol

SELECT minerals.mineral, bank.amount, members.credits AS cash FROM minerals INNER JOIN members RIGHT JOIN bank ON bank.minerID = minerals.id WHERE userid LIKE 1

There are 10 minerals and the bank contains the amount of each for each user. But the user 1 used in the query only have 9 out of 10. This query only returns a list of 9 and not 10.

Hope someone can help!

2

could you please do a SHOW CREATE TABLE for each table

3

Minerals

CREATE TABLE minerals( idint(11) NOT NULL AUTO_INCREMENT, mineralvarchar(32) NOT NULL, priceint(11) NOT NULL, description text NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

Bank

CREATE TABLE bank( idint(11) NOT NULL AUTO_INCREMENT, minerIDint(11) NOT NULL, userIDint(11) NOT NULL, amount bigint(20) NOT NULL, PRIMARY KEY (id), UNIQUE KEY id (id) ) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8

4

If you have a condition which checks for a value on the table you’ve right joined, it becomes an inner join.

If you want to find all the minerals as related to user 1, then you need to reverse the starting point on the query. I also noticed you don’t have any criteria on your join to the members table - how does this one match up?

SELECT minerals.mineral
     , bank.amount
     , members.credits AS cash 
  FROM bank
 INNER JOIN members  ON ??? = ???
 LEFT JOIN minerals ON bank.minerID = minerals.id 
 WHERE userid = 1 -- the like is not appropriate it's a specific value
5

Im adding the members table cause I need to print the amount of credits the user has. The rest will print the amount he has of each mineral. With the right join I would like to include minerals that the user dont have as well.

So it would look something like user has 10 credits, 5 gold, 2 silver, 0 bronze etc …

6

That’s fine, but the point is you’re not tying the tables together, so you’ll get ALL of the records on the members table instead of what just matches. You need an ON condition in there (something like ON bank.userid = members.id)

1 Like
7 
SELECT minerals.mineral
     , bank.amount
     , members.credits AS cash 
  FROM members 
LEFT OUTER 
  JOIN bank 
    ON bank.userID = members.userid
LEFT OUTER
  JOIN minerals
    ON minerals.id = bank.minerID
 WHERE members.userid = 1

pro tip: never use RIGHT JOINs

1 Like
8

I hate when you make me feel stupid… :frowning:

9

Thanks, I didn’t know :slight_smile: I added like you said. Only the ones the user has. So when I do an inner join or any kind of join I need an ON statement too right ?

SELECT minerals.mineral
     , bank.amount
     , members.credits AS cash 
  FROM bank
 INNER JOIN members  ON members.id = bank.userID
 RIGHT JOIN minerals ON bank.minerID = minerals.id 
 WHERE userid = 1
10

See @r937’s post. It should have been a left join… I corrected my post, but it was obviously too late…

11

No problem. Thanks for the help. I tried his query, but it returns only 9 out of 10 minerals.

The bank table for this user contains 9 minerals. In the mineral table there are a total of 10.

12

my apologies for misunderstanding your requirement

hang on a second…

13 
SELECT minerals.mineral
     , bank.amount
     , members.credits AS cash 
  FROM minerals 
LEFT OUTER 
  JOIN bank 
    ON bank.minerID = minerals.id    
   AND bank.userID = 1
LEFT OUTER
  JOIN members
    ON members.userid = bank.userID
1 Like
14

Thanks that’s awesome. I will study the difference and learn how to do it correct next time

15

I often refer back to this when my JOINs aren’t doing what I hoped they’d do.

2 Likes
16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.