i have 2 tables, a master table and a transaction table. i want to sort the
query result of the master table based on a condition :if a student number and studname is already
existing in the transaction table or basically, if the record (stud no, studname) is already in the
transaction table).
If the records is already exist, the result set should be in the last part of the query.
I've used to query here. I'll just merge the 2 datasets or use a UNION
i have a query here but is doesnt seem to work:
SELECT m.studno, m.studname FROM MASTERTABLE M
inner JOIN TRANSACTTABLE S
ON m.STUDNO <> s.STUDNOO and m.studname <> s.studname
--this query is for the non existing records yet, and it should be on top of the query set.
SELECT m.studno, m.studname FROM MASTERTABLE M
inner JOIN TRANSACTTABLE S
ON m.STUDNO = s.STUDNOO and m.studname = s.studname
--this query is for the existing records, and it should be on the lastrow of the query set.
just a piece of illustration:
Let's say MASTERTABLE CONTAINS
id, studno, studname
9 78797 aaa
10 878 bbb
11 675 ccc
12 099 ddd
TRANSACTTABLE
id, studno, studname
13 878 bbb
14 675 ccc
QUERY RESULT SHOULD BE:
studno, studname
78797 aaa
099 ddd
878 bbb
675 ccc
the first two row should consist of studname aaa and ddd since the two doesnt
exist in the transact table, and the last two is bbb & ccc since it does
exist in the tranasct table. How can i acieve this?
thanks. Please help