Solved

SQL Server Query

Posted on 2012-03-21
12
259 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL profiler 3 51
Estimating my database size 7 51
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 63
SQL query 45 41
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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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 …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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