help match the SQL query

SELECT user_id FROM wp_bp_xprofile_data WHERE (field_id=1 and value=23) and (field_id=2 and value=3)

I have to do it so that I pass through both field_id and sift out all with the union, I tried it, it displays it all at once, but I need to work according to the type and so that it goes like this if the first field_id = 1 and value = 23 then it goes further and checks those users who already have field_id = 2 and value = 3

  • not quite clear question. Do I need the query to display the id for the second part of the condition only if records are found for the first part? - Novitskiy Denis
  • So who should be left (well, that is, withdraw, return ...)? those who have both the first and second pairs? those who have neither the one nor the other? those who have only one of the two pairs, no matter what, and the second is not? - Akina
  • It is necessary to display in those who have all the options are the same - Alex Luminoso

2 answers 2

I hope you understand your question correctly. Use EXISTS:

 select user_id FROM wp_bp_xprofile_data WHERE exists (select * FROM wp_bp_xprofile_data where field_id=1 and value=23) and (field_id=2 and value=3)

Here is an example of the request - http://sqlfiddle.com/#!9/939157/2

  • Made so SELECT user_id FROM wp_bp_xprofile_data WHERE EXISTS (SELECT * FROM wp_bp_xprofile_data WHERE field_id = 474 AND VALUE> '17 ') AND (field_id = 474 AND VALUE <'26') AND (field_id = 3 AND value = 'Marriage') 47 earned - Alex Luminoso
  • in the conditions of the task there were other conditions now, of course, my request will not work, because You changed it beyond recognition. Give an example of the data and show exactly what the query should choose. - Novitskiy Denis

It is possible with exists, but it is also possible with join:

 SELECT a1.user_id FROM wp_bp_xprofile_data a1 JOIN wp_bp_xprofile_data a2 ON a2.user_id=a1.user_id WHERE a1.field_id=1 and a1.value=23 and a2.field_id=2 and a2.value=3