CODEWITHSUNDEEP
phpmysql
Acque1951
Acque1951

Reputation: 11

Select Query not work with where in

I have two table

one table is alldata ( here info_id is a text field data inserted using php )

=================
id  |   info_id
=================
1   |    2, 3, 5, 9
2   |
=================


second table is info
=================
id  |   name
=================
1   |    one
2   |    two
3   |    three
4   |    four
5   |    five
6   |    six
7   |    seven
9   |    eight
9   |    nine

=================

now I want to select list of data from table two where data id will be matched with table one first item info_id data

my query is

SELECT i.* FROM `info` as i,`alldata` as a where  i.id IN(a.info_id) and a.id=1

my query works but select only one item from table two.But there are multiple matched.

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You have a very poor database design. First, storing numeric ids as strings is a bad idea -- numbers should be stored as numbers. Second, SQL offers this great data structure for storing lists. It is called a table, not a string.

You should really have a junction table, one one row per id and info_id.

That said, sometimes we a struck with substandard data structure. MySQL offers support for this. You can use:

SELECT i.*
FROM `info` i JOIN
     `alldata` a 
     ON FIND_IN_SET(i.id, REPLACE(a.info_id, ', ', ',') ) > 0
WHERE a.id = 1;

You should also learn to use proper, explicit join syntax. If you use this method, instead of fixing the database design, you are not allowed to complain about performance. MySQL cannot take advantage of things like indexes to improve the performance of this type of query.

Upvotes: 1

Related Questions