I have a table like this (mytable):
+------------+-----------+----------------+
| id_mytable | id_artist | foreing_column |
+------------+-----------+----------------+
| 1 | 2 | 5 |
| 2 | 1 | 5 |
| 3 | 1 | 2 |
| 4 | 3 | 6 |
+------------+-----------+----------------+
but I only know the number 5 of the column foreign_column, knowing this 5, I can get all the id_artist(in this case 2 and 1)
SELECT id_artist FROM `artist_band` WHERE id_band= 5
so the problem now is that I want to get the foreign_column of in this case the two id_artist (1 ,2), so I will end up with this table:
+------------+-----------+----------------+
| id_mytable | id_artist | foreing_column |
+------------+-----------+----------------+
| 1 | 2 | 5 |
| 2 | 1 | 5 |
| 3 | 1 | 2 |
+------------+-----------+----------------+
(you know all the 2 and all the 1 being in foreign_column)
I've tried something like this:
(SELECT id_artist FROM `artist_band` as one WHERE id_band= 5)
inner join
(SELECT * FROM `artist_band` as two )
on one.id_artist = two.id_artist
or:
SELECT * FROM `artist_band` where id_artist =
(SELECT id_artist FROM `artist_band` WHERE id_band= 5)
thanks.
SELECT *
FROM mytable
WHERE id_artist
IN
(
SELECT id_artist
FROM mytable
WHERE foreign_column = 5 -- (or whatever)
)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments