Solved

# SQL Command

Posted on 2011-02-22
205 Views
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
Question by:ayha1999

LVL 18

Expert Comment

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

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

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

ewangoya 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
``````
``````select *
from table1
inner join table2 on table2.t2ID = table1.t1id
inner join table3 on (table3.t2id = table2.t2ID and table3.status = 100)
``````
0

LVL 6

Expert Comment

ID: 34958277
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
``````
0

## Featured Post

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 article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.