SELECT QUERY SORT PROBLEM

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 doesn't seem to work:

SELECT m.studno, m.studname FROM MASTERTABLE M 
inner JOIN TRANSACTTABLE S
ON m.STUDNO <> s.STUDNOO  and  m.studname <&开发者_高级运维gt; 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 doesn't exist in the transact table, and the last two is bbb & ccc since it does exist in the tranasct table.

How can I achieve this?

thanks. Please help

Try this:

SELECT m.studno, m.studname
FROM MASTERTABLE M
LEFT JOIN TRANSACTTABLE S ON m.STUDNO = s.STUDNOO  and  m.studname = s.studname 
ORDER BY s.STUDNO

The NULL values in s.STUDNO will collect all the rows with no matches.

Try this - it basically uses the LEFT OUTER JOIN that Phil's answer has, and based on whether or not the TransactTable has a value, it defines a Sequence value and sorts on that:

DECLARE @MasterTable TABLE (ID INT, StudNo INT, StudName VARCHAR(20))

INSERT INTO @MasterTable 
  VALUES(9, 78797, 'aaa'), (10, 878, 'bbb'), (11, 675, 'ccc'), (12, 099, 'ddd')

DECLARE @Transact TABLE (ID INT, StudNo INT, StudName VARCHAR(20))

INSERT INTO @Transact 
  VALUES(13, 878, 'bb'), (14, 675, 'ccc')

SELECT 
    m.studno, m.studname,
    CASE WHEN s.studname IS NULL THEN 1 ELSE 0 END 'Sequence'
FROM 
    @MASTERTABLE M
LEFT OUTER JOIN 
    @TRANSACT S ON m.STUDNO = s.STUDNO  
ORDER BY 
    sequence DESC, s.STUDNO DESC

The output I get is this:

studno  studname  Sequence
78797   aaa         1
   99   ddd         1
  878   bbb         0
  675   ccc         0