Geek asks and answers

Sql avoid cartesian products

I am new to SQL and struggling with querying (using Access, FWIW). I have Googled and StackOverflow search, but I have not seen this exact scenario. (This may also be due to the fact that I do not know the correct search conditions.)

I have two pretty simple tables containing similar data.

table1: state, lname, fname, network
table2: state, lname, fname, network

I want to find each person / state combination that matches in two tables, as well as the network from each table that the person is in:

state, lname, fname, t1.network, t2.network.

A person can be in several networks in each table. I want to see each network (from both tables) to which the person belongs.

I started by using JOIN as below:

SELECT t1.state, t1.lname, t1.fname, t1.network, t2.network
FROM t1 INNER JOIN t2 
ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state
GROUP BY t1.state, t1.lname, t1.fname, t1.network, t2.network

I quickly realized that I was getting a Cartesian product. So if "NY, Smith, John" was on two networks in t1 and three networks in t2, I would get something like this:

NY, Smith, John, NetworkA, NetworkB
NY, Smith, John, NetworkA, NetworkA
NY, Smith, John, NetworkB, NetworkA
NY, Smith, John, NetworkB, NetworkB
NY, Smith, John, NetworkA, NetworkC
NY, Smith, John, NetworkB, NetworkC

, , :

NY, Smith, John, NetworkA, NetworkA
NY, Smith, John, NetworkB, NetworkB
NY, Smith, John, NULL, NetworkC

- , ?

+5
5

, , , , , . , UNION 3 .

- :

SELECT t1.state, 
   t1.lname, 
   t1.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table1 t1 
   INNER JOIN table2 t2 
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
UNION 
SELECT t1.state, 
   t1.lname, 
   t1.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table1 t1 
   LEFT JOIN table2 t2 
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
WHERE t2.network IS NULL
UNION 
SELECT t2.state, 
   t2.lname, 
   t2.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table2 t2 
   LEFT JOIN table1 t1
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
WHERE t1.network IS NULL

.

SQL Fiddle .

- EDIT

- . WHERE , . : -)

- , :

SELECT t1.state, 
   t1.lname, 
   t1.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table1 t1 
   LEFT JOIN table2 t2 
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
UNION 
SELECT t2.state, 
   t2.lname, 
   t2.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table2 t2 
   LEFT JOIN table1 t1
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
WHERE t1.network IS NULL

.

BTW - MSAccess, UNION.

.

+1
SELECT DISTINCT t1.state, t1.lname, t1.fname, t1.network, t2.network
FROM t1 INNER JOIN t2 
ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state
GROUP BY t1.state, t1.lname, t1.fname, t1.network, t2.network

, .

(. Inside SQL Server: Querying book). , , , . .

0

UNION:

SELECT * FROM t1
UNION
SELECT * FROM t2

( , , - , SELECT *).

0

, UNION.

( SQL) :

SELECT state, lname, fname, network, network
FROM t1
UNION ALL
SELECT state, lname, fname, network, network
FROM t2

Then I would create other queries that request this one.

I would try to give you more detailed information myself, but I do not have MS Access installed.

0
source

One option is to use a full outer join:

SELECT coalesce(t1.state, t2.state), coalesce(t1.lname, t2.lname), coalesce(t1.fname, t2.fname),
  t1.network, t2.network
FROM t1 FULL OUTER JOIN t2 
ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state
and t1.network = t2.network

Conclusion:

NY  SMITH   JOHN    A   A
NY  SMITH   JOHN    B   B
NY  SMITH   JOHN    NULL    C

Edit: I did not see that you are using Access - this is standard SQL, but I do not know if it will work there.

0
source

More articles:


All Articles