Joining tables without duplicate

Question

I have 3 SQL tables as below:

Table 1

ItemId Name
----------
A      aa    
B      bb

Table 2

ItemId Category
----------
A      1
A      2
A      3
B      1

Table 3

ItemId  Dep
----------
A       D1
B       D2
B       D3

I need result as this

ItemId Name Category Dep
------------------------
A      aa   1        D1
            2
            3   
B      bb   1        D2
                     D3

Is there any way to get this result without looping tables?

What do you mean by Looping tables? can you explain further? :-) — youji.xii, Oct 07 '15 at 05:36
If you add `A` `D4` to table3, what the result should look like? — Vladimir Baranov, Oct 07 '15 at 06:17

score 1 · Answer 1 · answered Oct 07 '15 at 05:49

1

You can first JOIN the tables on ItemId and then use ROW_NUMBER and RANK for formatting.

I suggest you do the display format in the client side

SQL Fiddle

WITH CTE AS(
    SELECT
        t1.ItemId, t1.Name, t2.Category, t3.Dep,
        Rn_Cat  = ROW_NUMBER() OVER(PARTITION BY t1.ItemId, t1.Name ORDER BY t2.Category),
        Rn_Dep  = ROW_NUMBER() OVER(PARTITION BY t1.ItemId, t1.Name ORDER BY t3.Dep),
        Rnk_Cat = RANK() OVER(PARTITION BY t1.ItemId, t1.Name ORDER BY t2.Category),
        Rnk_Dep = RANK() OVER(PARTITION BY t1.ItemId, t1.Name ORDER BY t3.Dep)
    FROM Table1 t1
    LEFT JOIN Table2 t2
        ON t2.ItemId = t1.ItemId
    LEFT JOIN Table3 t3
        ON t3.ItemId = t1.ItemId
)
SELECT
    ItemId = CASE WHEN Rn_Cat = 1 THEN ItemId ELSE '' END,
    Name = CASE WHEN Rn_Cat = 1 THEN Name ELSE '' END,
    Category = CASE WHEN Rn_Cat = Rnk_Cat THEN CONVERT(VARCHAR(10), Category) ELSE '' END,
    Dep = CASE WHEN Rn_Dep = Rnk_Dep THEN CONVERT(VARCHAR(10), Dep) ELSE '' END
FROM CTE

answered Oct 07 '15 at 05:49

Felix Pamittan

31,544
7
41
67

If you add `A` `D4` to table 3 the result doesn't look right. – Vladimir Baranov Oct 07 '15 at 06:18
I see. I guess there's really no simple way to achieve the desired display. That's why I suggested doing the formatting on the client side. – Felix Pamittan Oct 07 '15 at 06:25
This question looks similar to this: http://stackoverflow.com/questions/27515577/best-way-to-glue-columns-together/27523439#27523439 I've asked OP to clarify, hope he'll add few more rows to the sample in the question. – Vladimir Baranov Oct 07 '15 at 06:28
Yeah, I hope the OP does too. Great answer BTW! – Felix Pamittan Oct 07 '15 at 06:30

score 0 · Answer 2 · answered Oct 07 '15 at 06:08

May be Using UNION

Fiddle Here

WITH CTE AS(
    SELECT
    t2.ItemId,t1.Name,t2.Category,t3.Dep,
  Rank() over(Partition by t1.ItemId,t1.Name order by t2.Category,t3.Dep) as rn
  from 
  Table1 t1 join Table2 t2 on t1.ItemId=t2.ItemId
  join Table3 t3 on t1.ItemId=t3.ItemId
)
SELECT
ItemId,Name,Category,Dep,Rn
FROM CTE where rn=1
union 
SELECT
'','',Category,Dep,Rn
FROM CTE where rn>1

score 0 · Answer 3 · answered Oct 07 '15 at 06:49

this will probably work:

;with cte_t1 as
(
select  'A' as ItemId, 'aa' as Name
union
select  'B' as ItemId, 'bb' as Name
),
cte_t2 as
(
select 'A' AS ItemId, 1 as Category
union
select 'A' AS ItemId, 2 as Category
union
select 'A' AS ItemId, 3 as Category
union
select 'B' AS ItemId, 1 as Category
),
cte_t3 as
(
select 'A' AS ItemId, 'D1' as Dep
union
select 'B' AS ItemId, 'D2' as Dep
union
select 'B' AS ItemId, 'D3' as Dep
),
cte_t4 as
(
SELECT T1.ItemId, t1.Name, T2.Category, T3.Dep, ROW_NUMBER() over(order by T1.ItemId, T2.Category) RowNumber
FROM cte_t1 t1 inner join cte_t2 t2
    on t1.ItemId = t2.ItemId 
        inner join cte_t3 t3
            on t1.ItemId = t3.ItemId
            and t2.ItemId = t3.ItemId
)
select  
case when a.ItemId = b.ItemId then '' else a.ItemId end as ItemId, 
case when a.Name = b.Name then '' else a.Name end as Name, 
case when a.Category = b.Category then '' else cast(a.Category as varchar(100)) end as Category, 
case when a.Dep = b.Dep then '' else a.Dep end as Dep
from cte_t4  a
    left join cte_t4 b
        on a.RowNumber-1= b.RowNumber

score 0 · Answer 4 · answered Oct 07 '15 at 07:18

Using this code -

SELECT table1.ItemID,
       table1.Name,
       table2.Category,
       table3.Dep
FROM table1, table2, table3
WHERE table1.ItemID = table2.ItemID AND table1.ItemID = table3.ItemID;

produces this output -

+--------+------+----------+-----+
| ItemID | Name | Category | Dep |
+--------+------+----------+-----+
| A      | aa   | 1        | D1  |
| A      | aa   | 2        | D1  |
| A      | aa   | 3        | D1  |
| B      | bb   | 1        | D2  |
| B      | bb   | 1        | D3  |
+--------+------+----------+-----+

Is this what you are after, or do you wish to display a blank space for every repeat of a value?

Thanks for the reply. But there are repeating category and dep for each itemID in your answer. Category 1 is repeating for itemId B and Dep D1 is repeating for the itemid A — Snj, Oct 07 '15 at 07:46

Joining tables without duplicate

4 Answers4