Solved

SQL Command

Posted on 2011-02-22
5
209 Views
Last Modified: 2012-06-21
I have three tables as follows;

table1
t1id(pk)  wname        ver
1          some text       0

table2
t2ID(pk)  t1id(FK)
10            1
11            1
12            1

table3
woIdP(pk)  t2id(FK) status
1000           10             100
1001           11              1
1002           12              2

How can I select all records from table1 if the status of all the records from table3=100 by relating all the three tables? I am using sql server 2005.

pls help

ayha
0
Comment
Question by:ayha1999
5 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 34953494
select a.*, b.*,c.*
from table1 a , table2 b, table3 c
where a.t1id = b.t2id
and b.t2id = b.t2id
and c.status = 100
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34953506

< How can I select all records from table1 if the status of all the records from table3=100 by relating all the three tables?

select a.t1id,a.wname,a.ver
from table1 a , table2 b, table3 c
where a.t1id = b.t2id
and b.t2id = b.t2id
and c.status = 100
0
 
LVL 4

Expert Comment

by:jmnf
ID: 34953590
Exactly the same answer as sventhan

SELECT      T1.t1id,
            T1.wname,
            T1.ver
FROM      table1 AS T1,
            table2 AS T2,
            table3 AS T3
WHERE      T3.status = 100
      AND      T2.t2id = T3.t2id
      AND T1.t1id = T2.t1id
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 250 total points
ID: 34953988
two ways
select * 
from table1
inner join table2 on table2.t2ID = table1.t1id
inner join table3 on table3.t2id = table2.t2ID
where status = 100

Open in new window

select * 
from table1
inner join table2 on table2.t2ID = table1.t1id
inner join table3 on (table3.t2id = table2.t2ID and table3.status = 100)

Open in new window

0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 34958277
Please try this:
SELECT T1.T1ID,T1.wname,T1.Ver
FROM table1 T1 INNER JOIN table2 T2 ON T1.t1Id = T2.t1Id
             INNER JOIN table3 T3 ON T2.t2ID = T3.T2ID
WHERE T3.Status = 100
SELECT T1.T1ID,T1.wname,T1.Ver
FROM table1 T1 INNER JOIN table2 T2 ON T1.t1Id = T2.t1Id 
	       INNER JOIN table3 T3 ON T2.t2ID = T3.T2ID
WHERE T3.Status = 100

Open in new window

0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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