How to remove the Cartesian Join

Question

I have two tables which have structure similar to below 2 tables. Name :

ID     | FirstName | LastName
__________________________
123     Akshay     kumar
123     Salman     khan
123     Johnny      lever
123     Tom           Cruise

City:

ID     | City
___________
123     Pune

Now when I Run the below Query:

Select N.*,C.* from Name N,City C where C.ID=N.ID and N.ID=123; 

ID     | FirstName | LastName | ID     | City 
_____________________________________________
123     Akshay     kumar             123     Pune
123     Salman     khan               123     Pune
123     Johnny      lever               123     Pune
123     Tom           Cruise            123     Pune

Pune is getting repeated 4 times. Whereas I want output as below:

ID     | FirstName | LastName | ID     | City 
_____________________________________________
123     Akshay     kumar             123     Pune
123     Salman     khan               
123     Johnny      lever               
123     Tom           Cruise

There is no relation between Name and City Table data except the ID Coumn. Also It is not compulsory that Name table will have more entries than City Table for same ID. City Table can also have more rows than Name Table for same ID.(So Left Join won't work) Please Help Guys.

MySQL or Oracle? And what is the connection between `name` and `city` why does Akshay Kumar get Pune and not any other person? — sticky bit, Apr 01 '20 at 11:33
It's not clear what the question or questions are 1) do you want to suppress repeating values for city id and city city - in which case you should do it in your presentation layer 2) do you want to report cities which have no names in which case you probably need a full join in oracle and a simulated full join in mysql 3) if you want to report names without cities then 2 applies 3) you should not use implict joins unless you want a cartesian product in which case you should use cross join for clarity. — P.Salmon, Apr 01 '20 at 11:42
@stickybit No connection between Akshay and Pune. It's just that City had only 1 row for 123 as Pune. I am using Oracle Sql — user8623671, Apr 01 '20 at 15:46

score 0 · Answer 1 · answered Apr 01 '20 at 11:42

This is a requirement which would typically be handled in your presentation layer, e.g. PHP, or whatever tool be using your database. That being said, we can actually handle your requirement using ROW_NUMBER:

WITH cte AS (
    SELECT n.FirstName, n.LastName, c.ID, c.City,
        ROW_NUMBER() OVER (PARTITION BY c.ID, c.City ORDER BY n.LastName, n.FirstName) rn
    FROM Name n
    INNER JOIN City c ON c.ID = n.ID
    WHERE n.ID = 123
)

SELECT ID, FirstName, LastName,
    CASE WHEN rn = 1 THEN ID END AS ID,
    CASE WHEN rn = 1 THEN City END AS City
FROM cte
ORDER BY
    ID,
    LastName,
    FirstName;

score 0 · Answer 2 · answered Apr 01 '20 at 11:47

You are not looking to remove a Cartesian product.

You are actually looking to put a "break" in your reporting. With Oracle, do this:

break on city

With MySql, you're out of luck. It has no such construct without some non-trivial coding. See this answer: mySQL adding column breaks data.

For other reporting tools, the technique will vary, but please understand, you're actually asking a reporting question, not a SQL question, and the reporting construct you're looking for is a "break" sometimes called a "control break".

Hi James, the query worked when there was only 1 row for 1 ID in City Table. But when there are 2 rows in City Table for 1 ID, Each Row in Name Table is repeating 2 times — user8623671, Apr 01 '20 at 15:57

score 0 · Accepted Answer · answered Apr 01 '20 at 14:28

0

You can use the row_number analytical function as follows:

select n.id, n.firstname, n.lastname, c.id, c.city, 
(select t.*, row_number() over (partition by id order by t.firstname) as rn  from name t) n
left join
(select t.*, row_number() over (partition by id order by t.city) as rn  from city t) c
on n.id = c.id and n.rn = c.rn
order by n.id, n.rn

answered Apr 01 '20 at 14:28

Popeye

35,427
4
10
31

Hi Tejash, Can you please tell how to use Break statement. Because I wrote Break on city Select N.*,C.* from Name N,City C where C.ID=N.ID and N.ID=123; I did not got the desired output. I am using SQL Developer – user8623671 Apr 01 '20 at 15:43

How to remove the Cartesian Join

3 Answers3