where exists question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

where exists question

Author

Topic

theroversreturn
Starting Member

12 Posts

Posted - 2011-04-01 : 09:26:12

Hi,
I have a 2 table set up which are linked by a record_id and version. The first table "f_cords" contains all the stuff like data_added, updated etc while the second table is a bus table containing values for datapoints corresponding to a record and version. So the set up would be similiar to the following.

Records
===
Record_id/version/data_added...
234/2/ts{'31/12/11'}..

Datapoint_bus
===
Record_id/version/datapoint_id/local_value
234/2/12/0.56
234/2/14/1.3
234/2/21/1
240/1/14/45

Basically what I want to do is return all records where I have a value in the datapoint_bus table for datapoint_id 3 but don't have a corresponding entry for datapoint_id 35 for the same record_id and version. I have tried a few efforts but none being successfull one being the following
===

select *
from f_records f
left join record_datapoint_bus b on f.record_id=b.record_id and f.version=b.version
where not exists (select * from f_records f1
					left join record_datapoint_bus b1 on f1.record_id=b1.record_id and f1.version=b1.version where datapoint_id = 35 and qc_status=5)
and exists (select * from f_records f1
					left join record_datapoint_bus b1 on f1.record_id=b1.record_id and f1.version=b1.version where datapoint_id = 3 and qc_status=5)
and f.qc_status=5

Any help would be great and if im being a bit vague let me know

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-01 : 09:56:33

Might be too early for me to understand, but here is a shot:

SELECT *
FROM 
    Records
INNER JOIN    
    Datapoint_bus AS A
    ON Records.Record_id = A.RecordID
    AND Records.Version = A.Version 
    AND A.datapoint_id = 35 
    AND A.qc_status=5
WHERE
    NOT EXISTS 
    (
        SELECT *
        FROM Datapoint_bus AS B
        WHERE
            datapoint_id = 35 and qc_status=5
            AND 
            A.record_id = B.record_id and A.version = B.version 
    )

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-01 : 09:58:53

ALso a quick conversion to a LEFT OUTER JOIN:

SELECT *
FROM 
    Records
INNER JOIN    
    Datapoint_bus AS A
    ON Records.Record_id = A.RecordID
    AND Records.Version = A.Version 
    AND A.datapoint_id = 35 
    AND A.qc_status=5
LEFT OUTER JOIN
    (
        SELECT *
        FROM Datapoint_bus AS B
        WHERE
            datapoint_id = 35 and qc_status=5
    ) AS T
    ON A.record_id = T.record_id 
    AND A.version = T.version 
WHERE
    T.record_id IS NULL

theroversreturn
Starting Member

12 Posts

Posted - 2011-04-01 : 10:15:55

A modification of your first response looks like it works ok.

SELECT *
FROM 
    financial_records
INNER JOIN    
    record_datapoint_bus AS A
    ON financial_records.record_id = A.record_id
    AND financial_records.version = A.version 
    AND A.datapoint_id = 3 
    AND qc_status=5
WHERE
    NOT EXISTS 
    (
        SELECT *
        FROM record_datapoint_bus AS B
        WHERE
            datapoint_id = 35 and qc_status=5
            AND 
            A.record_id = B.record_id and A.version = B.version 
    )

The only modification was the 35 changed to 3 from the inner join which Id say was just a typo. Thanks for that much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources