select from few tables mysql

Question

Having 3 tables:

1)name: companies, fields: company_id, company_name;

2)name: contacts, fields: contact_id, contact_name;

3)name: connections, fields: connection_id, contact_id, company_id;

I'm making multiple search. For example, I want to find companies called like "qwer" and contacts called like "abc". So my query will be:

$query="SELECT * FROM COMPANIES WHERE company_name RLIKE (qwer)"

Next step I'll make a massive with id of these companies. Next query will be like:

$query="SELECT * FROM connections WHERE company_id in (my massive)"

Making massive of contact_id from this selection and then making the last query:

$query="SELECT * FROM contacts WHERE contact_name RLIKE(abc) and id in(my massive)"

So, is it possible to make all this actions in one query or using less actions than I used?

As example, I need to find all companies called like "oogle" with contacts like "ith". As a result I need to get one company "Google" with two contacts: John Smith and Jenny Smith.

@Iqbal helps a lot, but I have one more table called addresses with two fields: id and street. And in table companies there is one more field called addresses_id. So I tried to get all info with such query:

select con.connection_id, com.company_name, ctx.contact_name 
from addresses as add, connections as con 
left join companies as com on con.company_id = com.company_id
left join contacts as ctx on con.contact_id = ctx.contact_id
where add.id=com.Legal_address

But it doesn't work, have a mistake: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add, Connections as con LEFT JOIN Companies as com on con.company_id = com.id LE' at line 1"

Please add some sample data and the expacted result – Jens Jun 19 '17 at 06:38 — Jens, Jun 19 '17 at 06:38
why don't use join – Nayas Subramanian Jun 19 '17 at 07:01 — Nayas Subramanian, Jun 19 '17 at 07:01
did it find useful to you? – Nayas Subramanian Jun 19 '17 at 07:09 — Nayas Subramanian, Jun 19 '17 at 07:09
@NayasSubramanian yep, thank you! – kliukovking Jun 19 '17 at 07:16 — kliukovking, Jun 19 '17 at 07:16

score 2 · Answer 1 · answered Jun 19 '17 at 07:06

Hi you can use something like this

select connections.connection_id, contacts.contact_id  companies.companyid
from connections 
left join companies on connections.company_id = companies.company_id
left join contacts on contacts.contact_id = connections.contact_id
where company_name LIKE ('%qwer%')
and contact_name LIKE('%abc%')

Sate Wedos · Accepted Answer · 2017-06-20T08:28:52.843

1

You can use left join..

If you want to open is connection_id, company_name and contact name

select con.connection_id, com.company_name, ctx.contact_name, add.street
from connections as con
left join companies as com on con.company_id = com.company_id
left join contacts as ctx on con.contact_id = ctx.contact_id
left join addresses as add on con.addresses_id = add.addresses_id

edited Jun 20 '17 at 08:28

answered Jun 19 '17 at 06:55

Sate Wedos

539
8
20

Of Course, I'm coding now using left join, will commend after complete!) – kliukovking Jun 19 '17 at 07:10
Can you help me? I don't understand exactly how works left join. I had one more table called addresses. And I need to select addresses.street where addresses.id=com.company_id. I tried many ways but it says that I "have an error in your SQL syntax" – kliukovking Jun 19 '17 at 09:29
I have changed my answer – Sate Wedos Jun 20 '17 at 06:23
TNX! In the last row, is it matter writing con.address_id=addr.id or addr.id=con.address_id? – kliukovking Jun 20 '17 at 07:31
con is connection where declarade in connections as con. so, con.address_id=addr.id or addr.id=con.address_id is condition where address field from connection table = field id from addres table. sorry if my English is bad – Sate Wedos Jun 20 '17 at 08:23
so it doesn't matter if I swap them? – kliukovking Jun 20 '17 at 08:27

select from few tables mysql

2 Answers2

Linked