Question

我要问:

SELECT DISTINCT
countryName,countrySlug
FROM countries AS Country 
INNER JOIN countries_networks AS n ON Country.id = n.country_id
AND n.network_id IN (1,14)

罚款。然而,我现在需要加上一个必须的条款,这样,N.network_id MUST也就列入这一规定(6,7,8,9)。 (随着Im直接从调查桌上拖走,她们可以有多个网络——女。)

因此,我试图增加另一个国家:

SELECT DISTINCT
    countryName,countrySlug
    FROM countries AS Country 
    INNER JOIN countries_networks AS n ON Country.id = n.country_id
    AND n.network_id IN (1,14)
AND n.network_id IN (6,7,8,9)

现在,这些结果根本没有结果。

这似乎像我这样大错了。谁能看看什么?

Answer 1

是的;你要求将<代码>network_id列入两份非交叉性清单。某一数值<代码>a不可能列入以下两个清单:

1, 14
6, 7, 8, 9

殴打死胎,看一看每个价值

value  list1 list2
------------------
1      x
6            x
7            x
8            x
9            x
14     x

这符合两个清单中的所有一套价值观;任何超出该范围的价值都赢得了“适当”, 要么获得条件,要么在“适当”范围内没有任何数值。

满足以下条件:单一<代码>国家可在<条码>基础上有多个<条码>network_id ;基于<条码>国家_network的链接表。

select distinct
    c.countryname, c.countryslug

from country c

join country_network cn1 on cn1.country_id = c.country_id
join country_network cn2 on cn2.country_id = c.country_id

where cn1.network_id in (1, 14) and cn2.network_id in (6, 7, 8, 9)

Answer 2

是的。

两套中不得同时使用<代码>network_id。 <代码>(1,14)和(6,7,8,9>

你可以通过自我加入来实现预期的结果。

... countries_networks cn1 join countries_networks cn2 on cn1.country_id = cn2.country_id where cn1.network_id in (1,14) and cn2.network_id in (6,7,8,9)

Answer 3

根据我上述评论,试图:

SELECT DISTINCT countryName,countrySlug
FROM countries AS Country 
WHERE Country.id IN (SELECT n.country_id FROM countries_networks n WHERE n.network_id IN (1,14))
AND Country.id IN (SELECT n.country_id FROM countries_networks n WHERE n.network_id IN (6,7,8,9))

业绩是明智的,可能受到两个分局的打击,但可能由于表格中的行数而不一致。我有一席之地。

<>>>>>

采用双重办法,这应当产生你需要的结果:

SELECT DISTINCT countryName,countrySlug
FROM countries AS Country
INNER JOIN countries_networks n1 ON n1.country_id = Country.id
INNER JOIN countries_networks n2 ON n2.country_id = Country.id
WHERE n1.network_id IN (1,14)
AND n2.network_id IN (6,7,8,9)

Answer 4

IN () is a synonym for a series of OR s

So WHERE a IN (1,2,4)
can also be written as

WHERE (a = 1 OR a = 2 OR a = 4)

你正在努力:

WHERE ... a IN (1,14) AND a IN (6,7,8,9)

可以改写

WHERE (a = 1 OR a = 14) AND (n.network_id = 6 OR ....)

无论你是否在加入条款中重新做,也从不认为我没有写出相应的全部法典。” 显然,网络“id cannot同时是两种不同的价值观,这就是为什么你的询问永远不会恢复。

友情链接