CODEWITHSUNDEEP
sqlsql-serversql-server-2005select
cdub
cdub

Reputation: 25711

SQL Server 2005 Joining Issues

I have the following 3 data tables

Options Table
UID (pk) SID (pk) TID (pk)
--------------------------
1        20       2
1        20       3
1        13       3
1        14       4

Types Table
TID (pk) TText
--------------------------
1        Text 1
2        Text 2
3        Text 3
4        Text 4

Sets Table
SID (pk)  SText
--------------------------
13        Set 13
14        Set 14
20        Set 20

I am trying to get the output as follows, with only 4 rows from the options table, but with the corresponding text from the other 2 tables. My joins seems to be off tho.

The result I want is:

UID (pk) SID (pk) TID (pk)  SText      TText
------------------------------------------------------
1        20       2         Set 20     Text 2
1        20       3         Set 20     Text 3
1        13       3         Set 13     Text 3
1        14       4         Set 14     Text 4

Upvotes: 2

Views: 69

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

You may try this :

SQLFIDDLE DEMO

edit: use distinct for sql server since the first query was written in mysql

select distinct o.*, t.ttext, s.stext from 
    options o
    inner join 
    types t
    on o.tid = t.tid
    left join 
    sets s
    on s.sid = o.sid
    ;

select o.*, t.ttext, s.stext from 
options o
inner join 
types t
on o.tid = t.tid
left join 
sets s
on s.sid = o.sid
group by o.uid, o.tid, o.sid
;

| UID | SID | TID |  TTEXT |  STEXT |
-------------------------------------
|   1 |  20 |   2 | Text 2 | Set 20 |
|   1 |  13 |   3 | Text 3 | Set 13 |
|   1 |  20 |   3 | Text 3 | Set 20 |
|   1 |  14 |   4 | Text 4 | Set 14 |

Upvotes: 0

John Woo
John Woo

Reputation: 263733

SELECT  a.*, b.Stext, c.TText
FROM    Options a
        INNER JOIN Sets b
            ON a.SID = b.SID
        INNER JOIN  Types c
            ON a.TID = c.TID

UPDATE 1

add DISTINCT

SELECT  DISTINCT a.*, b.Stext, c.TText
FROM    Options a
        INNER JOIN Sets b
            ON a.SID = b.SID
        INNER JOIN  Types c
            ON a.TID = c.TID

Upvotes: 1

Raj
Raj

Reputation: 10853

SELECT O.UID,O.SID,O.TID,T.TText,S.SText
FROM Options O
INNER JOIN Types T ON
    T.TID = O.TID
INNER JOIN Sets S ON
    S.SID = O.SID

Upvotes: 0

Related Questions