CODEWITHSUNDEEP
mysql
DBunting
DBunting

Reputation: 38

MySQL query not returning the expected number of rows when using 2 Inner joins

having some trouble with a query and im hoping you all can help my see what im missing. There are three rows in COLLECTIONS that have the value of 1 for album. But for whatever reason the query is only returning 1 row instead of 3.

Any Ideas?

SELECT a.id assetID
     , a.uri
     , a.caption
     , u.alias
     , u.icon
     , u.link
     , u.id userID
  FROM collections c
  JOIN assets a
    ON a.id = c.asset 
  JOIN users u
    ON u.id = a.user 
 WHERE c.album = 1
 ORDER 
    BY a.ts DESC

Heres the data structure

COLLECTIONS

album   asset
1       1
2       2
1       3
1       4
2       5

ASSETS

id  uri                         user    private new ts          type    caption
1   1-1391997727-15835000.jpg   1       public  0   1368847153  image   this is a test file thingy
2   2-1391997760-42134600.jpg   1       public  0   1368847353  image   more test files WOOHOO  0   0
3   3-1391997811-96846600.jpg   3       public  0   1368847553  image   i took this picture wooo         
4   3-1391997840-97233100.jpg   3       public  0   1368848232  image            
5   3-1391997859-93753900.jpg   3       public  0   1368848781  image   w00t

USERS

id  alias       link    icon
1   dbunting    NULL    NULL

Upvotes: 0

Views: 81

Answers (1)

aconrad
aconrad

Reputation: 586

You are getting a single row in the resultset because there is a single matched row in table assets for user id 1.

This is a good link for how joins work: What is the difference between Left, Right, Outer and Inner Joins?

This query:

SELECT 
c.album,
    a.id assetID
     , a.uri
     , a.caption
     , u.alias
     , u.icon
     , u.link
     , u.id userID
  FROM collections c
  INNER JOIN assets a ON (a.id = c.asset AND c.album=1)
  LEFT JOIN users u ON u.id = a.user

This will output the 3 rows for album 1

Play with it at: http://www.sqlfiddle.com/#!2/00b0f/1

Upvotes: 1

Related Questions