Select from two tables where left column IN content right column

Question

I have two tables:

users:

+-------+-------------+------+
| id    | name        | type |
+-------+-------------+------+
|   1   | Name 1      |  1   |
|   2   | Name 2      |  2   |
|   3   | Name 3      |  3   |
+-------+-------------+------+

messages:

+-------+-------------+-----------+----------+
| id    |  poster_id  |  message  | for_type |
+-------+-------------+-----------+----------+
|   1   |      3      |   mess1   |     1    |
|   2   |      2      |   mess2   |    2,3   |
|   3   |      1      |   mess3   |    3,1   |
+-------+-------------+-----------+----------+

What query for get messages on $user_type in for_type ?

SELECT messages.*, users.name FROM messages LEFT JOIN users ON 
messages.poster_id = users.id WHERE messages.id > ' . $last_message_id . ' AND 
' . $user_type . ' IN (messages.for_type) 
ORDER BY news.id ASC

See normalization , its bad design. Reference https://stackoverflow.com/questions/25644986/mysql-select-from-comma-separated-string/25645475#25645475 — M Khalid Junaid, Sep 15 '17 at 20:39

score 1 · Answer 1 · answered Sep 15 '17 at 20:55

1

If I have understood right, you want to select a particular for_type and have the problem that the column is not correctly normalised, having more than one value. If you cannot normalise the structure correctly a LIKE command in the where clause would work, for example:

for_type LIKE "%1%"

Will get record 1 and 3 from messages.

answered Sep 15 '17 at 20:55

Lew Perren

1,209
1
10
14

It's works only on number less 10 :) – Serg Sep 15 '17 at 21:08
1

I'm think of changing type_num to type_name and using LIKE – Serg Sep 15 '17 at 21:25
Yep, using LIKE gives loads of potential solutions on that column. Give us an upvote if I have helped. – Lew Perren Sep 15 '17 at 21:28

Select from two tables where left column IN content right column

1 Answers1