Solved

SQL Server Query

Posted on 2012-03-21
12
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

615 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