SQL Query to Find Duplicate form two different tables-CodePudding

I have two tables ganadordia and ganadornoche with numbers in column winNumber for both tables. I would like to create a new table based on the duplicated numbers found in each table

However, I'm encountering an issue: winNumber is creating an additional duplicate. For example, if I have two numbers 101 instead of getting only 2 I get 3

SELECT Distinct  ganadoresdia.winNumber as 'Winner Day',ganadoresdia.Month as 'Month Day', ganadoresnoche.winNumber as ' Winner Night',
ganadoresnoche.month as 'Month Night'
FROM ganadoresdia, ganadoresnoche
where ganadoresnoche.winNumber = ganadoresdia.winNumber
ORDER BY ganadoresdia.winNumber asc

Does anyone know how to fix this? it show 13 instead of 8 Table Example

This is the result for ganadornocheit should only display 5 exemple

This is the result for ganadordia it should only display 3 example

CodePudding user response：

From your sample I assume what you actually want to do is union the data rather than join (5 3=8). To do a join you need a primary:foreign key relationship, which you don't seem have.

SELECT 'day' as day_night_indicator,
       winNumber,
       month
  FROM ganadoresdia
 UNION
SELECT 'night' as day_night_indicator,
       winNumber,
       month
  FROM ganadoresnoche