Solved

SQL QUERY

Posted on 2011-02-28
8
295 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 8

Expert Comment

by:Anurag Agarwal
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 175 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks for your support.

ayha
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

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

18 Experts available now in Live!

Get 1:1 Help Now