Complex? MySQL Join SELECT query

Question

I'm trying to get a list of 'contacts' for a specified user id. Let says my user id is 1, i need to get the list of ids of my my contacts from chat-contactlist then get all the infos for each id.

All users' id, name and contact information

Table usr: uid, rname, phonenumber

Online status and other stuff

Table chat-usr: uid, nickname, online_status

Containing user id and the user id of each contact this user have :

Table chat-contactlist: uid, cid (cid = The id of the person who's int he "uid" user list

So I need the name, the nickname, the online_status for all the 'cid' for a specified 'uid'... Dont know i read a tutorial about left join but it seams complex to merge multiple tables, anyone wanna try? Any recommendation?

Thank you

EDIT

Changing name by rname because name is a reserved word for SQL.

You will use left join if there's no data in some table, is this te case? chat-usr will always have the data of the user or not? — , Jan 27 '12 at 18:01
chat-usr only contain online status and nick name, usr contain al the informations for the user and since the online_status will be updated often i want it the fastest possible and the usr table contain all the informations on all users so it might be huge. — Jeremy Dicaire, Jan 27 '12 at 18:08

Akhil · Answer 1 · 2012-01-27T18:57:33.503

2

SELECT  ccl.uid, 
        Name            = u.Name,
        Nickname        = cu.nickname,
        OnlineStatus    = cu.onlinestatus
FROM    chat-contactlist ccl
        JOIN chat-usr cu ON ccl.cid = cu.uid
        JOIN usr u       ON u.uid   = cu.uid
Where   ccl.uid = @uid /* your filter here */

edited Jan 27 '12 at 18:57

answered Jan 27 '12 at 18:05

Akhil

7,570
1
24
23

Uhoh, get an error near '-contactlist ccl JOIN chat-usr cu ON ccl.cid = cu.uid JOIN usr' at line 5' – Jeremy Dicaire Jan 27 '12 at 18:25
1

can you enclose the table names with "`", such as 'chat-usr', 'chat-contactlist' – Akhil Jan 27 '12 at 18:41
I've put JOIN `usr` u [...] now the error is [Err] 1052 - Column 'uid' in field list is ambiguous... ?! Very good error description, haha! – Jeremy Dicaire Jan 27 '12 at 18:46
you can resolve it by saying ccl.uid. Ive updated the answer to reflect it! – Akhil Jan 27 '12 at 18:57
Okay thx its working now, but it doesnt get the good infos, the name is empty and the online_status shows 1 and in the table I've put 0... Wierd – Jeremy Dicaire Jan 27 '12 at 19:32

score 2 · Answer 2 · edited May 23 '17 at 12:33

2

This is a 3-table join where you have a many-to-many relationship defined by the linker table chat-contactlist:

SELECT u.name username, c.nickname chat_username, c.online_status
FROM chat-contactlist cc
    JOIN usr u ON (u.uid = cc.uid)
    JOIN chat-usr c ON (c.uid = cc.cid);

This is an explicit join. If you are looking to select a specific user, you would add that in WHERE clauses afterward.

SELECT u.name username, c.nickname chat_username, c.online_status
FROM usr u, chat-usr c, chat-contactlist cc
WHERE cc.uid = u.uid
AND cc.cid = c.uid;

This is an implicit join, where the tables are related by equality in WHERE statements. It isn't recommended to use but I find them sometimes easier to read.

More info:

edited May 23 '17 at 12:33

Community

1
1

answered Jan 27 '12 at 18:12

JYelton

35,664
27
132
191

Thanks for the links and the answer but have a sql syntaxt error starting 'near' FROM. I've replaced name by rname too cause name is a reserved word in SQL – Jeremy Dicaire Jan 27 '12 at 18:43
You can use backticks (`) to escape reserved words, i.e. \`NAME\`. – JYelton Jan 27 '12 at 18:50

Complex? MySQL Join SELECT query

2 Answers2