Solved

SQL Server Query

Posted on 2012-03-21
12
247 Views
Last Modified: 2012-06-21
I am trying to write a query on SQL Server to display results from 3 tables, called:
Table1, Table2 and Table3
WHERE Table1.ColumnA='x' and Table2.ColumnB='y' and Table3.columnC='z'
Any ideas?   Thanks.
0
Comment
Question by:fjkaykr11
  • 6
  • 5
12 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 37749062
do you have a common column to join between these 3 tables?
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 37749262
Yes there is an ID column listed in all 3 tables.
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37749340
select t2.id, t2.loginname,t2.wrknum,t1.time,t3.description
from table2 t2
inner join table1 t1 on t1.id=t2.id
inner join table3 t3 on t3.id=t2.id
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 37749385
Thanks for the reply. I will give this a try.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 37749426
see if this works ..

select t1.ColumnA,t2.ColumnB,t3.ColumnC
from table1 t1,table2 t2,table3 t3
inner join table1 t1 on t1.id=t2.id
inner join table3 t3 on t3.id=t2.id
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 37749461
@ashok111.  I just realized this won't work. I need to filter for specific criteria in each column with the where clause (as posted in my original question).  
WHERE Table1.ColumnA='x' and Table2.ColumnB='y' and Table3.columnC='z'
Please advise how can I do this.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:sventhan
ID: 37749521
just add the filter as below...

select t1.ColumnA,t2.ColumnB,t3.ColumnC
from table1 t1,table2 t2,table3 t3
inner join table1 t1 on t1.id=t2.id
inner join table3 t3 on t3.id=t2.id
WHERE Table1.ColumnA='x' and Table2.ColumnB='y' and Table3.columnC='z'
0
 
LVL 18

Expert Comment

by:sventhan
ID: 37749523
it should be
select t1.ColumnA,t2.ColumnB,t3.ColumnC
from table1 t1,table2 t2,table3 t3
inner join table1 t1 on t1.id=t2.id
inner join table3 t3 on t3.id=t2.id
WHERE T1.ColumnA='x' and T2.ColumnB='y' and T3.columnC='z'
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 37749692
Is there way to write this without doing an alias name? I keep getting multiple syntax errors, no matter what I change I can't getting it when trying to follow the syntax logic above.
errors include:
The correlation name 'T1' has the same exposed name as table 'T1'.
or
The Table name T1 is specified multiple times in the FROM Clause
or
Msg 107, Level 16, State 2, Line 1
The column prefix 'T1' does not match with a table name or alias name used in the query.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 37749742
When I remove all references to the alias names, I get the error:
Msg 107, Level 16, State 2, Line 1
The column prefix 'Table1' does not match with a table name or alias name used in the query.
0
 
LVL 18

Accepted Solution

by:
sventhan earned 500 total points
ID: 37749850
sorry my bad

try now...

select t1.ColumnA,t2.ColumnB,t3.ColumnC
from table1 t1,table2 t2,table3 t3
where t1.id = t2.id
and t2.id = t3.id
and T1.ColumnA='x'
and T2.ColumnB='y'
and T3.columnC='z'
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 37750667
@sventhan that worked!  Thanks so much for following up.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql server lock cursor 13 41
MSSQL 2014 Query Synthax 8 38
Sql Stored Procedure 26 28
SQL Server can be started but not accessed 1 14
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now