Solved

SQL Server Query

Posted on 2012-03-21
12
251 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

948 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

22 Experts available now in Live!

Get 1:1 Help Now