Cross Join tables without duplicates - MYSQL

Question

I have multiple tables that I need to join and display, however I am having trouble getting exactly what I want. Both tables are identical in headings, though represent data for different events, and look like this:

I am looking to combine the two tables on the FirstName, LastName and City columns, and rename the remaining column tables to prepend the event each represents. When I try to CROSS JOIN the two tables, I get the cartesian product of the two, so I added a WHERE clause to only show rows where the three columns matched. While this helped significantly, I am still left with duplicate rows and have been unable to get rid of them:

This table structure/context is what I need, though as mentioned before, the issue is I don't want to display the duplicate rows.

Here is my query:

SELECT event1.FirstName, event1.LastName, event1.City, event1.AdultsAttending as Event1_AdultsAttending, event1.AdultsInvited as Event1_AdultsInvited, event1.Attending as Event1_Attending, event1.ChildrenAttending as Event1_ChildrenAttending, event1.ChildrenInvited as Event1_ChildrenInvited, event2.AdultsAttending as Event2_AdultsAttending, event2.AdultsInvited as Event2_AdultsInvited, event2.Attending as Event2_Attending, event2.ChildrenAttending as Event2_ChildrenAttending, event2.ChildrenInvited as Event2_ChildrenInvited 
FROM `event1` 
CROSS JOIN `event2`
WHERE event1.FirstName=event2.FirstName AND event1.LastName=event2.LastNameAND event1.City=event2.City

How can I modify this query to get the results I am looking for?

EDIT

Here is a SQLFiddle with my schemas: http://sqlfiddle.com/#!9/770473/2 This is my desired output:

Hi @Sal please use DISTINCT if you remove duplicate data. For clear understanding please provide table structure with sample data and expected output. — Rahul Biswas, Aug 12 '21 at 11:14
@RahulBiswas I tried `DISTINCT` and `DISTINCTROW`, but neither did anything. The table structure with expected output is already included in the post - is there some additional data that you are looking for? Both tables have identical structures, and the output listed has the structure/data I'm looking for, just with duplicate rows. — Sal, Aug 12 '21 at 18:58
@Akina I don't entirely understand what you mean, could you explain further and provide an example perhaps? — Sal, Aug 12 '21 at 18:58
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=503367e1965fba94c74af56a3327372f — Akina, Aug 12 '21 at 19:31
@Akina thank you for the example, I'll give this a shot and let you know if it works — Sal, Aug 12 '21 at 20:04
When it comes to samples, images should be used for graphic matters and text should be used for textual matters. Datasets are textual. Going further, if code is applicable, code should be used; data schemas can and should be described in SQL (rather than images or ad hoc textual schemas). — outis, Aug 12 '21 at 20:07
For clarity, join conditions should be placed in `ON` clauses, rather than a `WHERE` clause (which should be used for filter conditions). — outis, Aug 12 '21 at 20:09
@outis Thank you for the note, I'll keep that in mind moving forward. In regards to your second comment - I originally did have in under the `ON` clause, though while it was a valid query for `LEFT` and `RIGHT` `JOIN`, when I tried using that for `CROSS JOIN`, it said the query was invalid, which is why I resorted to `WHERE`, but your point makes sense. — Sal, Aug 12 '21 at 20:55
@Akina your solution worked! If you create an answer with the solution, I'll mark it as the correct one. Thanks a lot! — Sal, Aug 12 '21 at 20:56
Does this answer your question? [How can I do a FULL OUTER JOIN in MySQL?](https://stackoverflow.com/questions/4796872/how-can-i-do-a-full-outer-join-in-mysql) — outis, Aug 13 '21 at 02:09

score 2 · Answer 1 · answered Aug 12 '21 at 21:07

What you need here is OUTER JOIN since you need results from both tables. Ideally you'd use FULL OUTER JOIN, but MySQL does not support that. You need to UNION results from two OUTER JOINs as below:

SELECT event1.FirstName as FirstName, event1.LastName as LastName, event1.City as City, event1.AdultsAttending as Event1_AdultsAttending, event1.AdultsInvited as Event1_AdultsInvited, event1.Attending as Event1_Attending, event1.ChildrenAttending as Event1_ChildrenAttending, event1.ChildrenInvited as Event1_ChildrenInvited, event2.AdultsAttending as Event2_AdultsAttending, event2.AdultsInvited as Event2_AdultsInvited, event2.Attending as Event2_Attending, event2.ChildrenAttending as Event2_ChildrenAttending, event2.ChildrenInvited as Event2_ChildrenInvited 
FROM event1 LEFT OUTER JOIN event2
  ON event1.FirstName=event2.FirstName AND event1.LastName=event2.LastName AND event1.City=event2.City
UNION
SELECT event2.FirstName FirstName, event2.LastName as LastName, event2.City as City, event1.AdultsAttending as Event1_AdultsAttending, event1.AdultsInvited as Event1_AdultsInvited, event1.Attending as Event1_Attending, event1.ChildrenAttending as Event1_ChildrenAttending, event1.ChildrenInvited as Event1_ChildrenInvited, event2.AdultsAttending as Event2_AdultsAttending, event2.AdultsInvited as Event2_AdultsInvited, event2.Attending as Event2_Attending, event2.ChildrenAttending as Event2_ChildrenAttending, event2.ChildrenInvited as Event2_ChildrenInvited 
FROM event2 LEFT OUTER JOIN event1
  ON event1.FirstName=event2.FirstName AND event1.LastName=event2.LastName AND event1.City=event2.City
ORDER BY FirstName, LastName, City;

http://sqlfiddle.com/#!9/770473/25/0 shows this working. Btw, it looks like your expected result has some values swapped between event1 and event2.

score 0 · Accepted Answer · answered Aug 13 '21 at 06:59

In many DBMSs this can be easily performed with FULL OUTER JOIN, but MySQL does not support it, so you must emulate.

You must gather all (FirstName, LastName, City) combinations which are present in at least one of the tables.

One of the emulation methods is the complete list obtaining via subquery with UNION which removes duplicates (combinations which are present in both tables):

SELECT FirstName, LastName, City,
       event1.AdultsInvited, event2.AdultsInvited  -- the same for another columns pairs
FROM ( SELECT FirstName, LastName, City 
       FROM event1
       UNION
       SELECT FirstName, LastName, City 
       FROM event2 ) total
LEFT JOIN event1 USING (FirstName, LastName, City)
LEFT JOIN event2 USING (FirstName, LastName, City)
ORDER BY FirstName, LastName, City;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=503367e1965fba94c74af56a3327372f

Another method uses a LEFT JOIN b UNION a RIGHT JOIN b, or a LEFT JOIN b UNION ALL a RIGHT JOIN b WHERE a.id IS NULL.

Cross Join tables without duplicates - MYSQL

2 Answers2