Writing a query

Question

I have a task to write a query who will list all data from multiple tables.

So i have:

Table1 with ID and some other columns Table2 with ID, ID_ Table1 and some other columns Table3 with ID and some other columns. But Table3 is not related with the other two tables. Also number of rows are not the same.

So i need to get a result like this:

column from Table3, column from Table2 and new column which i will get if I compare the results from Table3 and Table2.

I ve done that with this code, but i dont know how to join the tables:

SELECT [ColumnFromTable3],
CASE
    WHEN [ColumnFromTable3] IN ('0001', '7004', '1004', '7001',  '8001', '7014',  '7012', '7015', '7006') THEN 'R1'
    WHEN [ColumnFromTable3] IN ('9001', '9017') THEN 'R2'
    WHEN [ColumnFromTable3] IN ('9003', '9006') THEN 'R3'
    WHEN [ColumnFromTable3] IN ('9005', '9008', '9004') THEN 'R4'
    ELSE 'OTHER'
END AS [NewColumn3]
FROM [dbo].[Table3]

The problem is that Table3 is not related with any other table and i need one column from that table.

Can you please suggest me how can i solve this. Thank you

How do you expect a relational database system to be able to connect data from table X, which you say is not related to data in table Y, to table Y? There MUST be some rule that you, as a human, would follow to say "oh, when it says 3 in table X, that relates to 'blue' in table Y" or "the first 3 rows in X are related to the first in Y, the 4, 5, 6 in X are related to the second row of Y" - even a crazy rules like this can be coded for, but *there has to be a rule* otherwise you cannot relate the data — Caius Jard, Jul 30 '19 at 10:10

score 1 · Answer 1 · answered Jul 30 '19 at 10:17

Generally, there are several ways to join tables it depends on result which You expect, please check following What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

example of right JOIN from my program :

        string sqlCheck = @"SELECT e.GBC, e.Replaced, e.Description, Barcode, Location, Quantity, Buildneed, p.Quantity - e.Buildneed as Afterbuild FROM Parts p Right JOIN Excel e ON e.GBC = p.GBC";

score 0 · Answer 2 · answered Jul 30 '19 at 10:10

one method you can use row_number() and use left join

with cte as
(
select row_number() over(order by col) rn
from table1
),cte1 as
(
SELECT [ColumnFromTable3],row_number() over(order by col) rn1,
CASE
    WHEN [ColumnFromTable3] IN ('0001', '7004', '1004', '7001',  '8001', '7014',  '7012', '7015', '7006') THEN 'R1'
    WHEN [ColumnFromTable3] IN ('9001', '9017') THEN 'R2'
    WHEN [ColumnFromTable3] IN ('9003', '9006') THEN 'R3'
    WHEN [ColumnFromTable3] IN ('9005', '9008', '9004') THEN 'R4'
    ELSE 'OTHER'
END AS [NewColumn3]
FROM [dbo].[Table3]
) select cte.*,cte1* from cte left join cte1 on cte.rn=cte1.rn1

Writing a query

2 Answers2