从第一张表中通过第二张表的两个ID获取行。

huangapple go评论66阅读模式
英文:

Get row from first table in many to many by two id from second table

问题

以下是翻译好的部分:

我有一个关于SQL查询的问题。我有3个表。
table_1

id name
1 aaa
2 bbb
3 ccc
4 ddd

table_2

id name
1 zzz
2 yyy
3 xxx
4 www

还有一个用于多对多关系的第三个表。

table_1_table_2

table_1_id table_2_id
1 1
1 2
3 1
3 2
3 3
3 4
4 4

最后,我的问题是。
例如,我有来自table_2的id(1和2);
我需要获取所有在t1_t2中具有t2.id = 1和2的table_1中的行。
如何获得这个结果:

table_1_id table_1_name
1 aaa
3 ccc

底部的代码不起作用

SELECT * FROM `table_1`
JOIN `table_1_table_2` ON table_1.id=table_1_table_2.table_1_id
JOIN `table_2` ON table_2.id=table_1_table_2.table_2_id
WHERE table_2.id = 1 OR table_2.id = 2

请注意,我在代码中的WHERE条件中使用了"OR"而不是"AND",因为您希望获取具有table_2.id为1或2的行。

英文:

i have a problem with sql request. I have 3 tables.
table_1

id name
1 aaa
2 bbb
3 ccc
4 ddd

table_2

id name
1 zzz
2 yyy
3 xxx
4 www

And third table for many to many relationship.

table_1_table_2

table_1_id table_2_id
1 1
1 2
3 1
3 2
3 3
3 4
4 4

And finally my question.
For example i have id from table_2 (1 and 2);
I need get all rows from table 1 which have row in t1_t2 where t2.id = 1 and 2
How to get this result:

table_1_id table_1_name
1 aaa
3 ccc

The code bottom is not working

SELECT * FROM `table_1`
JOIN `table_1_table_2` ON table_1.id=table_1_table_2.table_1_id
JOIN `table_2` ON table_2.id=table_1_table_2.table_2
WHERE table_2.id = 1 AND table_2.id = 2

答案1

得分: 0

尝试这个:

SELECT t1.*
FROM table1 t1
JOIN table2_table1 tt ON tt.table1_id = t1.id
JOIN table2 t2 ON tt.table2_id = t2.id
WHERE t2.id = 'second_table_id1'
  AND t2.id = 'second_table_id2';
英文:

try this:

SELECT t1.*
FROM table1 t1
JOIN table2_table1 tt ON tt.table1_id = t1.id
JOIN table2 t2 ON tt.table2_id = t2.id
WHERE t2.id = 'second_table_id1'
  AND t2.id = 'second_table_id2';

答案2

得分: 0

WHERE table_2.id = 1 AND table_2.id = 2
这不可能在同一列中有两个不同的id值
也许你的意思是:

WHERE table_1.id = 1 AND table_2.id = 2

英文:
WHERE table_2.id = 1 AND table_2.id = 2

it is impossible to have a row with two different ids in the same column
perhaps you meant:

WHERE table_1.id = 1 AND table_2.id = 2

答案3

得分: 0

以下是代码的翻译部分:

不需要连接所有表格。只需使用关系表格来计算每个id1的id2数量,然后如果您想要收集id1名称,可以与tab1连接

SELECT tab1.id,
       tab1.name
FROM       tab1 
INNER JOIN tab12
        ON tab1.id = tab12.table_1_id
WHERE tab12.table_2_id IN (1,2)
GROUP BY tab1.id,
         tab1.name
HAVING COUNT(DISTINCT tab12.table_2_id) = 2

输出

id name
1 aaa
3 ccc

在此查看演示

英文:

You don't need to join all tables. It's sufficient to use the relationships table to count amount of id2 for each id1, then join with tab1 if you want to gather id1 names.

SELECT tab1.id,
       tab1.name
FROM       tab1 
INNER JOIN tab12
        ON tab1.id = tab12.table_1_id
WHERE tab12.table_2_id IN (1,2)
GROUP BY tab1.id,
         tab1.name
HAVING COUNT(DISTINCT tab12.table_2_id) = 2

