子查询的子查询 [英] Subqueries on subqueries

问题描述

我正在尝试创建一个select语句

I'm trying to create a select statement

我需要从一个表中收集一个等于另一个表中使用的相同代码的记录。

I need to gather from one table a record that equals the same code used in another table.

更好地放置，客户端从下拉列表中选择城市和该城市的类别。
当客户端单击下一个时，将显示该城市中满足该类别的各个地方。
，但我不知道我会出错。

better put, a client selects from a drop down list a city and a category for that city. When the client clicks next, the various places in that city that meet that category are displayed. but I can't figure out where I'm going wrong.

现在当客户选择一个城市，有两个记录为每个城市，一个适当的城市代码和一个邮政信箱代码，它在我的第一个查询中以bx作为第一个字母

now when the client selects a city, there are two records that read for each city, a proper city code and a post box code that has 'bx' as the first letters

，以消除重复的邮件，我说

in my first query, to eliminate duplicates, I say

select c.[Description] from city c 
where c.Provincecode like 'EC' and 
      c.citycode in (select c.citycode from City c 
                     where SUBSTRING(c.citycode,0,3) not like 'bx')

，给我一个城市名称。

但现在，如果客户选择了例如只有现金的地方，
在结果中只应该有一条记录

but now, if the client has chosen, for instance, cash only places to see, there should only be one record showing up in the results

但尝试，因为我可能，我无法得到正确的语法

but try as I might, I can't get the right syntax

我试过：

select c.[Description] from city c 
where c.Provincecode like 'EC' and 
      c.citycode in (select c.citycode from City c 
                     where SUBSTRING(c.citycode,0,3) not like 'bx') 
      and exists (select * from Customers cu 
                  where cu.Category like 'SC' and cu.Province like 'EC')

但是这带来了比预期更多的结果

but that brings more results than anticipated

这是使用访问数据库，但我使用SQL来做编码，写入访问。这不是问题

this is done using an access database, but I am using SQL to do the coding, which I re-write into access. that's not a problem

所以如果有人可以提供SQL答案，我可以做其余的

so if someone could please provide the SQL answer, I can do the rest from there

我不知道如果我应该做一个加入。
我尝试了

I'm not sure if I'm supposed to do a join. I did try

select * from 
(select c.[Description] from city c 
 where c.Provincecode like 'EC' and 
       c.citycode in (select c.citycode from City c 
                      where SUBSTRING(c.citycode,0,3) not like 'bx')) x 
join Customers on  province=city.provincecode where Category like 'SC'

无法绑定多部分标识符的错误

but I get errors for multi-part identifier could not be bound

EDIT

这是新查询

select * 
from 
    (
        select c.* 
        from city c 
        where c.Provincecode like 'EC' 
            and c.citycode in
                (
                    select c.citycode 
                    from City c 
                    where SUBSTRING(c.citycode,0,3) not like 'bx'
                )
    ) x 
    join 
    Customers  
        on  province=x.Provincecode 
where Category like 'SC'

返回的是

what is returned is

您可以看到，许多以C Strydom为客户的退休，但所有的城市都有

as you can see, there are too many reults that have C Strydom as the customer, but all the cities are there

这个特定的例子，应该只显示一个记录，第二个

for this particular example, only one record should be shown, the second one

推荐答案

问题是不完整的JOIN（有关详细信息，请参阅问题评论）。该工作的查询是

The issue was an incomplete JOIN (see question comments for details). The query that worked was

select * 
from 
    (
        select c.* 
        from city c 
        where c.Provincecode like 'EC' 
            and c.citycode in
                (
                    select c.citycode 
                    from City c 
                    where SUBSTRING(c.citycode,0,3) not like 'bx'
                )
    ) x 
    INNER JOIN 
    Customers 
        ON Customers.province=x.Provincecode 
            AND Customers.city=x.Citycode 
where Category like 'SC'

这篇关于子查询的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！