从几张表中选择mysql [英] select from few tables mysql

问题描述

有3张桌子:

1)名称:公司，字段:company_id，company_name；

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

2)name:联系人，字段:contact_id，contact_name；

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

3)名称:连接，字段:connection_id，contact_id，company_id；

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

我正在进行多次搜索.例如，我想找到名为"qwer"的公司和名为"abc"的联系人.所以我的查询将是:

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)"

下一步，我将大量介绍这些公司的ID.下一个查询将类似于:

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)"

从此选择中获取大量的contact_id，然后进行最后一个查询:

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?

例如，我需要找到所有带有"ith"之类的名称的公司，例如"oogle".结果，我需要让一家拥有两个联系人的"Google"公司:约翰·史密斯和詹妮·史密斯.

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很有帮助，但是我还有一个表，称为地址，具有两个字段:id和street.在表格公司中，还有一个名为addresss_id的字段.因此，我尝试通过此类查询获取所有信息:

@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

但是它不起作用，有一个错误:您的SQL语法有错误；请查看与您的MySQL服务器版本相对应的手册，以获取正确的语法以在'add，Connections as con上使用正确的语法LEFT JOIN公司作为com在con.company_id = com.id LE'的第1行"

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"

推荐答案

您可以使用左联接.

如果要打开的是connection_id，company_name和联系人姓名

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

这篇关于从几张表中选择mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！