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.
source to share
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')
source to share
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.
source to share