Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL QUERY

Posted on 2011-02-28
8
Medium Priority
?
348 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 700 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 700 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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