how to get an index and use it in query mysql

Question

I have a table similar to this:

   memberID  |    clubIDs     |       clubRegistrationDates     ...
--------------------------------------------------------------------
   2             3,4,10,2             2010,2011,2015,2014       ...
   3             2,1,5,6              2015,2000,2005,2010       ...
   4             3,2                  2014,2014                 ...

Meaning of a row is, for example: member 2 was registered to club 3 in 2010, was registered to club 4 in 2011 and so on...
So the query I want to get is to get any member who is a member of club 2 since 2014, so this query should get me members 2 and 4. I already have an sql but it is not connected to year parameter which is:

SELECT clubID, clubName,
(SELECT count(*) FROM members WHERE FIND_IN_SET('2',clubIDs) AND status='1' AND memberTypeID='2') AS activeNumber,
(SELECT count(*) FROM members WHERE FIND_IN_SET('2',clubIDs) AND status='0' AND memberTypeID='2') AS inactiveNumber
FROM clubs;

So I need to get club 2's index from clubIDs column and use it for clubRegistrationDates column. Any help is appreciated, thanks in advance.

I think you should pass just 2 (as integer) not "2" (as string) and may be put > 0 after FIND_IN_SET — maksbd19
– maksbd19, Commented Apr 5, 2015 at 10:33
Actually you should really, really change your DB design. Never store multipe values in a single column. — juergen d
– juergen d, Commented Apr 5, 2015 at 10:36
I agree with @juergend, but sometimes its impossible to change if the system is already built and there is not enough time available to undertake this kind of major change. Once I have experienced such one :( — maksbd19
– maksbd19, Commented Apr 5, 2015 at 10:39
juergen d, a person can be a member of 20 clubs, or 1 club and there is no way of knowing that, therefore, putting 20 columns is not good either and this is a lot more dynamic. For 1 club members, other 19 columns would be null... maksbd19, thanks for the comment. — Ömer Öksüzler
– Ömer Öksüzler, Commented Apr 5, 2015 at 10:40

juergen d · Accepted Answer · 2015-04-05 10:52:56Z

2

Never store multiple values in a single column. This will only get you problems like yours now. A better table design would be

members table
-------------
id
name
...


clubs table
-----------
id
name
...


club_members table
------------------
club_id
member_id
registration_year

It is called a m to n relation. Then to get all members of club with id=2 since 2014 you can do

select m.id, m.name
from members m
join club_members cm on cm.member_id = m.id
where cm.club_id = 2
and cm.registration_year = 2014

And if you only have the club name and not the id then use

select m.id, m.name
from members m
join club_members cm on cm.member_id = m.id
join clubs c on cm.club_id = c.id
where c.name = 'Cool Club'
and cm.registration_year = 2014

edited Apr 5, 2015 at 10:52

answered Apr 5, 2015 at 10:42

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

Ömer Öksüzler Over a year ago

Yeah, that design is better. The query I wanted would be easier too. Thanks.

Collectives™ on Stack Overflow

how to get an index and use it in query mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related