如何对另一个表链接的两个表执行MySQL select查询

Suppose I have a table whose function is specifically to link two other tables in terms of OOP.

假设我有一个表,其功能是专门根据OOP链接其他两个表。

Suppose that I have two tables: one for person's name and another one for phone numbers:

假设我有两个表:一个用于人名,另一个用于电话号码:

Table 1:
id   person's name
1    John
2    Smith

Table 2:
id   Phone number
5     23424224
6      23424242

And then I have a third table that links the person and their respective phone numbers:

然后我有第三个表连接此人及其各自的电话号码:

Table 3:
id    person-id    phone-number-id
1         1           5
2         2           6

Hence John has phone number 23424224 and Smith has phone number 23424242.

因此约翰的电话号码是23424224,史密斯的电话号码是23424242。

And I want to run an SQL query to fetch all persons from Table 1 whose phone number start with, let's say, (234).

我想运行一个SQL查询来从表1中获取电话号码开头的所有人,比方说,(234)。

How would I go about linking the select queries within this table structure...what query would I run?

我将如何在此表结构中链接选择查询...我将运行什么查询?

6 个解决方案

#1

First, the only reason to do that table is if you have a many-to-many relation. While a person can have many phone numbers, can really one phone number have many persons? If that is true, then your schema implements that requirement, but that seems a little over-engineered to me :-)

首先,做这个表的唯一原因是你有多对多的关系。虽然一个人可以拥有多个电话号码,但真的一个电话号码有多少人?如果这是真的,那么你的架构实现了这个要求,但这似乎对我来说有点过度设计:-)

Second, this is a fairly simple join. What you want to do is first select out the phone numbers in question, then given that, select out the person IDs from the third table, then given that, select the names from the first table. Something like:

其次,这是一个相当简单的连接。您要做的是首先选择有问题的电话号码,然后给出,从第三个表中选择人员ID,然后给出,从第一个表中选择名称。就像是:

SELECT t1.name as name, t2.number from table1 t1, table2 t2, table3 t3 where t2.number like '234%' and t3.personid = t1.id and t3.phoneid = t2.id;

SELECT t1.name作为名称,t2.number来自table1 t1,table2 t2,table3 t3其中t2.number喜欢'234%'和t3.personid = t1.id和t3.phoneid = t2.id;

You can also rewrite the "blah.id = blah.id" as a join if you need outer join semantics (include certain fields with NULLs).

如果需要外连接语义(包括带有NULL的某些字段),您还可以将“blah.id = blah.id”重写为连接。

#2

I've assumed column names for person's name and phone number, and assumed you want to return a list of people who have any phone numbers beginning 234 (rather than that all of their phone numbers must begin 234).

我已经假设了人名和电话号码的列名,并假设您想要返回任何电话号码开头234的人的列表(而不是他们所有的电话号码必须开始234)。

SELECT 
    persons-name
FROM
    Table1
WHERE
    id IN
        (SELECT
             Table3.person-id
         FROM
             Table3
             INNER JOIN Table2 ON Table2.id = Table3.phone-number-id
         WHERE
             phone-number like '234%')

I have to admit to only knowing MS-SQL so potentially I'll have added some platform-specific features, let me know if you have any trouble.

我不得不承认只知道MS-SQL,所以我可能会添加一些特定于平台的功能,如果您有任何问题,请告诉我。

Alternatively you could use an inner join rather than an "in", but this way feels like it more closely describes the desired effect. Also it avoids having to do any "distincts" to avoid having someone with 2 phone numbers beginning 234 appearing twice.

或者你可以使用内连接而不是“in”,但这种方式感觉它更接近地描述了所需的效果。此外,它避免了必须做任何“区分”,以避免让有2个电话号码开始234的人出现两次。

#3

For numbers to a person:

对于一个人的数字:

SELECT persons.name, numbers.phone_number FROM persons
  LEFT JOIN person_number ON person_number.person-id = persons.id
  LEFT JOIN numbers ON person_number.phone-number-id = numbers.id

For persons to a number

对于一些人来说

SELECT persons.name, numbers.phone_number FROM numbers
  LEFT JOIN person_number ON person_number.phone-number-id = numbers.id
  LEFT JOIN persons ON person_number.person-id = persons.id

#4

select person.id, person.name from
table1 as person,
table2 as numbers,
table3 as link
where person.id=link.person-id
and numbers.id=link.phone-number-id
and numbers.phonenumber like '234%'

选择person.id,person.name从table1作为person,table2作为数字,table3作为链接,其中person.id = link.person-id和numbers.id = link.phone-number-id和numbers.phonenumber like'234% “

#5

First... you don't need column id in Table 3.

首先......表3中不需要列ID。

Just make personId and phoneId your primary key.

只需将personId和phoneId作为主键。

That's the purpose of your many-to-many table (associate multiple phones with the same person). You can have the same person Id for different phone Ids and vice-versa, that is, you can have the same phone Id for different person Ids. You'll never have two rows with the same person Id and phone Id. That makes no sense at all.

这就是你的多对多表格的目的(将多个手机与同一个人联系起来)。对于不同的手机ID,您可以拥有相同的人ID,反之亦然,也就是说,您可以为不同的人ID拥有相同的手机ID。您永远不会有两行具有相同的人ID和电话ID。这毫无意义。

With this query you'll get the expected result:

使用此查询,您将获得预期结果:

SELECT p.*
FROM Person as p, Phone as ph, PersonPhone as pf
WHERE pf.PersonId = p.Id AND pf.PhoneId = ph.Id and ph.Number like '234%'

#6

It would be something like the following. Using standard SQL, here we are using an inner join to the persons table to get the name, then another join to the resulting subquery (filtering by your phone pattern) to get the persons matching numbers.

它将类似于以下内容。使用标准SQL,这里我们使用person表的内连接来获取名称,然后使用另一个连接到结果子查询(通过电话模式过滤)以获得匹配数字的人。

SELECT T1.fullname, T.phone_number
FROM TABLE3 AS T3
INNER JOIN TABLE1 AS T1
      ON T3.person_id = T1.id
INNER JOIN (
      SELECT phone_id, phone_number
      FROM TABLE2
      WHERE phone_number LIKE '%PATTERN%'
) AS T
     ON T3.phone_id = T.id

#1