using a preset array with IN () problems

[KarveR]

I know this shouldn't phaze me but it is. Big time.

I have a field `junk`, which contains an array I wish to use later e.g 2,3,4,5

I want to use this as such:

select * from mytable where id in (select junk from anothertable where somethingelse='stuff');

I only ever get 1 row returned which is the first number in `junk` (2 in this case). Why can't I get the rest, there are no group by's.

It works if I manually set the IN(), or is I use group_concat(blah).

I'd find it much easier to just preset the 10 entries with an array list in one field rather than creating 90 odd rows of 1 entry each.

chairs,
Kev


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[cLFlaVA]

this is not the proper way to do this. the proper way would be to have three tables:

[tt]table 1
-------
thing_id thing_descr
---------------------
1 blah
2 stuff
3 things

table 2
-------
junk_id junk_descr
--------------------
1 20
2 30
3 40

table 3
-------
map_id thing_id junk_id
-----------------------------
1 1 1
2 1 2
3 1 3
4 2 2
4 2 3[/tt]


you would then use joins to appropriately determine which "junk"s belong to which "thing"s.



other than this, you will need to concatenate strings together to form a SQL statement, then run the statement separately.



*cLFlaVA
----------------------------
[tt]mr. pibb + red vines = crazy delicious![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[KarveR]

exactly, I just dont really want to put all 90+ items in if I can get away with it.

It seemed worth a try, afterall, manually setting in to 2,3,4 works, group_concat gives the same effect, so why can't you select 2,3,4 from a field and have it work....

maybe I'll have to buglist it if no-one else can help <nudge TonyG>
...


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

it's not a bug

when you say

where id in (select junk from anothertable ...)

you are asking mysql to find out if the id is any one of the values in a column

your subquery probably returns only one row, with a value consisting of a string

and chances are, the id is not equal to that single string value, right?



you may wish to try

where find_in_set(id, (select junk from anothertable ...) )





r937.com | rudy.ca

 

[KarveR]

um, not entirely ...

select SERVER_SERVICES.service_ids from SERVER_SERVICES where SERVER_SERVICES.server='server2'
-> ;
+-------------+
| service_ids |
+-------------+
| 2,3,4,6,7,8 |
+-------------+
1 row in set (0.02 sec)

Thats fine, ok se we use that -

select * from SERVICES where id in (select SERVER_SERVICES.service_ids from SERVER_SERVICES where SERVER_SERVICES.server='server2');
+----+------+------+
| id | name | port |
+----+------+------+
| 2 | SSH | 22 |
+----+------+------+
1 row in set (0.00 sec)
I would have expected an error, but I get a row instead. just one.

It works for the first id, but not the rest.

It looks like it should work the same as
select * from SERVICES where id in (2,3,4,6,7,8); <--this works properly.


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[cLFlaVA]

you don't understand. your select statement (with subquery) is looking for rows in SERVICES that have an ID of '2,3,4,6,7,8'.

it may just be that, when the text converts to number to compare against the ID field, that everything following the 2 is lost.

this is not the way to go about what you're trying.



*cLFlaVA
----------------------------
[tt]mr. pibb + red vines = crazy delicious![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[KarveR]

No, my select with subquery is looking for rows in SERVICES that have an ID IN (...) not of.

You're also right, I don't understand.

If (a) select .. IN (2,3,4,6,7,8) works,
and
(b)select .. IN (select ...which returns "2,3,4,6,7,8")
which is effectively the same as (a)
only returns the first row why is it going pearshaped?

Surely if it were that badly formatted it would error.




______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

yes, IN ( list ) works

but the subquery doesn't return a list, it returns a single string

that's the difference


did you try FIND_IN_SET yet?

r937.com | rudy.ca

 

[KarveR]

if it looks like a list, smells like a list and walks like a list .....

why is it a llama?

FIND_IN_SET just makes everything hard(er).

I guess its the LongWay(tm).



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[cLFlaVA]

No, my select with subquery is looking for rows in SERVICES that have an ID IN (...) not of.

yes, IN (...).

but that ... is filled in with one value, "2,3,4,6,8". what it's NOT filled in with is 5 different values: 2 and 3 and 4 and 6 and 8.

as further proof i just ran this query on my database:

SELECT * FROM `news` WHERE news_id IN ('6cory')

and i got one record, the record with a news_id of 6.



*cLFlaVA
----------------------------
[tt]mr. pibb + red vines = crazy delicious![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[cLFlaVA]

when you do an IN statement with a subselect, it returns a recordset with one column of data that the parent query can use to determine if the records match the criteria.

what it does NOT return is a string of comma-delimited values.

that is the difference between your subselect and the string "2,3,4,6,8".



*cLFlaVA
----------------------------
[tt]mr. pibb + red vines = crazy delicious![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[r937]

if it looks like a list, smells like a list and walks like a list

but that's the whole point -- it does not walk like a list, it walks like a string

anyhow, i think you should go back to the 2nd post in this thread and seriously consider that approach

;-)

r937.com | rudy.ca

 

[KarveR]

Thanks guys, I'll suggest a LIST() function on the mysql bug/forums, it just seems such an oversight to miss out something which would be so useful - and much easier to handle than List partitioning.

In the meantime .. ho hum, <clickety><clickety>....



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

so, did you try FIND_IN_SET() yet?

r937.com | rudy.ca

 

[guelphdad]

FIND_IN_SET is like a LIST function no? So why not use it?

Best practice of course is to normalize the data.

 

[KarveR]

I'll have play with it later, but from the (brief) readof the manual it doesn't seem quite appropriate.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.