SQL query - Selecting distinct values from a table

Question

I have a table in which i have multiple entries against a FK. I want to find out the value of FK which do not have certain entries e.g
my table has following entries.

PK----------------FK-----------------Column entries

1----------------100-----------------ab1
2----------------100-----------------ab2
3----------------100-----------------ab4
4----------------200-----------------ab1
5----------------200-----------------ab2
6----------------200-----------------ab3
7----------------300-----------------ab1
8----------------300-----------------ab2
9----------------300-----------------ab3
10---------------300-----------------ab4

Now, from this table i want to filter all those FK which do not have ab3 or ab4 in them. Certainly, i expect distinct values i.e. in this case result would be FK= 100 and 200.
The query which i am using is

select distinct(FK) 
from table1 
where column_entries != 'ab3' 
   or column_entries != 'ab4';

Certainly, this query is not fetching the desired result.

What result do you get, and how does it differ from expectations? — StilesCrisis
– StilesCrisis, Commented Jan 26, 2013 at 19:31
@StilesCrisis Result which i get is FK=100, 200,300. This because the first row of 300 does not have ab3 or ab4. — Femme Fatale
– Femme Fatale, Commented Jan 26, 2013 at 19:39
Each record satisfy condition because you used or; 'ab3' != 'ab3' or 'ab3' != 'ab4' yields true, as well as 'ab4' != 'ab3' or 'ab4' != 'ab4'. Use and or, to express intent more clearly, not in (but comparison list must not contain null). — Nikola Markovinović
– Nikola Markovinović, Commented Jan 26, 2013 at 19:39
@NikolaMarkovinović i have tried not in as well, does not bring the result i want i.e FK = 100, 200. It still bring 300 as well. — Femme Fatale
– Femme Fatale, Commented Jan 26, 2013 at 19:41
Silly me. Ab1 and Ab2 satisfy the condition. You need to select fk's having ab3 or ab4, and exclude them using outer join or not exists. — Nikola Markovinović
– Nikola Markovinović, Commented Jan 26, 2013 at 19:45

Max · Accepted Answer · 2013-01-26 19:47:40Z

5

try the following :-

select distinct fk_col from table1
minus
(select distinct fk_col from table1 where col_entry='ab3'
intersect
select distinct fk_col from table1 where col_entry='ab4')

This will show all those FKs which do not have 'ab3' and 'ab4'. i.e. 100 and 200 in your case

answered Jan 26, 2013 at 19:47

Max

4,0871 gold badge21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andriy M Over a year ago

+1, although distinct is probably not required. I am not sure but I think it is implied with both minus and intersect.

Muhammad Hani · Accepted Answer · 2013-01-26 20:35:37Z

1

The below script may be the solution if I got your question in a right way.

SELECT DISTINCT(TableForeignKey)
FROM Test
WHERE TableForeignKey NOT IN (
SELECT T1.TableForeignKey
FROM Test T1 INNER JOIN Test T2 ON T1.TableForeignKey = T2.TableForeignKey
WHERE T1.TableEntry = 'ab3' AND T2.TableEntry = 'ab4')

SQLFiddle Demo

answered Jan 26, 2013 at 20:35

Muhammad Hani

8,6725 gold badges32 silver badges44 bronze badges

Comments

Andriy M · Accepted Answer · 2013-01-26 23:38:20Z

0

You could use GROUP BY with conditional aggregation in HAVING:

SELECT FK
FROM table1
GROUP BY FK
HAVING COUNT(CASE column_entries WHEN 'ab3' THEN 1 END) = 0
    OR COUNT(CASE column_entries WHEN 'ab4' THEN 1 END) = 0
;

The two conditional aggregates count 'ab3' and 'ab4' entries separately. If both end up with results greater than 0, then the corresponding FK has both 'ab3' and 'ab4' and is thus not returned. If at least one of the counts evaluates to 0, then FK is considered satisfying the requirements.

answered Jan 26, 2013 at 23:38

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Collectives™ on Stack Overflow

SQL query - Selecting distinct values from a table

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related