CODEWITHSUNDEEP
sqlsql-servert-sql
disaster
disaster

Reputation: 7

Writing a query

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

Upvotes: 0

Views: 90

Answers (2)

Kuba Do
Kuba Do

Reputation: 155

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";

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 0

Related Questions