asked
Select data using join across 3 tables.
Hi, I am new to SQL programming and was working on practicing sql commands. I have three tables **Tab1 tab2 tab3** in each table I have some columns that I need to select **Tab1:** col1, col2, col3 **Tab2:** Col5, col6,col8 **Tab 3:** col9, col12, col1 I need to get data across all three tables where tab1.col3= upper('y') using joins across these three tables. here is the condition I want to satisfy Tab1.Col1 = Tab2.col5 and Tab1.Col1=Tab3.Col9 and Tab 1.Col3 = upper('y') I got the syntax as select tab1.col1,tab1.col2,tab1.col3, tab2.col6, tab2.col8, tab3.col12 from tab1,tab2, tab3 where ( select * from tab1 T left join Tab2 P on
P.id =
T.id LEFT OUTER JOIN Tab3 y ON
y.id =
T.id WHERE T.app = upper('y')) for some reason I can't get this to work. any ideas? if the T.app = y then it usually returns about 25 rows but I am getting only 3 rows not sure where I am missing the point. Any help is much appreciated.
If you just want the conditions you mention (Tab1.Col1 = Tab2.Col5 and Tab1.Col1 = Tab3.Col8 and Tab1.Col3=UPPER('y')), then it's pretty straightforward. I can't work out what it is you're trying to do in the code you have supplied, but here's how I would solve it, if the requirements are as you posted. SELECT tab1.col1, tab1.col2, tab1.col3, tab2.col6, tab2.col8, tab3.col12 FROM tab1 INNER JOIN Tab2 ON Tab1.col1 = Tab2.col5 INNER JOIN Tab3 ON Tab1.Col1 = Tab3.Col9 WHERE Tab1.Col3 = UPPER('Y')
@Magnus Ahlkvist Why UPPER('Y') :) UPPER(Tab1.Col3) = 'Y' would have make more sense.
From the query in question, it looks like the collation is not case sensitive (just like in majority of SQL Server configurations), so the **Tab1.Col3 = UPPER('Y')** or more proper **UPPER(Tab1.Col3 = 'Y'** part could be restated more succinctly as Tab1.Col3 = 'Y' If SELECT
tab2.col6 etc from
Tab2 already works then surely there is no need to use the UPPER function anywhere unless the database in question is not SQL Server :)
SELECT O.OrderID,O.CustID,O.OrderTotal,C.Name, OC.OrderAmount FROM Orders as O INNER JOIN Customers as C ON O.CustID=C.CustID INNER JOIN OrderItems as OC ON O.OrderID=OC.OrderID
1 Person is following this question.
