Use List from TEXT Field in IN () condition

[henryrhenryr]

Hi

I have the following query:

[code=sql]
SELECT user_id_pk, first_name, last_name, email
FROM users
LEFT JOIN status ON user_id_pk=user _id_fk
WHERE live='Y'
AND (status=1 OR status IS NULL)
AND user_id_pk IN (SELECT uid_csv FROM groups WHERE group_id_pk=1);
[/code]

The groups table contains a TEXT field with a comma seperated list such as:

1,10,14,25

When I write the query longhand (ie without the sub-select), it returns the correct rows. Otherwise it doesn't seem to recognise the list properly. I suppose this is because the uid_csv field is TEXT format. How can I use fix the query? Can I convert the format of the TEXT field so that it is recognised properly?

Thanks!

Henry

[chaarmann]

Hi

I have the following query:

[code=sql]
SELECT user_id_pk, first_name, last_name, email
FROM users
LEFT JOIN status ON user_id_pk=user _id_fk
WHERE live='Y'
AND (status=1 OR status IS NULL)
AND user_id_pk IN (SELECT uid_csv FROM groups WHERE group_id_pk=1);
[/code]

The groups table contains a TEXT field with a comma seperated list such as:

1,10,14,25

When I write the query longhand (ie without the sub-select), it returns the correct rows. Otherwise it doesn't seem to recognise the list properly. I suppose this is because the uid_csv field is TEXT format. How can I use fix the query? Can I convert the format of the TEXT field so that it is recognised properly?

Thanks!

Henry

The problem is that your SQL returns only one instead of many strings back inside the IN-statement. It is the same as if you type
... in ('1,10,14,25')
instead of
... in (1,10,14,25) which is the same as
... in ('1','10','14', '25') .

So you have to split up the string somehow. But this is very difficult. Even if this command exists in the new MySQL version then I would not use it: it is non-standard SQL and would not run on other DBs. You can solve the problem by writing a procedure, but again it would be very tricky. Better is to use a complete other way.

We don't split up the string, but instead are searching inside the string for the number with regular expressions!
To be able to also find the first and last element, we must frame the String with commas.

Then following code should do: (not tested, but I hope you get the idea)

[code=sql]
SELECT user_id_pk, first_name, last_name, email
FROM users
LEFT JOIN status ON user_id_pk=user _id_fk
WHERE live='Y'
AND (status=1 OR status IS NULL)
AND concat(',',(SEL ECT uid_csv FROM groups WHERE group_id_pk=1), ',')like concat('%,',use r_id_pk, ',%') ;
[/code]

Also you could try to do it with instr() command instead of like, whatever is faster.

By the way, if you don't frame with commas, then searching for '%34%' would aslo match on ',12345,', which needs to be avoided.

[henryrhenryr]

Hi Chaarman

Thanks for getting back to me... I see what you mean, I was hoping that someone would know a way of type-setting.

I have gone for two queries in the end - the tables are not currently very full up so it doesn't cause too many problems just a bit clunky.

Thanks!

Henry