How to cast MySQL result to a string?

Question

There is a query:

SELECT blalist FROM blatable WHERE blafield=714

which returns a string that looks like: "2,12,29,714,543,1719". And there is another query:

SELECT userid, name, surname, creditcardnum, items
    FROM stolencards WHERE userid IN
    (SELECT blalist FROM blatable WHERE blafield=714)

Now that's not working.
I only managed to get it working by executing these queries separately. What should I do to keep it in a single query?

Answer 1

check the data type of blalist and userid are same, or blalist really returns userid?

Answer 2

If you have specified correct data types and pk and fk relationships then try this:

SELECT s.userid, s.name, s.surname, s.creditcardnum, s.items
FROM stolencards s inner join blatable b on s.userid = b.blafield
where b.blafield = 714

Answer 3

You should never store more than one value in one cell. Each value in blatable should be in its own row, then your IN clause would work like a charm. Take a look at database normalization and especially at First normal form on how your tables should be designed.

As you have all the values in one cell, doing an IN comparison results in all userids being compared to the string "2,12,29,714,543,1719", which obviously will not match. Your query effectively looks like this:

...FROM stolencards WHERE userid IN ("2,12,29,714,543,1719")