MySQL 2 来自同一个表的 LEFT JOIN

发布于 2024-12-11 07:51:23 字数 523 浏览 0 评论 0原文

我将客户电话和传真号码保存在同一个数据库表中。这并不反常。并非所有客户都有电话号码，而其他客户则没有传真号码。

我需要查看电话和传真，即使它们是空的。在 tbl_customers 表中，我有 2 个特定列专门用于记录的默认电话和默认传真。他们只是存储来自 tbl_phonenumbers 的记录 ID。

这是电话：

SELECT c.customername, p.phonenumber
FROM `tbl_customers` c
LEFT JOIN `tbl_phonenumbers` p ON c.customerid = p.customerid
WHERE c.defaultphone = 22

现在我需要添加默认的传真号码 ID，它遵循与默认电话相同的想法。

我不太确定该怎么做。当然，我可以创建一个名为“tbl_faxnumbers”的全新数据库表，并对其进行左连接。但我宁愿保留一张表来存放电话和传真号码。

LEFT JOIN 可以这样做吗？

原文

I keep my customer phone and fax numbers in the same database table. Which isn't abnormal.
Not all my customers have phone numbers, while others do not have fax numbers.

I need to view both phone and faxes, even if they're empty.
In the tbl_customers table I have 2 specific columns dedicated to the record's default phone and default fax. They simply store the record id from tbl_phonenumbers.

Here's for phone:

SELECT c.customername, p.phonenumber
FROM `tbl_customers` c
LEFT JOIN `tbl_phonenumbers` p ON c.customerid = p.customerid
WHERE c.defaultphone = 22

Now I need to add the default faxnumber id, which follows the same idea as the default phone.

I'm not quite sure how to go about this. Of course, I could create an entirely new database table called 'tbl_faxnumbers` and LEFT JOIN to that. But I'd rather keep one single table for phone and fax numbers.

Is this a possibility with the LEFT JOIN?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

好倦 2024-12-18 07:51:23

您可以多次联接同一个表，只要每个联接都有一个唯一的别名，以便数据库服务器可以找出您正在讨论的表的哪些额外版本：

SELECT ...
FROM maintable
JOIN othertable AS copy1 ON copy1.somefield = ...
JOIN othertable AS copy2 ON copy2.somefield = ...

对于您的情况，我会添加一个“类型”将“标识符”字段添加到电话号码表中，以便您可以指示哪个是电话号码，哪个是传真号码。然后你将进行连接：

SELECT ..., phones.phonenumber, faxes.phonenumber
...
JOIN tbl_phonenumbers AS phones ON (... = phones.id) AND (phones.type = 'phone')
JOIN tbl_phonenumbers AS faxes ON (... = faxes.id) AND (faxes.type = 'fax');

You can join the same table multiple times, as long as each join has a unique alias so the DB server can figure out WHICH of those extra versions of the table you're talking about:

SELECT ...
FROM maintable
JOIN othertable AS copy1 ON copy1.somefield = ...
JOIN othertable AS copy2 ON copy2.somefield = ...

For your case, I'd add a "type" identifier field to the phone numbers table, so you can indicate which is a phone number and which is a fax number. Then you'd do your joins as:

SELECT ..., phones.phonenumber, faxes.phonenumber
...
JOIN tbl_phonenumbers AS phones ON (... = phones.id) AND (phones.type = 'phone')
JOIN tbl_phonenumbers AS faxes ON (... = faxes.id) AND (faxes.type = 'fax');

回复收藏 0 原文