Solved

SQL QUERY

Posted on 2011-02-28
8
328 Views
Last Modified: 2012-05-11
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 RECORDS FROM table3=100 by relating all the three tables? I am using sql server 2005. The query for the above sample data should not bring any record bcz some status other than 100 is there for the same t1id

pls help

ayha
0
Comment
Question by:ayha1999
8 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35004129
You can try something like this:
Select * from table1 t1 where t1.t1id in (select t2.t1id from table2 t2 where t2.t2id in (select t3.t2id from table3 t3 where t3.status = 100))
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35004144
SELECT *
FROM table1 t1
WHERE NOT EXISTS (SELECT *
                    FROM table2 t2 INNER JOIN
                         table3 t3 ON t2.t2id=t3.t2id
                    WHERE t2.t1id = t1.t1id AND
                          t3.status <> 100
                  )

Open in new window

0
 
LVL 1

Expert Comment

by:VBisMe
ID: 35004148
SELECT *
FROM table1 t1
WHERE NOT EXISTS (SELECT 1
                   FROM table2 t2
                   INNER JOIN table3 t3 ON t3.t2id = t2.t2id
                   WHERE t2.t1id = t1.t1id
                                   AND t3.Status <> 100)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 8

Expert Comment

by:Anurag Agarwal
ID: 35004242
try sommething like this

Select table1.*  from table1 left join table2 on table1.t1id= table2.t1id left join table3 on table2.t2id=table3.t2id where table3.status=100
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 175 total points
ID: 35004496
You can also try like this.
SELECT * 
  FROM table1 t1 
       JOIN table2 t2 
         ON t1.t1id = t2.t1ID 
 WHERE EXISTS (SELECT 1 
                 FROM table3 
               HAVING MAX(status) = 100 
                      AND MIN(status) = 100)

Open in new window

0
 
LVL 7

Author Comment

by:ayha1999
ID: 35004504
jimyX's solution doesn't work if even all status=100.

Thomasian's solution retrieves records as per status but one problem e.g.
if a record exists in t1 or t1 and t2 but not in t3 then its returns that records too regardless status (there is no statusfor that record bcz it is not in the t3)

VBisMe's same as the above

anurag_onnet''s produce wrong result because it returns multiple records.

thanks

ayha
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 175 total points
ID: 35004526
In that case http:#a35004496 should work for you. If you don't need the records from table2, then you could include it with the subquery to avoid duplicate results.
SELECT *
FROM table1 t1
WHERE EXISTS (SELECT *
                FROM table2 t2 INNER JOIN
                     table3 t3 ON t2.t2id=t3.t2id
                WHERE t2.t1id = t1.t1id
                HAVING MAX(t3.status)=MIN(t3.status)
                       AND MAX(t3.status)=100
                 )                  

Open in new window

0
 
LVL 7

Author Closing Comment

by:ayha1999
ID: 35006019
thanks for your support.

ayha
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IDE for Python 5 73
ASP.NET Built-In Report Creator / Viewer 5 25
SQL query 7 18
vb.net application has warrnings about VB6 calls. 2 19
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 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