• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

SQL Command

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
ayha1999
Asked:
ayha1999
1 Solution
 
sventhanCommented:
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
 
sventhanCommented:

< 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
 
jmnfCommented:
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
 
Ephraim WangoyaCommented:
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
 
Rajesh_mjCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now