How to use multiple WHERE in SQL?

Question

I have two tables:

Table A: ticket
RoundID | PlayerID | num1 | num2 | num3 | num4 | num5 | num6

Table B: lotteryresult
RoundID | num1 | num2 | num3 | num4 | num5 | num6

I have created a selector as:

SELECT roundID, UserInfo_ID, num1, num2, num3, num4 , num5, num6
FROM ticket JOIN lotteryresult ON ticket.roundID = lotteryresult.roundID
WHERE...; -- I want to match num1 to num6 to find matched numbers

The problem is I don't know what to do to match num1 to num6, and the difficulty is that a number located in num1 at Table A (e.g. '6') could be located at num3 at Table B.

So what can I do to match these numbers?

can the results be provided as a group of six numbers or they have to come from a table? — Richard Muvirimi
– Richard Muvirimi, Commented Apr 4, 2021 at 15:13
Can you add , say, 3 players on 3 rounds (that would be 9 rows for ticket) and 3 rounds for lottery result to your question, so I don' need to type any results from my side?. I have a solution, but I'm too lazy to create test data ... — marcothesane
– marcothesane, Commented Apr 4, 2021 at 15:14
You can only have a single where in a select, if you want multiple conditions, use AND and OR. — Mark Rotteveel
– Mark Rotteveel, Commented Apr 4, 2021 at 15:41

shawnt00 · Accepted Answer · 2021-04-05 03:12:01Z

1

I'm not sure what the goal is:

where
    a.num1 in (b.num1, b.num2, b.num3, b.num4, b.num5, b.num6) or
    a.num2 in (b.num1, b.num2, b.num3, b.num4, b.num5, b.num6) ...

Or maybe you're wanting to count matches?

select *
from ticket t inner join lotteryresult r on r.roundID = t.roundID
    cross apply (
        select sum(case when pvt.num in (r.num1, r.num2, r.num3, r.num4, r.num5, r.num6) then 1 end
        from (values (t.num1), (t.num2), (t.num3), (t.num4), (t.num5), (t.num6k)) pvt(num)
) m(matches)

edited Apr 5, 2021 at 3:12

answered Apr 4, 2021 at 16:48

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dávid Laczkó · Accepted Answer · 2021-04-04 15:13:34Z

0

If I understand well, you want either ticket.num1 = lotteryresult.num6 or ticket.num1 = lotteryresult.num3 to match, and it's just like that:
ticket.num1 = lotteryresult.num6 or ticket.num1 = lotteryresult.num3
and a shorter version of that is
ticket.num1 in (lotteryresult.num6, lotteryresult.num3)
and you can add more columns of lotteryresult if needed.

answered Apr 4, 2021 at 15:13

Dávid Laczkó

1,0662 gold badges7 silver badges28 bronze badges

Comments

marcothesane · Accepted Answer · 2021-04-04 21:47:17Z

Pivot both ticket table and result table vertically; join those pivoted tables on round id and the one of the six numbers resulting out of pivoting vertically, and count.

Here goes:

WITH
-- some input data ...
ticket(roundid , playerid , num1 , num2 , num3 , num4 , num5 , num6) AS (
            SELECT 1,1, 5,13,22,27,42,49
  UNION ALL SELECT 2,1, 5,13,22,27,42,49
  UNION ALL SELECT 3,1, 5,13,22,27,42,49
  UNION ALL SELECT 1,2, 6,14,23,28,44,48
  UNION ALL SELECT 2,2, 6,14,23,28,44,48
  UNION ALL SELECT 3,2, 6,14,23,28,44,48
  UNION ALL SELECT 1,3, 7,15,24,29,43,47
  UNION ALL SELECT 2,3, 7,15,24,29,43,47
  UNION ALL SELECT 3,3, 7,15,24,29,43,47
)
,
-- some input data ...
lotteryresult(roundid , num1 , num2 , num3 , num4 , num5 , num6) AS (
            SELECT 1,       5,    23,    29,34,36,40
  UNION ALL SELECT 2,       1,    12,    16,23,31,48
  UNION ALL SELECT 3,       4,    12,    13,19,31,46
)
,
-- real query starts here, replace comma above with "WITH"
-- need a series of integers ...
i(i) AS (
            SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
  UNION ALL SELECT 5
  UNION ALL SELECT 6
)
,
unpivot_tk AS (
  SELECT
    roundid
  , playerid
  , CASE i
      WHEN 1 THEN num1
      WHEN 2 THEN num2
      WHEN 3 THEN num3
      WHEN 4 THEN num4
      WHEN 5 THEN num5
      WHEN 6 THEN num6
    END AS num
  FROM ticket CROSS JOIN i
)
,
unpivot_res AS (
  SELECT
    roundid
  , CASE i
      WHEN 1 THEN num1
      WHEN 2 THEN num2
      WHEN 3 THEN num3
      WHEN 4 THEN num4
      WHEN 5 THEN num5
      WHEN 6 THEN num6
    END AS num
  FROM lotteryresult CROSS JOIN i
)
SELECT
  tk.roundid
, tk.playerid
, COUNT(*) AS hits
FROM unpivot_tk tk
JOIN unpivot_res USING(roundid,num)
GROUP BY
  tk.roundid
, tk.playerid
ORDER BY
  tk.roundid
, tk.playerid
-- out  roundid | playerid | hits 
-- out ---------+----------+------
-- out        1 |        1 |    1
-- out        1 |        2 |    1
-- out        1 |        3 |    1
-- out        2 |        2 |    2
-- out        3 |        1 |    1

Collectives™ on Stack Overflow

How to use multiple WHERE in SQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related