SQL query - select individual values ​​from a table

I have a table in which I have multiple records against FK. I want to know the FK value that has no specific records, for example
my table has the following records.

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 out all those FKs that do not have ab3 or ab4. Of course, I expect different values, that is, in this case the result will be FK = 100 and 200. The
query I am using is

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

      

Of course, this query doesn't get the desired result.

+3


source to share


3 answers


try this: -

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 that do not have "ab3" and "ab4". those. 100 and 200 in your case

+5


source


Below script might be a solution if I asked your question correctly.

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

+1


source


You can use GROUP BY with conditional aggregation on 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
;

      

Two conditional aggregates are counted 'ab3'

and 'ab4'

recorded separately. If both end up with results greater than 0, then the matching FK

has both 'ab3'

and 'ab4'

and thus is not returned. If at least one of the counters evaluates to 0, then FK

it is considered satisfying.

0


source







All Articles