MySQL INNER JOIN 两个表，其中一个表包含具有相同 id 的多行

Question

我想根据具有相同 ID 的另一个表中的多行从一个表中获取 ID。仅当其他表中的所有行都与每个行请求匹配时，才必须返回 id。我的表结构如下所示。

   tbl_one ------------------  id  companyName ------------------1   CompanyOne  2   CompanyTwo  tbl_two -----------------------------   id  type        content-----------------------------1   zipcode     543211   category    Car dealers2   zipcode     543212   category    Supermarkets

我已经尝试过使用 INNER JOIN，但无论我如何尝试，我似乎都无法让它工作。

SELECT     tbl_one.id FROM tbl_one INNER JOIN     tbl_twoON    tbl_two.id = tbl_one.id WHERE(     type = 'zipcode' AND content = '54321')   AND(     type = 'category' AND content = 'Car dealers' )

有人可以回答我的问题吗？谢谢:)

Answer 1

嗯..试试这个:-

/*
drop    table tbl_one;
drop    table   tbl_two;

create  table tbl_one(id int, companyName varchar(20)) ;
Insert into tbl_one values
(1,  'CompanyOne'), 
(2,  'CompanyTwo')  ;

create  table tbl_two(id int,type varchar(20), content varchar(20));
insert into tbl_two values
(1 ,  'zipcode'  ,   '54321'),
(1 ,  'category' ,   'Car dealers'),
(2 ,  'zipcode'  ,   '54321'),
(2 ,  'category' ,   'Supermarkets');
*/
SELECT t1.*
FROM  tbl_one t1
WHERE t1.id in (select t2.id from tbl_two t2 where t2.id = t1.id and t2.type = 'zipcode' AND t2.content = '54321')
and     t1.id in (select t3.id from tbl_two t3 where t3.id = t1.id and t3.type = 'category' AND t3.content = 'Car dealers')