自连接，只在一个表/面上忽略null。

I have 2 tables.

我有两个表。

One table defines customer connections:

一个表定义了客户连接:

    CREATE TABLE IF NOT EXISTS `cust_connections` (
    `id` int(11) NOT NULL,
      `short_name` char(15) COLLATE utf8_unicode_ci NOT NULL,
      `source_fnn` char(10) COLLATE utf8_unicode_ci NOT NULL,
      `dest_fnn` char(10) COLLATE utf8_unicode_ci NOT NULL,
      `service_type` char(32) COLLATE utf8_unicode_ci NOT NULL,
      `ladder_side` char(10) COLLATE utf8_unicode_ci NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    INSERT INTO `cust_connections` (`id`, `short_name`, `source_fnn`, `dest_fnn`, `service_type`, `ladder_side`) VALUES
    (1, 'cust1', 'N2843453A', '', 'HD_300_Connect', 'src only'),
    (2, 'cust2', '', 'N2843600A', 'HD_300_Connect', 'dest only'),
    (3, 'cust3', 'N2720257O', 'N2731164O', 'DVB25_188byte', 'both'),
    (4, 'cust4', 'N27xxx7O', 'N2731164O', 'DVB25_188byte', 'src ukn'),
    (5, 'cust4', 'N27xxx7O', '', 'DVB25_188byte', 'ukn +blk');

ALTER TABLE `cust_connections`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `cust_connections`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=18;

The other table defines the equipment:

另一个表定义了设备:

     CREATE TABLE IF NOT EXISTS `cust_port` (
    `id` smallint(11) NOT NULL,
      `system_name` char(32) COLLATE utf8_unicode_ci NOT NULL,
      `slot_no` char(2) COLLATE utf8_unicode_ci NOT NULL,
      `port_no` char(2) COLLATE utf8_unicode_ci NOT NULL,
      `port_fnn` char(9) COLLATE utf8_unicode_ci NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    INSERT INTO `cust_port` (`id`, `system_name`, `slot_no`, `port_no`, `port_fnn`) VALUES
    (1, '01-06C2:source', '7', '1', 'N2843453A'),
    (2, '01-27B4:dest', '1', '2', 'N2843600A'),
    (3, '01-27B6:source+dst', '17', '3', 'N2720257O'),
    (4, '01-27B6:dst+src', '17', '3', 'N2731164O'),
    (5, '01-32C6:dup_fnn1', '1', '2', 'N2845070O'),
    (26, '01-32C6:dup_fnn2', '1', '3', 'N2845070O'),
    (27, '01-32D6:no_fnn', '1', '4', ''),
    (28, '01-32D6:diff_fnn', '1', '4', 'x123456');

ALTER TABLE `cust_port`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `cust_port`
MODIFY `id` smallint(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=29;

SQl Result is:

SQl结果是:

 cc_id  short_name  source_fnn  dest_fnn    service_type    ladder_side     src_system_name     src_slot_no     src_port_no     src_port_fnn    dst_system_name     dst_slot_no     dst_port_no     dst_port_fnn    
1       cust1       N2843453A               HD_300_Connect  src only       01-06C2:source         7                  1          N2843453A       01-32D6:no_fnn       1              4   
2       cust2       N2843600A               HD_300_Connect  dest only      01-32D6:no_fnn         1                  4                          01-27B4:dest         1              2               N2843600A
3       cust3       N2720257O   N2731164O   DVB25_188byte   both           01-27B6:source+dst    17                  3          N2720257O       01-27B6:dst+src      17             3               N2731164O
4       cust4       N27xxx7O    N2731164O   DVB25_188byte   src ukn        NULL                   NULL               NULL       NULL            01-27B6:dst+src      17             3               N2731164O
5       cust4       N27xxx7O                DVB25_188byte   ukn +blk       NULL                   NULL               NULL       NULL            01-32D6:no_fnn       1              4

I am doing a join on the two tables.

我在这两个表上做一个连接。

The problem is, I want to exclude the row if the port_fnn is null, but display the row if any of the customer source or destination fnns are null.

问题是，如果port_fnn为null，我想排除行，但是如果有任何客户源或目标fnn为空，则显示行。

I am doing a left (self) join to match on source and destination fnns against equipment fnn. Unfortunately my customer fnns must be able to have null values.

我正在做一个左(自)连接，以匹配源和目标fnn的设备fnn。不幸的是，我的客户fnns必须能够有null值。

My query works great if there are no null values in the equipment table. My query is:

如果设备表中没有空值，我的查询就会非常有效。我查询的方法是:

SELECT 
cc.id AS cc_id, short_name,source_fnn, dest_fnn, service_type,ladder_side,
src.system_name AS src_system_name,
src.slot_no AS src_slot_no,
src.port_no AS src_port_no,
src.port_fnn AS src_port_fnn,
dst.system_name AS dst_system_name,
dst.slot_no AS dst_slot_no, 
dst.port_no AS dst_port_no,
dst.port_fnn AS dst_port_fnn
FROM cust_connections cc 
 LEFT JOIN cust_port src on cc.source_fnn=src.port_fnn 
 LEFT JOIN cust_port dst on cc.dest_fnn=dst.port_fnn

In my result set: Row 1 - Has a source fnn only. I want the result to be empty for destination fields ie:

在我的结果集中:第一行只有一个源fnn。我希望目标字段为空:

cc_id   short_name  source_fnn  dest_fnn    service_type    ladder_side     src_system_name     src_slot_no     src_port_no     src_port_fnn    dst_system_name     dst_slot_no     dst_port_no     dst_port_fnn    
1       cust1       N2843453A               HD_300_Connect  src only       01-06C2:source         7                  1          N2843453A       NULL                NULL             NULL            NULL

The query is detecting a null fnn and populating with the equipment that does not have an fnn associated. I.e.: 01-32D6:no_fnn.

查询是检测一个空的fnn，并填充没有fnn关联的设备。即。:01-32D6:no_fnn。

The same issue occurs for source_system_name on row 2 and dst_system_name on row 5.

在第5行中，第2行和dst_system_name上的source_system_name发生了相同的问题。

4 个解决方案

#1

'' (the empty string) is not NULL. (Which sqlfiddle outputs as "(null)".)

(空字符串)不是NULL。(sqlfiddle输出为“(null)”)

In text, do not write "NULL" or "<NULL>" or "null" or "(null)" to refer to the empty string as you did originally in your question. Be clear what is '' and what is NULL.

在文本中，不要写“NULL”或“ ”或“NULL”或“(NULL)”来引用空字符串，就像您最初在问题中所做的那样。清楚什么是“什么是空”。

'' = '' but NULL <> NULL. So your LEFT JOIN finds a match between columns cc.source_fnn and src.port_fnn and between columns cc.dest_fnn and cust_port port_fnn when the equality tests involve ''. But you don't want the LEFT JOIN to match those rows.

" = "但是NULL <> NULL。因此，您的左连接在列cc.source_fnn和src之间找到了一个匹配项。当等式测试涉及到的时候，port_fnn和列cc.dest_fnn和cust_port port_fnn。但是你不希望左边的连接匹配这些行。

You can say that by:

你可以这样说:

Declaring all _fnn columns nullable, ie as NULL (the default) instead of NOT NULL and using NULL in your tables where you now use '' (the empty string). Then your query will give the correct answer!

声明所有_fnn列为nullable(默认值为NULL)，而不是在您现在使用的表中使用NULL(空字符串)。然后您的查询将给出正确的答案!

Requiring port_fnn <> '':

要求port_fnn < >”:

FROM cust_connections cc
LEFT JOIN cust_port src
ON cc.source_fnn=src.port_fnn AND cc.source_fnn <> ''
LEFT JOIN cust_port  dst
ON cc.dest_fnn=dst.port_fnn AND cc.dest_fnn <> '';

Removing those rows from cust_port before you LEFT JOIN with it:

将这些行从cust_port中删除之前，将其与它连接:

FROM cust_connections cc
LEFT JOIN
    (SELECT * FROM cust_port WHERE port_fnn <> ''
    ) src
ON cc.source_fnn=src.port_fnn
LEFT JOIN
    (SELECT * FROM cust_port WHERE port_fnn <> ''
    ) dst
ON cc.dest_fnn=dst.port_fnn;

Sqlfiddle for 1 using NULL and for 2 & 3 using ''. These have an added second cust_port row with absent port_fnn to show that the above give the correct result. Your query used with '' incorrectly generates other spurious rows from that.

使用NULL和2 & 3使用的Sqlfiddle。这些都添加了第二个cust_port行，其中没有port_fnn，以显示上面给出了正确的结果。您的查询使用“错误地生成其他虚假的行”。

If you want NULL from the LEFT JOIN shown as the empty string on output then you can use IFNULL for those columns:

如果你想从左边的连接中显示空字符串，那么你可以用IFNULL来表示这些列:

IFNULL(dst.port_fnn,'') AS dst_port_fnn

IFNULL dst_port_fnn(dst.port_fnn”)

#2

You could add a where clause like:

你可以添加一个where子句:

where  (cc.source_fnn is null or cc.dest_fnn is null)
       or (src.port_fnn is not null or dst.port_fnn is not null)

Now it will always display rows with an empty source_fnn or dest_fnn. When both are filled, it will filter out rows where a matching port_fnn column is empty.

现在，它将总是显示带有空source_fnn或dest_fnn的行。当两者都被填充时，它将过滤出匹配的port_fnn列为空的行。

So you'll get rows with missing foreign keys, but suppress rows where the foreign key references a row with an empty column. At least that's what I think you're looking for. Please clarify your question if it's not.

因此，您将会得到缺少外键的行，但是会抑制外键引用一行空列的行。至少这是我认为你在寻找的。请澄清你的问题，如果不是。

#3

I think what you are trying to do can be done with an IF() for each respective source/destination column to MASK the name and ports. Most of the time people will try to do something to PREVENT nulls and show something like an empty string... Instead, you want the reverse... if the "port_fnn" is null, you want to HIDE those elements.

我认为，您正在尝试做的事情可以用IF()来完成，以便为每个源/目标列屏蔽名称和端口。大多数情况下，人们会尝试做一些事情来防止nulls，并显示一些类似于空字符串的东西…相反，你想要的是相反的…如果“port_fnn”是空的，则希望隐藏这些元素。

So I have done an IF( expression, result if true, result if false ) for each column. So, if the port_fnn IS NULL, then show null as the result, otherwise return whatever the column was (system name, slot, port, etc)

因此，我对每一列都做了IF(表达式、结果IF true、result IF false)。因此，如果port_fnn为NULL，则显示为NULL，否则返回任何列(系统名称、槽、端口等)

SELECT 
      cc.id AS cc_id, 
      short_name,
      source_fnn, 
      dest_fnn, 
      service_type,ladder_side,
      if( src.port_fnn = '', NULL, src.system_name ) AS src_system_name,
      if( src.port_fnn = '', NULL, src.slot_no ) AS src_slot_no,
      if( src.port_fnn = '', NULL, src.port_no ) AS src_port_no,
      if( src.port_fnn = '', NULL, src.port_fnn ) AS src_port_fnn,
      if( dst.port_fnn = '', NULL, dst.system_name ) AS dst_system_name,
      if( dst.port_fnn = '', NULL, dst.slot_no ) AS dst_slot_no, 
      if( dst.port_fnn = '', NULL, dst.port_no ) AS dst_port_no,
      if( dst.port_fnn = '', NULL, dst.port_fnn ) AS dst_port_fnn
   FROM 
      cust_connections cc 
          LEFT JOIN cust_port src 
             on cc.source_fnn = src.port_fnn
          LEFT JOIN cust_port dst 
             on cc.dest_fnn = dst.port_fnn

I adjusted the query above to work on your data... NULL is different than an empty string. I copied your tables and sample data to SQL Fiddle and then the above query. It appears to be showing NULLS for the source and destination respectively.

我调整了上面的查询来处理你的数据…NULL与空字符串不同。我将您的表和示例数据复制到SQL Fiddle，然后将上面的查询复制。它似乎分别显示了源和目的地的空值。

SQLFiddle per your example structure and data

以你的示例结构和数据为例。

#4

[This answer was in the context of an earlier version of the qeustion where the example input unclearly described the empty string ie '' as being NULL/< NULL >/null so it seemed like the "" values were NULL. Had they been NULL this answer applied. Sqlfiddle]

[这个答案是在之前版本的qeustion的上下文中，在这个版本中，示例输入不清楚地描述了空字符串ie“为NULL/< NULL >/ NULL”，所以看起来“值为NULL”。如果它们是零，这个答案就适用了。Sqlfiddle]

Wrong "desired output"

错误的期望输出值”

The equip4 1/2 in your "desired output" is a mistake, you want equip1 1/2 from port id 2 via 4444. After you correct that, the desired output is what you described (unclearly) elsewhere.

在你的“期望输出”中，设备1/2是一个错误，你希望设备1/2从端口id 2到4444。在您纠正这个错误之后，期望的输出是您在其他地方所描述的(不清楚的)。

NULL port_fnn is not a problem

NULL port_fnn不是问题。

The null port_fnn is not putting anything into the table from the query. The LEFT JOINs never match it.

null port_fnn不会从查询中放入任何东西。左边的连接永远不匹配。

What rows do you want?

你要哪几排?

You are unclear. The only rows that you don't address are if a non-NULL source_fnn or dest_fnn has no matching port_fn. Then in the LEFT JOIN output it is paired with NULL port_fnn info.

你是不清楚。如果非空source_fnn或dest_fnn没有匹配的port_fn，那么您不处理的唯一行是。然后在左连接输出中，它与NULL port_fnn信息配对。

It would be strange if you didn't want those rows. Not only would you have no row for a cust_connection but the other half of the extended information would be thrown away with it. Maybe you would only throw away rows where both source and dest are non-NULL with match. Still strange. You'd have to tell us whether you want these rows. Presumably you want them, beause they're in your query.

如果你不想要这些行，那就太奇怪了。您不仅不会为cust_connection设置行，而且还会丢弃其他一半的扩展信息。也许您只会抛出两个源和最大非null的行。还奇怪。你必须告诉我们你是否想要这些行。假设你想要它们，因为它们在你的查询中。

If every source_fnn and dest_fnn has a matching port_fnn, ie there are foreign keys from source_fnn adn dest_fnn to port_fnn, then this can never happen and your query is correct.

如果每个source_fnn和dest_fnn都有一个匹配的port_fnn(从source_fnn adn dest_fnn到port_fnn有外键)，那么这就永远不会发生，您的查询是正确的。

So your query seems ok.

因此，您的查询似乎可以。

And it seems that you wrongly guessed that it was failing for NULL port_fnn to explain why it differed from your wrong expected output.

而且，您似乎错误地猜测到，它在NULL port_fnn上失败了，从而解释了它与您错误的预期输出的不同之处。

#1