Output:

id name
1 aaa
3 ccc

Check the demo here.

答案4

得分: 0

以下是您提供的代码的翻译部分:

Others already answered, I just propose a slight different way:
其他人已经回答了,我只是提出一个稍微不同的方法:

Example data:
示例数据:

CREATE TABLE t1(
	id		INT, 		
	name	VARCHAR(30)
);
创建表 t1

INSERT INTO t1 VALUES
(1, 	'aaa'),
(2, 	'bbb'),
(3, 	'ccc'),
(4, 	'ddd');
将数据插入表 t1

CREATE TABLE t2(
	id		INT,
	name	VARCHAR(30)
);
创建表 t2

INSERT INTO t2 VALUES
(1, 	'zzz'),
(2, 	'yyy'),
(3, 	'xxx'),
(4, 	'www');
将数据插入表 t2

CREATE TABLE t1_2(
	t1_id 		INT,
	t2_id		INT
);
创建表 t1_2

INSERT INTO t1_2 VALUES
(1, 	1),
(1, 	2),
(3, 	3),
(4, 	4);
将数据插入表 t1_2

Query:
查询:

SELECT id, name
FROM t1 
INNER JOIN (SELECT t1_id, COUNT(*) AS RC
            FROM t1_2
            GROUP BY t1_id) Tab1_2 ON t1.id=Tab1_2.t1_id
WHERE Tab1_2.RC=2
从表 t1 中选择 id  name,与(从 t1_2 表中选择 t1_id,按 t1_id 分组并计算数量作为 RC 的子查询)Tab1_2 进行内连接,其中 t1.id 等于 Tab1_2.t1_id,且 Tab1_2.RC 等于 2

Output:
输出:

+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
结果:

> ---- after your question update 
> ---- 在您的问题更新后

example data
示例数据

...
...
(中间部分省略)

Query
查询

SELECT t1.id,t1.name
FROM t1
INNER JOIN (SELECT  DISTINCT t1_id 
            FROM t1_2  
            WHERE t1_2.t2_id IN (1,2)
            ) t12 ON t1.id=t12.t1_id
从表 t1 中选择 t1.id  t1.name,与(从 t1_2 表中选择 DISTINCT t1_id,其中 t1_2.t2_id  (1,2) 中)的子查询 t12 进行内连接,其中 t1.id 等于 t12.t1_id

Output
输出

+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  3 | ccc  |
+----+------+
结果
英文:

Others already answered, I just propose a slight different way:
Example data:

CREATE TABLE t1(
id		INT, 		
name	VARCHAR(30)
);
INSERT INTO t1 VALUES
(1, 	'aaa'),
(2, 	'bbb'),
(3, 	'ccc'),
(4, 	'ddd');
CREATE TABLE t2(
id		INT,
name	VARCHAR(30)
);
INSERT INTO t2 VALUES
(1, 	'zzz'),
(2, 	'yyy'),
(3, 	'xxx'),
(4, 	'www');
CREATE TABLE t1_2(
t1_id 		INT,
t2_id		INT
);
INSERT INTO t1_2 VALUES
(1, 	1),
(1, 	2),
(3, 	3),
(4, 	4);

Query:

SELECT id, name
FROM t1 
INNER JOIN (SELECT t1_id, COUNT(*) AS RC
FROM t1_2
GROUP BY t1_id) Tab1_2 ON t1.id=Tab1_2.t1_id
WHERE Tab1_2.RC=2

Output:

+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+

> ---- after your question update

example data

...
CREATE TABLE t1_2(
t1_id       INT,
t2_id       INT
);
INSERT INTO t1_2 VALUES
(1,     1),
(1,     2),
(3 ,1),
(3,2),
(3,     3),
(4,     4);

Query

SELECT t1.id,t1.name
FROM t1
INNER JOIN (SELECT  DISTINCT t1_id 
FROM t1_2  
WHERE t1_2.t2_id IN (1,2)
) t12 ON t1.id=t12.t1_id

Output:

+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  3 | ccc  |
+----+------+

huangapple
  • 本文由 发表于 2023年5月24日 19:44:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76323159.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定