4

I have three tables: TABLE 1 contracts

-------------------
id  |  contract_name
--------------------
1      test name
2      test name 2
2      test name 3
4      test name 4

TABLE 2 rooms

-------------------
id  |  room_name
--------------------
1      test name
2      test name 2
2      test name 3
4      test name 4

TABLE 3 promos

----------------------------------
 id  |  contracts_id  |   rooms_id
----------------------------------
 1          1,3            1,3,4
 1          2              1,2,3

No I am trying to do an inner join to get the names of the contract and the rooms according to the ids in the array saved in database. I know this is not ideal at all, but I can not change the database set up. So here is what I would like to do with my query, but obviously it is impossible. Does anyone have any idea on how I can accomplish this?

mysql_query("SELECT pb.*, c.contract_name, r.room_name FROM promo_blackouts AS pb
INNER JOIN contracts as c ON c.contract_id IS IN pb.contracts_id
INNER JOIN rooms as r ON r.room_id IS IN pb.rooms_id 
WHERE pb.promo_id = '$promo_id'") or die(mysql_error());
Jens
  • 67,715
  • 15
  • 98
  • 113
luv2code
  • 1,216
  • 6
  • 22
  • 42
  • What's the issue? Does mysql complain about duplicate column names or are you just unable to access the value by name (i.e. `$row['id']` because it's non-unique)? Please post the error message if possible. – mreithub Nov 08 '12 at 00:58
  • Ugh, that's messy. You have to parse the fields, splitting them by commas. There's no split function in mysql, but you could write your own using substr_index and a series of loops. Your example code looks like PHP - have you thought about parsing in PHP and doing a couple of queries? – sonofagun Nov 08 '12 at 01:09
  • See also http://stackoverflow.com/questions/3653462 – Bill Karwin Nov 08 '12 at 01:42
  • I'm suprised no one has mentioned this yet: this is not an optimal database design. You may already know this but in case you don't, you should do some research on normalisation. – Nick.Mc Nov 08 '12 at 01:50

1 Answers1

9

Are you looking for something like this?:

SELECT DISTINCT
    contract_name,
    room_name
FROM
    promos
INNER JOIN
    contracts ON FIND_IN_SET(contracts.id, contract_id) != 0
INNER JOIN
    rooms ON FIND_IN_SET(rooms.id, room_id) != 0
WHERE
    promos.id = 1
Michael
  • 11,912
  • 6
  • 49
  • 64