Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL QUERY

Posted on 2011-02-28
8
Medium Priority
?
351 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

564 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