right outer join or full outer join..
Posted on 2014-08-24
hi I have for example 2 tables. Table one has order numbers , class. Table 2 has class, table one may have order 1234 with 3 records that match values in table 2, however, table 2 might have another value that DOES NOT have an ordernumber to go with it. I need a query that will bring back the one record from table 2 that does not have a matching record in table one. I have tried pretty much every join and combination that I can.
Table one (ordernumber/class is pk: table two class is pk:
Order Number Class Class Longname
30395 abc abc whatever abc is
30395 cdef cdef whatever cdef is
30395 123 123 whatever 123 is
30395 456 456 whatever 123 is
DET whatever DET IS
I need one record returned that shows that order number 30395 DOES NOT HAVE at DET record
here is the original sql 2000 query that works, trying to update the joins.. but have not been able to get it to work, right joins, left joins, full outer joins.. nothing I do brings back that one blank record
DECLARE @OrderNumber int
SET @OrderNumber = 30395
table1.ordernumber = @OrderNumber
and table2.class = 'DT'
and table1.class =* cdef.class