SQL query that shows overlapping rows in the same table?
I've searched on Google and SO for an answer to this, but with difficulty. Not even sure if I'm asking the right thing, but I'm going to give it a better result:
My details:
Fruit | Attributes
-------------------
Apple | Dark Red
Apple | Green
Apple | Yellow
Apple | Light Red
Apple | Greenish Yellow
Apple | Dark Yellow
Apple | Brown
Banana | Yellow
Banana | Greenish Yellow
Banana | Dark Yellow
Banana | Yellow
Banana | Brown
Banana | Red
Banana | Black
What I would like to do is run a query that outputs the number of attributes for all fruits (in this case, only apples and bananas) that overlap.
Hope I was clear ... please let me know if I need to clarify.
+3
source to share
1 answer
Something like that?
SELECT DISTINCT ATTRIBUTE, FRUIT
FROM FRUIT_TABLE
WHERE ATTRIBUTE IN (
SELECT ATTRIBUTE
FROM FRUIT_TABLE
GROUP BY ATTRIBUTE
HAVING COUNT(DISTINCT FRUIT_NAME) > 1 )
ORDER BY ATTRIBUTE, FRUIT_NAME
I tried it with City and State in a similar way and it worked:
SELECT DISTINCT CITY, STATE
FROM ADDRESS
WHERE CITY IN (
SELECT CITY
FROM ADDRESS
GROUP BY CITY
HAVING COUNT(DISTINCT STATE) > 1)
ORDER BY CITY, STATE
- BATH NB
- BATH ON
- BEDFORD NS
- BEDFORD QC
- BRANDON MB
- BRANDON MT
- Brampton NB
- Brampton ON
... etc.
SELECT ATTRIBUTE, COUNT(*) FROM (
SELECT DISTINCT ATTRIBUTE, FRUIT
FROM FRUIT_TABLE
WHERE ATTRIBUTE IN (
SELECT ATTRIBUTE
FROM FRUIT_TABLE
GROUP BY ATTRIBUTE
HAVING COUNT(DISTINCT FRUIT_NAME) > 1 )
ORDER BY ATTRIBUTE, FRUIT_NAME ) AS RESULTS
GROUP BY ATTRIBUTE
+2
source to share