Hello to Everyone.
I have two table, for example;
TABLE Name X
________________
| id | A |
|____|_________|
| 1 | 1,2,4,8 |
|____|_________|
This Query is working in another TABLE Y,
mysql_query(" Select * from Y where id IN(1,2,4,8) ")
But this in not working,
mysql_query(" Select * from Y where id IN(Select A from X where id=1) ")
What can I do? Thank You.
A better table design would be:
table X:
--------
id int
someid int
Values in the table would be:
id someid
-- --------
1 1
2 1
4 1
8 1
Your query could then be mysql_query(" Select * from Y where id IN(Select id from X where someid=1").
To answer your question -- You appear to be querying from PHP or something similar. Using the table structure you have, you could retrieve the value from table X using mysql_query("Select A from X where id=1") and store the results in a variable. You could then execute a second query, mysql_query(" Select * from Y where id IN(" + yourVar + ") ").
This is a "Has Many" relationship, so you should have an intermediate join table. The benefit to this structure is on insertions and deletes. In your structure to append or delete from A you'd have you pull the record, change the value and write it back (or write a procedure maybe).
X
_________
| id |
|_______|
| 1 |
|_______|
Y
_________
| id |
|_______|
| 1 |
|_______|
| 2 |
|_______|
| 3 |
|_______|
X_Y
____________________________
| id | x_id | y_id |
|_______|________|_________|
| 1 | 1 | 1 |
|_______|________|_________|
| 2 | 1 | 2 |
|_______|________|_________|
| 3 | 1 | 3 |
|_______|________|_________|
SELECT * FROM Y INNER JOIN X_Y ON Y.id=X_Y.y_id WHERE X_Y.x_id=1
or
SELECT * FROM Y WHERE id IN (SELECT y_id FROM X_Y WHERE x_id=1)
