select join return duplicate result

[edjayks]

I have to table, first column of each table is primary key

Table1
User_Id | User_Password | User_StaffNo
---------------------------------------
1234 | pass | 1234
1235 | pass | 1235
1236 | pass | 1236

Table2
Table2_Id | User_Id | Comp_Id
----------------------------------
1 | 1234 | 2
2 | 1235 | 2

i want to query for the row that exist in Table1 but not in Table2.
my query = select Table1.User_StaffNo from Table1,Table2 where Table1.User_Id<>Table2.User_Id

the query returned one row 1234, one row 1235 and two 1236 which is as below:

User_StaffNo
---------------
1234
1235
1236
1236

What i wanted it to return is just one 1236, like this:

User_StaffNo
---------------
1236

What is wrong here?

[amitpatel66]

I have to table, first column of each table is primary key

Table1
User_Id | User_Password | User_StaffNo
---------------------------------------
1234 | pass | 1234
1235 | pass | 1235
1236 | pass | 1236

Table2
Table2_Id | User_Id | Comp_Id
----------------------------------
1 | 1234 | 2
2 | 1235 | 2

i want to query for the row that exist in Table1 but not in Table2.
my query = select Table1.User_StaffNo from Table1,Table2 where Table1.User_Id<>Table2.User_Id

the query returned one row 1234, one row 1235 and two 1236 which is as below:

User_StaffNo
---------------
1234
1235
1236
1236

What i wanted it to return is just one 1236, like this:

User_StaffNo
---------------
1236

What is wrong here?

Try this:

 
SELECT user_staffno from table1 WHERE user_id NOT IN (SELECT user_id FROM table2)
 
 

[mwasif]

Or try the following

SELECT Table1.User_StaffNo from Table1
LEFT JOIN Table2 ON Table1.User_Id=Table2.User_Id
WHERE Table2.User_Id IS NULL