Solved

SQL QUERY

Posted on 2011-02-28
8
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
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.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 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