Solved

SQL Command

Posted on 2011-02-22
5
205 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:
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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now