How to show result between 3 tables?

Question

How to show result between 3 tables?

Navigation

#1 by (0 votes)

3

I have these three tables;

    table_1
    |---------------------|
    |   ID1 |     name    |
    |---------------------|
    |   1   |  'xxxxx'    |
    |   2   |  'xxxxxx'   |
    |   3   |  'xxxxxxxx' |
    |   4   |  'xxxxxx'   |
    |   5   |  'xxxxxx'   |
    |---------------------|

    table_2
    |---------------------|
    |   ID2 |     date    |
    |---------------------|
    |   1   |     '1a'    |
    |   2   |     '2b'    |
    |   3   |     '3c'    |
    |---------------------|


    table_3
    |--------------------------------------|
    |   ID2_fk  |     valu    |   ID1_fk   |
    |--------------------------------------|
    |     1a    |  'value1'   |      1     |
    |     2b    |  'value2'   |      1     |
    |     3c    |  'value3'   |      3     |
    |--------------------------------------|

And I need to show something similar to this:

Eu preciso de um select que mostra isso
|------------------------------------------------------|
|   ID1 |     name    |    1a    |    2b    |    3c    |
|------------------------------------------------------|
|   1   |  'xxxxx'    | 'value1' | 'value2' |          |
|   2   |  'xxxxxx'   |          |          |          |
|   3   |  'xxxxxxxx' |          |          | 'value3' |
|   4   |  'xxxxxx'   |          |          |          |
|   5   |  'xxxxxx'   |          |          |          |
|------------------------------------------------------|

How do I? Remember that table_2 varies.

sql sql-select sql-server

asked by anonymous 21.03.2016 / 15:21

1 answer

Return error message for database queries with PHP Add line in JTable when clicking the button

score 0 · Accepted Answer

declare @table_1 table
(
   ID1 int,    
   name varchar(100)
)

insert into @table_1 values
(1   ,  'xxxxx'  )
,(2   ,  'xxxxxx'  )
,(3   ,  'xxxxxxxx' )
,(4   ,  'xxxxxx'  )
,(5   ,  'xxxxxx' )


declare @table_2 table
(
   ID1 int,    
   date varchar(100)
)

insert into @table_2 values
( 1  ,      '1a'    ),
( 2  ,      '2b'     ),
( 3 ,       '3c'     )


declare @table_3 table
(
   ID2_fk varchar(100),    
   value varchar(100),    
   ID1_fk int

)

insert into @table_3 values
('1a',      'value1'     ,    1   ),
('2b',      'value2'     ,    1    ), 
('3c',     'value3'     ,    3     )


SELECT *
FROM
(SELECT t1.*, t3.value, t3.ID2_fk
    FROM @table_1 t1
    left join @table_3 t3
    on t3.ID1_fk = t1.ID1
    ) AS SourceTable
PIVOT
(
   MAX(value)
   FOR SourceTable.ID2_fk IN ([1a],[2b], [3c])
) AS PivotTable
order by ID1

Just a detail here you will need to specify the columns in FOR SourceTable.ID2_fk IN ([1a],[2b], [3c]) , if you use the columns of table_2 , you will need to read them to play inside the IN ([]).