select inner join table id vb

Question

I used this first time.

SELECT classroom .cls_id, 
   classroom.cls_name, 
   u1.users_id as users_id1,
   u2.users_id as users_id2
FROM   classroom 
   INNER JOIN clsown 
           ON clsown.cls_id = classroom .cls_id 
   INNER JOIN users AS u1 
           ON clsown.users_id = u1.users_id 
   INNER JOIN users AS u2
           ON clsown.users_id = u2.users_id

I get this:

cls_id  cls_name  users_id1  users_id2

1 -------- room1 ------- 1 --------- 1
1 -------- room1 ------- 2 --------- 2
2 -------- room2 ------- 3 --------- 3
2 -------- room2 ------- 4 --------- 4

i want to see

cls_id  cls_name  users_id1  users_id2

1 -------- room1 ------- 1 --------- 2
2 -------- room2 ------- 3 --------- 4

I do not know how to do inner join. What am i doing wrong?

Thank for answer.

Where are the 2 `users_id` coming from? Your query is constraining where those fields must be equal in `clsown` and `u1` so I'm confused how you are wanting them to be different. — David Starkey, Mar 26 '13 at 15:46
@Harajukuzz Are you only ever going to have 2 users? What RDBMS are you using? — Taryn, Mar 26 '13 at 17:08

score 1 · Answer 1 · answered Mar 26 '13 at 16:23

The query that you are noting here would actually require a pivot as you want to take the unique values for cls_id and cls_name and then order the users into columns.

I wrote this article back in 2009 that shows how to do something like this. It should help

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

I assume that you are using Mysql as your RDBMS, if so you can make use of GROUP_CONCAT() to display the records as per your need, but in a different form. i.e] This query will stuff all the additional coloumns you wanted, into a single cell separated by a separator.

SELECT classroom .cls_id, 
       classroom.cls_name, 
       GROUP_CONCAT(u1.users_id ORDER BY u1.users_id SEPARATOR ', ') Users 
FROM   classroom 
       INNER JOIN clsown 
               ON clsown.cls_id = classroom .cls_id 
       INNER JOIN users AS u1 
               ON clsown.users_id = u1.users_id
       Group by classroom .cls_id

The above query will return records like below,

cls_id  cls_name    users

  1      room1       1,2

  2      room2       3,4

EDIT: Reference for achieving the above result in Sql server

score 0 · Accepted Answer · answered Mar 26 '13 at 18:08

Since you are using SQL Server there are a few ways that you can pivot the data.

You can use an aggregate function with a CASE expression:

select cls_id,
    cls_name,
    MAX(case when rn = 1 then users_id end) user_id1,
    MAX(case when rn = 2 then users_id end) user_id2
from
(
    SELECT cr.cls_id, 
        cr.cls_name, 
        u1.users_id,
        ROW_NUMBER() over(partition by cr.cls_id order by u1.users_id) rn
    FROM classroom cr
    INNER JOIN clsown co
        ON co.cls_id = cr.cls_id 
    INNER JOIN users AS u1 
        ON co.users_id = u1.users_id 
) d
group by cls_id, cls_name;

Or you can use the PIVOT function:

select cls_id,
    cls_name,
    user_id1, 
    user_id2
from
(
    SELECT cr .cls_id, 
        cr.cls_name, 
        u1.users_id,
        'user_id'+cast(ROW_NUMBER() over(partition by cr.cls_id order by u1.users_id) as varchar2)) col
    FROM classroom cr
    INNER JOIN clsown co
        ON co.cls_id = cr.cls_id 
    INNER JOIN users AS u1 
        ON co.users_id = u1.users_id 
) d
pivot
(
    max(users_id)
    for col in (user_id1, user_id2)
) piv;

@Harajukuzz I don't understand what you are asking, please ask a new question with the new details? — Taryn, Mar 27 '13 at 13:50
http://stackoverflow.com/questions/15664926/select-inner-join-column2-null new question — Harajukuzz, Mar 27 '13 at 17:13

select inner join table id vb

3 Answers3