Doing a MySQL SQL query from 2 tables

[webdev1]

Hi

I am trying to demonstrate a random number college project by code.

I have a query which works fine getting data from table:

 

SELECT id,Date, GROUP_CONCAT(ball ORDER BY ball) balls
  FROM
     ( SELECT id,Date,Ball1 ball FROM history eh
       UNION
       SELECT id,Date,Ball2 ball FROM history eh
       UNION
       SELECT id,Date,Ball3 ball FROM history eh
       UNION
       SELECT id,Date,Ball4 ball FROM history eh
       UNION
      SELECT id,Date,Ball5 ball FROM history eh
     ) x
 WHERE ball IN (6, 24, 32, 48, 50)
 GROUP
    BY id HAVING COUNT(*) >= 2

 

This will give 3 columns result:

id,Date,balls which match any 2 or more of above numbers

 

I want to join the result with another table starballs which has the same id as above and stores two colums of data Star1 Star2

i.e. id,Star1,Star2

I assume this will be inner join? to keep the data of top query and join it with starballs table.

 

I have tried all sorts and cannot get it working.

Can someone help me with this please.

 

Thanks

Edited January 10, 2018 by webdev1

[webdev1]

Hi

I am trying to demonstrate a random number college project by code.

I have a query which works fine getting data from table:

 

SELECT id,Date, GROUP_CONCAT(ball ORDER BY ball) balls

  FROM

     ( SELECT id,Date,Ball1 ball FROM history eh

       UNION

       SELECT id,Date,Ball2 ball FROM history eh

       UNION

       SELECT id,Date,Ball3 ball FROM history eh

       UNION

       SELECT id,Date,Ball4 ball FROM history eh

       UNION

      SELECT id,Date,Ball5 ball FROM history eh

     ) x

 WHERE ball IN (6, 24, 32, 48, 50)

 GROUP

    BY id HAVING COUNT(*) >= 2

 

This will give 3 columns result:

id,Date,balls which match any 2 or more of above numbers

 

I want to join the result with another table starballs which has the same id as above and stores two colums of data Star1 Star2

i.e. id,Star1,Star2

select * FROM starballs WHERE STAR1= 5 AND STAR2=9

I assume this will be inner join? to keep the data of top query and join it with starballs table.

 

I have tried all sorts and cannot get it working.

Can someone help me with this please.

 

Thanks

Edited January 10, 2018 by webdev1