hSJEs3eu

1ID | Animal
21  | Man
32  | Panda 
4     
5ID | Fruit
61  | Bananna
72  | Apple
83  | Bamboo 
9     
10Man   | Bananna
11Man   | Apple
12Panda | Bananna
13Panda | Bamboo 
14     
15DROP TABLE IF EXISTS A;
16
17CREATE TABLE A
18(ID SERIAL PRIMARY KEY
19,Animal VARCHAR(12) NOT NULL UNIQUE
20);
21
22INSERT INTO a VALUES
23(1,'Man'),
24(2,'Panda');
25
26DROP TABLE IF EXISTS B;
27
28CREATE TABLE B
29(ID SERIAL PRIMARY KEY
30,Fruit VARCHAR(12) NOT NULL UNIQUE
31);
32
33INSERT INTO b VALUES
34(1,'Banana'),
35(2,'Apple'),
36(3,'Bamboo');
37
38SELECT * 
39  FROM a 
40  JOIN b 
41    ON (a.id = 1 AND b.id IN(1,2)) 
42    OR (a.id = 2 AND b.id IN(1,3));
43+----+--------+----+--------+
44| ID | Animal | ID | Fruit  |
45+----+--------+----+--------+
46|  1 | Man    |  1 | Banana |
47|  2 | Panda  |  1 | Banana |
48|  1 | Man    |  2 | Apple  |
49|  2 | Panda  |  3 | Bamboo |
50+----+--------+----+--------+ 
51     
52CREATE VIEW BadWay
53AS
54SELECT map.AID, a.Name AS AName, map.BID, b.Name AS BName
55FROM TableA AS a
56     INNER JOIN
57     (   SELECT 1 AS AID, 1 AS BID UNION ALL
58         SELECT 1 AS AID, 2 AS BID UNION ALL
59         SELECT 2 AS AID, 1 AS BID UNION ALL
60         SELECT 2 AS AID, 3 AS BID
61     ) AS map
62        ON map.AID = a.ID
63     INNER JOIN TableB AS b
64        ON b.ID = map.BID; 
65     
66CREATE TABLE ABMapping 
67(   
68    AID INT NOT NULL, 
69    BID INT NOT NULL
70); 
71INSERT INTO ABMapping (AID, BID) 
72VALUES (1, 1), (1, 2), (2, 1), (2, 3); 
73     
74CREATE VIEW GoodWay
75AS
76SELECT  map.AID, a.Name AS AName, map.BID, b.Name AS BName
77FROM    TableA AS a
78        INNER JOIN ABMapping AS map
79            ON map.AID = a.ID
80        INNER JOIN TableB AS b
81            ON b.ID = map.BID; 
82     
83SELECT A.Animal, B.Fruit
84FROM A
85INNER JOIN (SELECT 1 as Animal_ID,1  as Fruit_ID UNION ALL 
86            SELECT 1, 2 UNION ALL 
87            SELECT 2, 1 UNION ALL 
88            SELECT 2,3) Derived
89 on A.ID = Derived Animal_ID
90INNER JOIN B 
91 on Derived.Fruit_ID = B.ID 
92     
93SELECT A.Animal B.Fruit
94FROM A
95CROSS JOIN B
96WHERE not exists (SELECT 1 
97                  FROM (SELECT 1 as Animal_ID,1  as Fruit_ID UNION ALL 
98                        SELECT 1, 2 UNION ALL 
99                        SELECT 2, 1 UNION ALL 
100                        SELECT 2,3) Derived
101                  WHERE A.ID = Derived.Animal_ID
102                   and  B.ID = Derived.Fruit_ID)