CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Naief M
Naief M

Reputation: 35

Select single row from two table related using mapping table SQL Server

I have three tables: Table1, Table2, Table1Table2Mapping.

Each tabel1 data having multiple data in tabel1, this table1 relation table2 is done through the mapping table the Primary key of the table1 and table2 is put in mapping table.

Table1:

Table1_ID  Name    ,Other columns  
---------  ----     ------  
1          Name1  
2          Name2  
3          Name3

Table2:

Table2_ID   Title  
---------   -----  
101         Title1  
102         Title2  
103         Title3  
104         Title4  
105         Title5

Table1Table2Mapping:

Table1_ID  Table2_ID
---------  ------------
1          101
1          102
2          103
3          104
3          105

I am getting all the related rows from table1 and its relation in table1 through mappping table

I need to select single row for each Table1 row.

Like this

Table1_ID  Name    Title
---------  ------- -----
1          Name1   Title1
2          Name2   Title3
3          Name3   Title4

Upvotes: 0

Views: 414

Answers (2)

majik
majik

Reputation: 1

use outer apply in sql server 2005 and later

    Select Table1.*, ttt.Title From Table1 Outer Apply
(Select Top 1 Table2.* From Table2 Inner Join Table1Table2Mapping 
    On Table2.Table2_Id = Table1Table2Mapping.Table2_Id 
Where Table1.Table1_Id = Table1Table2Mapping.Table1_Id) ttt

Upvotes: 0

Pred
Pred

Reputation: 9042

The CROSS APPLY will do the trick:

SELECT
  T1.Table1_ID
  , T1.Name
  , TMP.Title
FROM
  Table1 T1
  CROSS APPLY (
    SELECT TOP(1)
      T2.Title
     FROM
      Table2 T2
      INNER JOIN Table1Table2Mapping TTM
        ON T2.Table2_ID = TTM.Table2_ID
     WHERE
      TTM.Table1_ID = T1.Table1_ID
    ORDER BY
      TTM.TAble2_ID ASC -- You can change this order to what you want
  ) TMP

You can change the order of the subquery.

SQL Fiddle

Using CROSS APPLY in TechNet

EDIT IF it is enough, you can use aggregation too:

SELECT
  T1.Table1_ID
  , T1.Name
  , MIN(T2.Title) -- You can use MAX
FROM
  Table1 T1
  INNER JOIN Table1Table2Mapping TTM
    ON  TTM.Table1_ID = T1.Table1_ID
  INNER JOIN Table2 T2
    ON T2.Table2_ID = TTM.Table2_ID
GROUP BY
  T1.Table1_ID
  , T1.Name

(This gives the same result for the provided dataset, but in real life, the result of this and the previous solution mostly different!

Upvotes: 2

Related Questions