SELECT Table Based on Condition From Multi tables using UNION?-CodePudding

I have the following four tables:

Produit

id	Produit	Unit	Quantité	Date_S
1	torada55	904	32	08/06/2022
2	teska	560 EHRS	44	08/06/2022
3	shella	905	56	08/06/2022
4	teska	701	12	08/06/2022
5	teska	BNA	45	08/06/2022

Aéro

id	Unit	region	Division
12	560 EHRS	FMC	DNB
14	428 EHAC	FMO	DTM

Commun

id	Unit	region	Division
12	BNA	FMC	DKM
14	BMEK	FMO	DE

Flot

id	Unit	region	Division	Type_Equip	Equip	Prod_Util	Périod_Vidange	Q_vid_equi
14	904	FMC	DNBLS	MOTEUR&DG	Moteur diesel	SHELL	15	12
23	905	FME	DNBLS	MOTEUR&DG	Moteur diesel	SHELL	15	12
22	906	FMO	DNBLS	MOTEUR&DG	Moteur diesel	SHELL	15	12
21	911	FME	DNBLS	MOTEUR&DG	Moteur diesel	SHELL	15	12
55	701	FME	DNBLS	MOTEUR&DG	Moteur diesel	SHELL	15	12

Expected Output

id	Produit	Unit	Quantité	Date_S
1	torada55	904	32	08/06/2022
2	teska	560 EHRS	44	08/06/2022
5	teska	BNA	45	08/06/2022

I've tried this Query :

SELECT * 
FROM Produit,
     Aéro 
WHERE Aéro.Unit = Produit.Unit 
  AND Aéro.region = 'FMC' 

UNION 

SELECT * 
FROM Produit,
     Commun 
WHERE Commun.Unit = Produit.Unit 
  AND Commun.region = 'FMC' 

UNION 

SELECT * 
FROM Produit,
     Flot 
WHERE Flot.Unit = Produit.Unit 
  AND Flot.region = 'FMC'

While I select "Produit" Based on just the "Commun" and "Aéro" tables, it works just fine. If I select just "Produit" based on "Flot" table, it's work too. But when I select "Produit" based on the other three tables, it returns error.

CodePudding user response：

If you want to extract all products whose unit is associated to the region "FMC", you can apply the UNION ALL on the units from your three filtering tables ("Aéro", "Commun" and "Flot"), then select all those products that allow that unit in the corresponding "Produit.Unit".

SELECT * 
FROM Produit 
WHERE Unit IN (SELECT Unit FROM Aéro   WHERE region = 'FMC'
               UNION ALL 
               SELECT Unit FROM Commun WHERE region = 'FMC'
               UNION ALL
               SELECT Unit FROM Flot   WHERE region = 'FMC')

Check the demo here.

Note: UNION ALL is more efficient than UNION because it avoids you a heavy aggregation. Check the difference here.