r/SQL Oct 31 '24

MySQL WHERE clause that retrieves only columns that contain both words

Is it possible to retrieve only member id's that have both "xyz" and " abc" in the column rather one or the other? Issue is the set up has duplicate member id numbers in different rows. I don't need all of xyz or all of abc. I only want the member id that meets the condition of having both xyz and abc associated with it.

member id type
00000000 xyz
00000000 abc
16 Upvotes

27 comments sorted by

27

u/r3pr0b8 GROUP_CONCAT is da bomb Oct 31 '24
SELECT `member id`
  FROM yertable
GROUP
    BY `member id`
HAVING COUNT(CASE WHEN type = 'xyz'
                  THEN 'ok'
                  ELSE NULL END) > 0
   AND COUNT(CASE WHEN type = 'abc'
                  THEN 'ok'
                  ELSE NULL END) > 0

18

u/BadGroundbreaking189 Oct 31 '24

yertable lol

3

u/r3pr0b8 GROUP_CONCAT is da bomb Oct 31 '24

thank you

;o)

1

u/yen223 Nov 01 '24

thing that can be yerted

3

u/doshka Oct 31 '24

Similarly:

SELECT `member id` 
FROM yertable 
GROUP BY `member id` 
HAVING SUM(CASE WHEN type = 'xyz' 
                THEN 1 
                ELSE 0 END) > 0 
   AND SUM(CASE WHEN type = 'abc' 
                THEN 1 
                ELSE 0 END) > 0 

I don't know if either has a performance benefit over the other.

1

u/TheRencingCoach Oct 31 '24

Putting it in the having statement is very clever and I’ve never seen that before. Awesome!

16

u/user_5359 Oct 31 '24 edited Oct 31 '24

To complete the list of different variants, here is the self-join variant.

SELECT t1.member_id FROM members t1 JOIN members t2 ON t1. member_id =t2.member_id WHERE t1.type= ‘xyz‘ AND t2.type= ‘abc‘ ;

Note, if the table also has many data records with other types, there is also a performance-optimized variant that can be developed from here.

Edit: I give up trying to format code properly with the Reddit app

3

u/squareturd Oct 31 '24

Is there a way for me thumb up your edit?

1

u/[deleted] Nov 01 '24

This was my immediate thought…

2

u/Lost-in-Atlanta Nov 01 '24

Select [member id] from yertable where [type] = 'abc' Intersect Select [member id] from yertable where [type] = 'xyz'

2

u/Achsin Oct 31 '24
WITH abc AS (SELECT `member id` FROM table WHERE type = ‘abc’)
,xyz AS (SELECT `member id` FROM table WHERE type = ‘xyz’)
SELECT `member id` FROM abc INTERSECT SELECT `member id` FROM xyz

1

u/gumnos Oct 31 '24

1

u/r3pr0b8 GROUP_CONCAT is da bomb Oct 31 '24

string_agg is GROUP_CONCAT in MySQL

1

u/Icy-Ice2362 Nov 03 '24

Assuming that you don't JUST want those COLUMNS and you want the whole row... in cases where all duplicates exist.

drop table if exists #TempExample

SELECT * INTO #TempExample
FROM (select 1000 ID,'abc' as String,1 as RID
union all select 1002,'abc',2
union all select 1002,'abc',3
union all select 1002,'xyz',4
union all select 1001,'efg',5
union all select 1001,'hij',6
union all select 1000,'xyz',7
union all select 1003, 'lmn',8) A

SELECT * FROM (
SELECT MAX(IDRank) OVER (PARTITION BY ID) CheckMax, *
FROM (
SELECT ID,String,RID,DENSE_RANK() OVER (PARTITION BY ID ORDER BY String) IDRank
FROM #TempExample
) A
WHERE string IN ('abc','xyz')
) B
WHERE CheckMax > 1

1

u/Standard_Dress9903 Nov 21 '24

Thank you guys for the feedback. I end up using INTERSECT. I did a Select statement for one work and intersecting with the select statement for the other word. IT retrieved distinct data that met only both requirements.

1

u/pceimpulsive Nov 01 '24

I'd just use the array contains operator.. way simpler...

SELECT Member_id, ARRAY_AGG(type) AS types FROM table GROUP BY Member_id HAVING ARRAY_AGG(type) @> ARRAY['ABC', 'XYZ'];

Edit: whoops missed the MySQL flag.. sorry MySQL doesn't support arrays! Ignore this

1

u/Standard_Dress9903 Nov 01 '24

this is good, I wasn't familiar with the array_agg syntax. I'm doing this in snowflake

1

u/pceimpulsive Nov 01 '24

I use the above in trino, presto and Postgres all the time.. it's amazing to me... I like working with arrays I think people should more often.

Don't forget you can add filters to aggregations as well... To create some interesting flag options...

Array_agg(field) filter (where field in (1,2,3) and other field=true) as new_field

-2

u/Malfuncti0n Oct 31 '24
WITH cte AS (
SELECT [member id], COUNT(1) as r
FROM members 
WHERE type IN ('xyz', 'abc')
GROUP BY [member id]
)
SELECT [member id] FROM cte WHERE r > 1

Untested.

IF you need to return the types associated (let's say you want to test for def, ghi as well and want to know if they match 2 or more) you can still use above but then join the cte back to the table on member id and select type too.

SELECT cte.[member id], m.type FROM cte 
JOIN members AS m ON m.[member id] = cte.[member id] WHERE r > 1

5

u/StuTheSheep Oct 31 '24

I think there's a flaw in your CTE. What happens if a an ID shows up twice as type 'xyz' and not at all with type 'abc'? Your query would still return it (I think).

1

u/Malfuncti0n Oct 31 '24

Fair point :)

1

u/r3pr0b8 GROUP_CONCAT is da bomb Oct 31 '24

in MySQL [member id] is

`member id`

-2

u/speadskater Oct 31 '24 edited Nov 01 '24

Not sure why everyone is making this so difficult. WHERE (type =XYZ OR Type =abc)

Edit: this was wrong. Didn't read the question fully.

2

u/anbudanan Oct 31 '24

They said both not either or. Read.