Selectively extract data from mysql SELECT statements

You can get two matching records in one line by specifying a record with where user_id 1 and a record with user_id 5 in the same category.

select a.*,b.*
from table 1a
left join table 1 b on a.category = b.category and b.user_id = 5
where a.user_id = 1;

http://sqlfiddle.com/#!9/31d8c/1

Sample Results

 id category user_id text id category user_id text
2 200 1 b 3 200 5 c
1100 1 a(null)(null)(null)(null)

The first four columns are the information for the record with user_id 1 and the fifth and subsequent columns are the same category and the user_id is the record with user_id 5.
If there is no record with user_id of 5 in the same category, it will be null after the fifth column.

You can get the desired result by retrieving the SQL results and determining if the fifth column or later is null.

You can also get the desired result by subquerying SQL in solution 1 and determining whether user_id=5 exists in the outer select.

http://sqlfiddle.com/#!9/31d8c/3

select
  case when id5 is null then id1 else id5 end id,
  case when id5 is null then category1 else category5 end category,
  case when id5 is null then user_id1 else user_id5 end user_id,
  case when id5 is null then text1 else text5 end text
from(
  select
    a.id id1,
    a. Category 1,
    a. user_id user_id1,
    a. text text1,
    b.id id5,
    b. Category 5,
    b. user_id user_id5,
    b. text text 5
  from table 1a
  left join table 1 b on a.category = b.category and b.user_id = 5
  where a.user_id = 1
as sub1

SELECT 
  id, category, user_id, text 
from 
  your_table 
WHERE
  user_id in (1,5) 
ORDER BY 
  category, user_id desc
GROUP BY
  category;

How about(I can't check the operation right away, so if it fails, I'll fix it later.)

You should extract records with user_id 1 or 5 and put them together in categories so that 5 comes first for records with both user_id 1 and 5, and finally the GROUP BY category should be free of duplication (5 takes precedence).

I tried to create a SQL by connecting a set that does not have user_id=1 and user_id=5 records in the same category and taking out the set of user_id=5 with union.
As for the interpretation of preferential extraction, if user_id=5 is in the same category, the specification does not return a record of user_id=1.

 select * from tbla
where
    a. user_id = 1
and
    exists (select 1 from tbl b where a.category = b.category and b.user_id<>5)
union
select * from tblc
where
    c.user_id = 5