mysql JOINS query

Question

I have 3 tables, usersonline, mobsters, and gangs.

I'm trying to create a query for the ONLINE USERS page that will run through the records in the usersonline table, linking the IDs to the relevant record in the mobsters table. If the mobster is in a gang, I want it to also grab the record from the gangs table.

Here is my query so far (this doesn't work):

SELECT m.*, g.* FROM usersonline u 
   INNER JOIN mobsters m ON u.userID = m.id 
      JOIN gangs g ON g.id = m.gang 
         ORDER BY u.timestamp DESC

What kind of JOINs should I be using? Is this query even correct?

The gang table is optional but it should always have a record in the mobsters that relates to the usersonline.

Note: My actual query doesn't grab all fields from each table, just did it this way for simplification.

Did you run it? What did it return? What was wrong with that? A shot in the dark, but try adding LEFT OUTER to JOIN gangs and verify the results. — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Jan 5, 2012 at 22:55

Conrad Frix · Accepted Answer · 2012-01-05 22:53:15Z

2

You need a LEFT Join on the Gangs table since "The gang table is optional"

e.g.

SELECT m.*, g.* FROM usersonline u 
   INNER JOIN mobsters m ON u.userID = m.id 
      LEFT JOIN gangs g ON g.id = m.gang 
         ORDER BY u.timestamp DESC

answered Jan 5, 2012 at 22:53

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql JOINS query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related