Multiple condition using "AND" operator and return exact matching rows from a single table using MySQL

Question

select 
  games_exchange.u_exchange_id 
from 
  games_exchange 
where  
  ( games_exchange.game_id = 7 
  AND games_exchange.exchange_type = 1 )
  AND (
  games_exchange.game_id = 7 
  AND games_exchange.exchange_type = 2 )
group by 
  games_exchange.u_exchange_id 
HAVING 
  COUNT( games_exchange.u_exchange_id ) = 2

My expected result from above query is u_exchange_id = 171. but the above query return u_exchange_id = 171 & 170.

[the screenshot for table is here][2]

use **OR** . it not possible to have type = 1 **AND** type = 2 — Bernd Buffen, Dec 10 '18 at 11:33
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Dec 10 '18 at 11:33
try: `... WHERE first_game_id_fk_array = 7 AND second_game_id_fk_array = 7 AND type IN (1, 2)` or: `... WHERE (first_game_id_fk_array = 7 OR second_game_id_fk_array = 7) AND type IN (1, 2)` Your question is not well documented - we cannot understand what You exactly want — num8er, Dec 10 '18 at 11:35
@Bangash I think Bernd means `( my_table.first_game_id_fk_array IN ('7') AND (my_table.type = 1) ) OR ( my_table.second_game_id_fk_array IN ('7') AND (my_table.type = 2) )` . Otherwise your query makes no sense - it would always check each row to see if it has a type of 1, and then it would check again to see if that row also has a type of 2. Clearly that's impossible. Of course you can then compress that query to the version num8er has just suggested, which is shorter and neater. (Another point - using `IN` when you only have one value (e.g. `IN ('7')` is pointless - `=` will do the job fine — ADyson, Dec 10 '18 at 11:36
provide a visuals of fields and data and what You expect. otherwise we cannot understand Your question — num8er, Dec 10 '18 at 11:37
Why are you using `IN ()` with only ONE possible value? Use `=` if there is only one value to match — RiggsFolly, Dec 10 '18 at 11:38
'Note: first_game_id_fk_array AND second_game_id_fk_array have one column named games_ids' - I don't understand this statement - are you attempting to use foreign keys in a query?- if so that's not possible. Please add table definition as text to the question. — P.Salmon, Dec 10 '18 at 11:41
You haven't actually said what you are trying to achieve here so you will attract best guess answers based on you non working code. — P.Salmon, Dec 10 '18 at 12:01
Demo sql server: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ec5538a7f8b4be3de9ee26d495296909 — Bangash, Dec 11 '18 at 19:15
i have already done it, below is the correct answer to this question. thanks — Bangash, Dec 11 '18 at 19:18

Bangash · Accepted Answer · 2018-12-11T18:56:30.293

2

After a lot of research i came up with a solution to my problem. Actually i was using where condition in a wrong way with having clause. The proposed solution for my problem is given in below code

SELECT u_exchange_id FROM games_exchange 
WHERE 
u_exchange_id IN 
( SELECT u_exchange_id FROM games_exchange WHERE game_id = 7 AND 
exchange_type = 1 )
AND 
u_exchange_id IN 
( SELECT u_exchange_id FROM games_exchange WHERE game_id = 7 AND 
exchange_type = 2)
GROUP BY u_exchange_id 
HAVING COUNT( u_exchange_id ) = 2

Note: I achieved this by using foreach() on games ids of both section and I added dynamically sub-query for every id.

edited Dec 11 '18 at 18:56

answered Dec 11 '18 at 12:02

Bangash

117
9

1

based on your sample data, this does not return any results. Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=197aaee7074975e9902e0b5547425bfe – ADyson Dec 11 '18 at 15:38
@ADyson you are right, check the query now, i have updated my answer with a right version. here is the result for above query. Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ec5538a7f8b4be3de9ee26d495296909 – Bangash Dec 11 '18 at 19:15

Multiple condition using "AND" operator and return exact matching rows from a single table using MySQL

1 Answers1