Select data from single table using Self join

subquery

I have requirement to select a data from single table. The requirement is as follows
Select data from table which has one value but not another.
for e.g
I want to select distinct ID which has value 1 but not value 8 present. For for above e.g the desired o/p should be

ID      Value
123     1
123     8
234     8
456     1
876     5
876     1
765     8
765     5

O/p
ID

456
876

Also my table contains 500K record so query should be able to execute with good performance
Any help regarding it as i am stuck.

Best Answer

Here's how i got it to work:

create table tableone (id int, value int)
insert into tableone values (123,     1);
insert into tableone values (123,     8);
insert into tableone values (234,     8);
insert into tableone values (456,     1);
insert into tableone values (876,     5);
insert into tableone values (876,     1);
insert into tableone values (765,     8);
insert into tableone values (765,     5);
Select a.*
from tableone a
left join tableone X on a.id = x.id and x.value = 8
Where a.value = 1
and X.id is null
drop table tableone

Related Solutions

Mysql – Subselect dependent on first select value

You can use 2 subqueries, one joining with the contact_id and another joining with the alt_contact_id. Then UNION them and ORDER BY:

SELECT cont.*, cred.*, 'main' AS contact_type
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.contact_id
WHERE cont.contact_id = 1

UNION ALL 

SELECT cont.*, cred.*, 'alternate'
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.alt_contact_id
WHERE cont.contact_id = 1

ORDER BY contact_id ;

If you want this for all contacts, just remove the two WHERE clauses.

Sql-server – How to select a subset from a query result

This query SUM all the distances between 6 and 7:

SELECT AssetId, [Event], GeofenceId
    , DistanceCoveredK = SUM(DistanceCoveredK)
    , TimeSpentDuringVisitSeconds
FROM (
    SELECT AssetId = vm.iAssetId
        , [Event] = c.eEventCode
        , DistanceCoveredK = p.sptGeoLocaitonPoint.STDistance(
            LEAD(p.sptGeoLocaitonPoint) OVER(PARTITION BY vm.iAssetId, vM.dtUTCDateTime ORDER BY c.dtUTCDateTime)
        )
        , GeofenceId = vm.iGeofenceId
        , TimeSpentDuringVisitSeconds = DATEDIFF(SECOND, vm.dtUTCDateTime, c.dtUTCDateTime)
    FROM VehicleMonitoringLog vm
    CROSS APPLY (
        SELECT top(1) eEventCode, dtUTCDateTime, sptGeoLocaitonPoint 
        FROM VehicleMonitoringLog WHERE iAssetId = vm.iAssetId AND dtUTCDateTime > vm.dtUTCDateTime AND eEventCode = 7
        ORDER by dtUTCDateTime
    ) c
    INNER JOIN VehicleMonitoringLog p ON p.iAssetId = vm.iAssetId AND p.dtUTCDateTime >= vm.dtUTCDateTime AND p.dtUTCDateTime <= c.dtUTCDateTime
    WHERE vm.eEventCode = 6
) g
GROUP BY AssetId, [Event], GeofenceId, TimeSpentDuringVisitSeconds

Output:

AssetId Event   GeofenceId  DistanceCoveredK    TimeSpentDuringVisitSeconds
6024    7       1015        172,642601234594    100
6024    7       1015        1210,11662866215    28393

I changed the datatype of sptGeoLocaitonPoint in your SQL Fiddle from int to geography. New SQL Fiddle is here

Best Answer

Related Solutions

Mysql – Subselect dependent on first select value

Sql-server – How to select a subset from a query result

Related Question