Mysql - joins - too many rows are being returned - one for each of the joined table but dont want that

Question

I have this kind of mysql db (table name/fields)

tbl_users: id, title, approved

tbl_photos: id, user_id, title, filename

I have one row in users, and three photos (all with user_id = the id of the user)

doing this:

select 
tbl_users.*, 
tbl_photos.filename 

from tbl_users 

left join tbl_photos on tbl_photos.user_id = tbl_users.id 

where tbl_users = '1' order by rand()

(table query simplified a little bit)

doing that sql query returns three rows . I only want one row (ie i want the user row, plus any random photo that has the user_id of that user id)

i've tried all joins - left right inner but they always return 3 rows

score 1 · Accepted Answer · answered May 04 '12 at 03:33

1

Your code should be like the following

select 
tbl_users.*, 
tbl_photos.filename 

from tbl_users 

left join tbl_photos on tbl_photos.user_id = tbl_users.id 

where tbl_users = approved order by rand() LIMIT 1

The LIMIT keyword will restrict the query for returning only 1 row.

A simple list of my-sql command examples can be found here http://itswadesh.wordpress.com/2011/04/12/mysql-commands/

answered May 04 '12 at 03:33

itswadesh.wordpress.com

76
10

in this exact eample i only have one user, but i want to return all users with one random photo for each user (if one exists). adding limit 1 will limit the whole query to one – May 04 '12 at 03:43
1

@notsid: If you want one row (one photo) per user, you should add that in your question. We do not have psychic powers. – ypercubeᵀᴹ May 04 '12 at 06:14

John Woo · Answer 2 · 2012-05-04T03:50:14.747

0

you also add DISTINCT:

select  DISTINCT tbl_users.id, 
        tbl_users.title, 
        tbl_users.approved, 
        tbl_photos.filename 
from tbl_users left join tbl_photos 
       on tbl_photos.user_id = tbl_users.id 
where tbl_users = 'approved' order by rand()

edited May 04 '12 at 03:50

answered May 04 '12 at 03:39

John Woo

258,903
69
498
492

the you enclose `Approved` with single quotes?it is a string so you need to enclose it. – John Woo May 04 '12 at 03:51
yea, by 'it didn't work' i meant it still returned 3 results, not an sql error – May 04 '12 at 04:28

score 0 · Answer 3 · answered May 04 '12 at 05:47

0

It is very simple if you want to get only one row for each do it like this

select 
tbl_users.*, 
GROUP_CONCAT(tbl_photos.filename) as Photos

from tbl_users 

left join tbl_photos on tbl_photos.user_id = tbl_users.id 

where tbl_users = '1' order by rand()

It will give you comma seperated values in a single field

answered May 04 '12 at 05:47

Muhammad Raheel

19,823
7
67
103

If you remove the where condition you will get all users. with each user you will get photos related to each user – Muhammad Raheel May 04 '12 at 05:51

score 0 · Answer 4 · answered May 04 '12 at 06:06

If filename is the only field you will retrieve from tbl_photos, this could deliver what you need:

select 
    tbl_users.*, 

    (select filename 
     from tbl_photos where user_id = tbl_users.id 
     order by rand()
     limit 1) as filename

from tbl_users

If you want other information from photos, this is the query:

select tbl_users.*, 'x' as separator, tbl_photos.*
from tbl_users
left join tbl_photos on tbl_photos.user_id = tbl_users.id
where (tbl_users.id, tbl_photos.id) in

    (  
    -- you can run this query independently
        select 

            id, 

            (select id 
             from tbl_photos 
             where user_id = tbl_users.id 
             order by rand() 
             limit 1) 

        from tbl_users
    )

Mysql - joins - too many rows are being returned - one for each of the joined table but dont want that

4 Answers4