Sai Ram Sai Ram - 3 months ago 12
MySQL Question

Select rows from a table where id in another second table based on second table ids shows results top as well as next results as it is + Mysql

I have users table

users

id name
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
7 test7
8 test8
9 test9
10 test10


Payment

id userid name
1 5 test5
2 3 test3
3 9 test9


i want results like this

id name
3 test3
5 test5
9 test9
1 test1
2 test2
4 test4
6 test6
7 test7
8 test8
10 test10


i want whoever pays the amount that one should be display in top order by.

Answer

This will produce your desired results:

SELECT a.id, a.name, 1 AS RN  
FROM users a
INNER join payments b
ON a.id=b.userid

UNION ALL

SELECT a.id, a.name, 2 AS RN 
FROM users a
LEFT JOIN payments b
ON a.id=b.userid
WHERE b.id IS NULL
ORDER BY RN, a.id

The first query simply selects the id and name columns for rows that appear in the payments table.

The second query gets the id and name columns for rows that do not appear in the payments table.

The UNION ALL simply merges the results together.

Notice the 1 AS RN and the 2 AS RN that are in the queries. This is an extra column that I have added so that the results can be ordered how you wanted. You can simply ignore this when you process the data.