none
How to join a table with the result of another join between two table RRS feed

  • Question

  • i have table1, table2 and table3. now i want to join first between table1 & table2 and then want to join table3 with result of join between two first tables. how it is possible without storing first join result in to temporary table. idea please. thanks
    Saturday, November 16, 2019 7:58 PM

Answers

  • i have table1, table2 and table3. now i want to join first between table1 & table2 and then want to join table3 with result of join between two first tables. how it is possible without storing first join result in to temporary table. idea please. thanks

    If the query is simple and straightforward, you can have a single select else use Common table expression like below:

    ;WITH CTE
    AS (
    	-- Some complex logic can be added in below
    	SELECT a.Id, a.col1, b.col1
    	FROM Table1 AS a
    	INNER JOIN Table2 AS b ON a.Id = b.Id
    	)
    SELECT c.Id, cte.col1, cte.col2
    FROM Table3 AS c
    INNER JOIN cte AS cte ON c.Id = cte.id


    If the response helped, do "Mark as answer" or Upvote it
    - Vaibhav

    • Marked as answer by Sudip_inn Sunday, November 17, 2019 3:55 PM
    Sunday, November 17, 2019 5:37 AM

All replies

  • Hi Sudip,

    You can do multiple joins in one SQL query

    SELECT A.NAME, B.NAME, C.NAME
    FROM TABLE_A A
    JOIN TABLE_B B
    ON B.ID = A.ID
    JOIN TABLE_C C
    ON C.ID = B.ID
    

    if you are doing outer joins, ISNULL function can be used to join with appropriate record 

    SELECT A.NAME, B.NAME, C.NAME
    FROM TABLE_A A
    FULL OUTER JOIN TABLE_B B
    ON B.ID = A.ID
    JOIN TABLE_C C
    ON C.ID = ISNULL(A.ID, B.ID)
    Follow some examples here


    Rajkumar

    Sunday, November 17, 2019 2:09 AM
  • i have table1, table2 and table3. now i want to join first between table1 & table2 and then want to join table3 with result of join between two first tables. how it is possible without storing first join result in to temporary table. idea please. thanks

    If the query is simple and straightforward, you can have a single select else use Common table expression like below:

    ;WITH CTE
    AS (
    	-- Some complex logic can be added in below
    	SELECT a.Id, a.col1, b.col1
    	FROM Table1 AS a
    	INNER JOIN Table2 AS b ON a.Id = b.Id
    	)
    SELECT c.Id, cte.col1, cte.col2
    FROM Table3 AS c
    INNER JOIN cte AS cte ON c.Id = cte.id


    If the response helped, do "Mark as answer" or Upvote it
    - Vaibhav

    • Marked as answer by Sudip_inn Sunday, November 17, 2019 3:55 PM
    Sunday, November 17, 2019 5:37 AM