How to query with where and like between two tables?

Question

How to query with where and like between two tables?

Navigation

#1 by (3 votes)
#2 by (0 votes)

0

I have the following problem:

I have two tables, the following tables:

tabela mensagens
+----+-------------------+-------+---------------------+
| id | mensagem          | _de   | hora                |
+----+-------------------+-------+---------------------+
| 1  | teste de mensagem | tiago | 2015-12-04 14:56:41 |
+----+-------------------+-------+---------------------+
tabela amigos
+----+--------+--------------+
| id | user   | amigos       |
+----+--------+--------------+
| 1  | italos | igors;tiago; |
+----+--------+--------------+

I would like to make the following query: that I would receive the result of all messages only if there is in the friends table the _de value of the messages table. Thank you all.

php mysql sql codeigniter

asked by anonymous 05.12.2015 / 00:50

2 answers

Get values from only one row of a table How to feed a tableView of a screen with information from another screen?

score 3 · Answer 1

You need a table to bind, do more varchar search with multiple values, you can make your query heavy and slow idle.

tabela mensagens
+----+-------------------+---------------------+
| id | mensagem          | hora                |
+----+-------------------+---------------------+
| 9  | teste de mensagem | 2015-12-04 14:56:41 |
+----+-------------------+---------------------+

tabela amigos
+----+--------+
| id | user   |
+----+--------+
| 1  | italos | 
+----+--------+

tabela amigosItem
+----+--------+--------+--------------+
| id | user   | msg    |  amigos      |
+----+--------+--------+--------------+
| 5  | 1      | 9      | igors        |
+----+--------+-----------------------+
| 6  | 1      | 9      | tiago        |
+----+--------+-----------------------+

Select A.mensagem 
from amigos A
 Inner join amigosItem AI On A.id = AI.user
 Inner join mensagens M On M.id = AI.msg

It would look like this.

score 0 · Answer 2

Let's go Wesley, first the friends field of the friends table should be a new table in the database ( friend_item ), it is not advisable to have a multivalued attribute in a table. One solution for making the select the way you want it is to use INNER JOIN . However, it will only work if your bank has all the relationships correctly. Is it possible to generate a DB structure script and send it here?