英文:
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 |
+----+------+
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论