Mysql table link child table query-CodePudding

SELECT * from
(SELECT
A. *
B.l abelId
The FROM
Book a
LEFT the JOIN
BranchBook b
ON b.b ookId=Anderson d
WHERE b.l abelId=001 OR b.l abelId=002) ab
Group by ab. Id
Mysql query statement the main table link child table, the query conditions for child table field labelId, query the same book to satisfy multiple labelId label id book (that is, multiple tags AND connection), return to the main table data (group by ab. Id), how do you write the SQL?
Pray god give directions!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CodePudding user response:

Group by cannot select *, this paragraph should can't run

CodePudding user response:

To table a table 2 according to find out like this table 3? (check here is LABELID at the same time satisfy the 2, 3)

CodePudding user response:

refer to the second floor kun jins kao kun jins kao response:

To table a table 2 according to find out like this table 3? (check here is LABELID at the same time satisfy the 2, 3)

Yes.

CodePudding user response:

 
The CREATE TABLE # A (
ID INT 
) 
The CREATE TABLE # B (
BOOKID INT, 
LABELID INT 
) 
# INSERT INTO A VALUES (1), (2), (3) 
INSERT INTO # B VALUES (1, 1), (1, 2), (1, 4), (2, 2), (2, 3), (3, 1), (3, 2), (3, 3) 

SELECT * FROM # A 
SELECT * FROM # B 

The SELECT XX. ID FROM (
The SELECT, Anderson D 
COUNT (*) COUNTS the FROM # A, A, B # B 
WHERE Anderson, D=B.B OOKID AND B.L ABELID IN (2, 3) GROUP BY Anderson, D) XX WHERE COUNTS>=2 

DROP TABLE # A, # B

Ideas are look-up table 2 LABELID=the number of 2 or 3, if the quantity is greater than or equal to 2 to smoke out the corresponding data in a table, but it seems a little bit of trouble,

CodePudding user response:

reference kun jins kao, 4/f, kun jins kao response:

 
The CREATE TABLE # A (
ID INT 
) 
The CREATE TABLE # B (
BOOKID INT, 
LABELID INT 
) 
# INSERT INTO A VALUES (1), (2), (3) 
INSERT INTO # B VALUES (1, 1), (1, 2), (1, 4), (2, 2), (2, 3), (3, 1), (3, 2), (3, 3) 

SELECT * FROM # A 
SELECT * FROM # B 

The SELECT XX. ID FROM (
The SELECT, Anderson D 
COUNT (*) COUNTS the FROM # A, A, B # B 
WHERE Anderson, D=B.B OOKID AND B.L ABELID IN (2, 3) GROUP BY Anderson, D) XX WHERE COUNTS>=2 

DROP TABLE # A, # B

Ideas are look-up table 2 LABELID=the number of 2 or 3, if the quantity is greater than or equal to 2 to smoke out the corresponding data in a table, but it seems a little bit of trouble,

Select *
The from
(SELECT
A. *
GROUP_CONCAT (b.l abelId) as ids
The FROM
Book a
LEFT the JOIN
BranchBook b
ON b.b ookId=Anderson d group by Anderson, d) computed tomography (ct)
WHERE FIND_IN_SET (001, ct. Ids)
AND FIND_IN_SET (002, ct. Ids)
My train of thought is to do with FIND_IN_SET, but this seems data efficiency will be lower

CodePudding user response:

reference 5 floor Harvey__chen reply:

Quote: refer to 4th floor kun jins kao kun jins kao response:

The CREATE TABLE # A (
ID INT
)
The CREATE TABLE # B (
BOOKID INT,
LABELID INT
)
# INSERT INTO A VALUES (1), (2), (3)
INSERT INTO # B VALUES (1, 1), (1, 2), (1, 4), (2, 2), (2, 3), (3, 1), (3, 2), (3, 3)

SELECT * FROM # A
SELECT * FROM # B

The SELECT XX. ID FROM (
The SELECT, Anderson D
COUNT (*) COUNTS the FROM # A, A, B # B
WHERE Anderson, D=B.B OOKID AND B.L ABELID IN (2, 3) GROUP BY Anderson, D) XX WHERE COUNTS>=2

DROP TABLE # A, # B

Ideas are look-up table 2 LABELID=the number of 2 or 3, if the quantity is greater than or equal to 2 to smoke out the corresponding data in a table, but it seems a little bit of trouble,

Select *
The from
(SELECT
A. *
GROUP_CONCAT (b.l abelId) as ids
The FROM
Book a
LEFT the JOIN
BranchBook b
ON b.b ookId=Anderson d group by Anderson, d) computed tomography (ct)
WHERE FIND_IN_SET (001, ct. Ids)
AND FIND_IN_SET (002, ct. Ids)
FIND_IN_SET my train of thought is used to do, but this seems to be the data volume efficiency will be lower if

Your idea is very good, very helpful, thanks