Re: Joining results from two tables
Hi, thanks for replying to my problem.
I have designed a table with 5 columns for main numbers and a table with 2 columns for the star balls.
Your suggestion means I have to bin that and have 5 rows per id in one table and 2 per id in the second table which makes the id not unique and will be duplicated on several rows.
Pls advise if you can you help with the working sql query I have below on the current existing structure:
SELECT id,Date, GROUP_CONCAT(ball ORDER BY ball) balls
FROM
( SELECT id,Date,Ball1 ball FROM table1 eh
UNION
SELECT id,Date,Ball2 ball FROM table1 eh
UNION
SELECT id,Date,Ball3 ball FROM table1 eh
UNION
SELECT id,Date,Ball4 ball FROM table1 eh
UNION
SELECT id,Date,Ball5 ball FROM table1 eh
) x
WHERE ball IN (6, 24, 32, 48, 50)
GROUP
BY id HAVING COUNT(*) >= 2
This will produce the following data from the above data sample:
1046 29/09/2017 32,48
1047 03/10/2017 6,24,32,48,50
I wish to join this with a query to find a match for either numbers 1,5 in the second table2 so I get the following matches in the result:
1047 03/10/2017 1 5
and the final result will show a full match for row 1047:
1047 03/10/2017 6 24 32 48 50 1 5
On it's own the single table 2 query would be " select id from euro_history_starballs where Starball1=1 AND Starball2=5;" but I want to join this with the first query.
I have tried left join and cannot get it to work.
Look forward to hearing from anyone on this.