Help joining tables

[slj90]

I currently use the following query to get status's and count the number of likes from 2 different tables. However, I now want to join the user table to this query.

SELECT s.*, COUNT(l.likes_location_id) AS likeCount
 FROM stream AS s
  LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
 ORDER BY s.stream_id DESC LIMIT 50

What do I need to add to include the 'user' table?

 

Also, could anyone recommend a good tutorial for joining tables?

Many thanks,

 

[slj90]

I currently use the following query to get status's and count the number of likes from 2 different tables. However, I now want to join the user table to this query.

SELECT s.*, COUNT(l.likes_location_id) AS likeCount
 FROM stream AS s
  LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
 ORDER BY s.stream_id DESC LIMIT 50

What do I need to add to include the 'user' table?

 

Also, could anyone recommend a good tutorial for joining tables?

 

Many thanks,

Trial and error...

SELECT s.*, u.*, COUNT(l.likes_location_id) AS likeCount
 FROM stream AS s
 LEFT JOIN users as u ON (u.users_username = s.stream_username)
  LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
 ORDER BY s.stream_id DESC LIMIT 50

[Psycho]

A few things:

 

1. Don't use SELECT * unless you really need everything from those tables. Requesting all the columns takes more resources to run the query and will not scale well.

 

2. Don't use a LEFT/RIGHT JOIN unless it is necessary. Again, it uses more resources than needed. I would assume that every stream has an association with a user - if so, no LEFT JOIN would be needed to the users table.

 

3. Are you really using the username as the primary and foreign key? Normally an INT type field would be used and be indexed for performance.

[slj90]

A few things:

 

1. Don't use SELECT * unless you really need everything from those tables. Requesting all the columns takes more resources to run the query and will not scale well.

 

2. Don't use a LEFT/RIGHT JOIN unless it is necessary. Again, it uses more resources than needed. I would assume that every stream has an association with a user - if so, no LEFT JOIN would be needed to the users table.

 

3. Are you really using the username as the primary and foreign key? Normally an INT type field would be used and be indexed for performance.

 

Thanks for the reply,

 

I am using all columns in the tables except for the users table so I will change that to just get the required data.

 

How would I write it without the LEFT JOIN? I have tried it with just "JOIN" and that doesn't give any results.

 

I am using the username to make the relationship in this case but it's not the primary key in the tables. Is using it to make the relationship a problem?

 

Thanks for your help