[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

right outer join or full outer join..

Posted on 2014-08-24
9
Medium Priority
?
266 Views
Last Modified: 2014-09-23
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

SELECT
      table2.class,
isnull(table1.fielda,0),
isnull(table2.field3,0)
      
FROM
      table1 ,
      table2
WHERE
      table1.ordernumber = @OrderNumber
      and table2.class = 'DT'
      and table1.class =* cdef.class
0
Comment
Question by:myst_black
  • 4
  • 3
  • 2
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40281757
Something like this perhaps:
DECLARE @OrderNumber int
SET @OrderNumber = 30395

SELECT  t2.class,
        ISNULL(t1.fielda, 0),
        ISNULL(t2.field3, 0)
FROM    table1 t1
        LEFT JOIN table2 t2 ON t1.class = t2.class
WHERE   t1.ordernumber = @OrderNumber

Open in new window

0
 

Author Comment

by:myst_black
ID: 40281759
hi

after reviewing some more, I think this query is broken even in the 2000 syntax. it seems to bring back the exact same data if an order number exists in table1 with a record of class dt or not..

so I have to review with the users to see if this is even used anywhere.


thank you.
0
 

Author Comment

by:myst_black
ID: 40281854
hi here is another query that is not upgrading and returning the same results as it does with the sql 2000 joins: this one returns records if an ordernumber exists in the order table, does not exist in the allotment table and the records that match in the class def table (1 record in the order table, no record in the allotment table,  3 records are returned, because there are 3 allotment records).  Again, here I have tried left, right and full joins and nothing brings back the same data as the old joins. Is it possible to do this with joins or do I need to go another route?

SELECT
      o.OrderNumber, o.companyname Company, cdef.shortname RegClass,
      isnull(xa.Allotment,0) Allotment,
      IsNull((select sum(qty) from tab_item where OrderNumber = xa.OrderNumber and ItemCode = cdef.Purchase_Allotment_Item and ItemStatus = 'A'),0) Purchased,
      IsNull((select count(*) from tab_reg where regclass = xa.regclass and OrderNumber = xa.OrderNumber),0) Used
      
FROM
      tab_Order o , tab_exhibitorallotment xa,       tab_ss_classdefinitions cdef
--      LEFT OUTER JOIN Tab_ExhibitorAllotment xa on o.OrderNumber = xa.OrderNumber
--      RIGHT OUTER JOIN TAB_SS_ClassDefinitions cdef on xa.Regclass = cdef.ShortName

WHERE
      (cdef.exhibitor = 1)
      and xa.regclass =* cdef.shortname
      and o.ordernumber *= xa.ordernumber
      and o.ordernumber <> 30000
      and o.OrderType = 1

Order By
       o.companyname
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:myst_black
ID: 40281859
thank you Anthony, but I tried that and it only returns records that actually have a matching value in t2, that's been the issue, I can't get it to return records that do NOT exist in t2, perhaps I need to stop trying to use a join and try a not exist??
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 750 total points
ID: 40282485
>>"I have tried left, right and full joins and nothing brings back the same data as the old joins"

In your question you use "table1" and "table2" yet later the original query references cdef.class

Sometimes the "simplification" of queries for use in questions results in small errors or omissions and this might be the case here? (please don't be offended it's not a criticism, just exploring possibilities)

DECLARE @OrderNumber int
SET @OrderNumber = 30395

SELECT 
      table2.class, 
isnull(table1.fielda,0),
isnull(table2.field3,0)
      
FROM
      table1 , 
      table2 
WHERE
      table1.ordernumber = @OrderNumber
      and table2.class = 'DT'
      and table1.class =* cdef.class

Open in new window

If Table2 and cdef are the same table. then that query produces the result of an INNER JOIN (because a NULL in table2.class is not allowed)

If cdef is a different table altogether, then table1 & table2 are joined by an inner join
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40282499
full outer join works for test case:
| ORDERNUMBER | REGCLASS | SHORTNAME |
|-------------|----------|-----------|
|      (null) |   (null) |       DET |

SELECT
      *
FROM tab_exhibitorallotment xa
      FULL OUTER JOIN tab_ss_classdefinitions cdef
                  ON xa.RegClass = cdef.ShortName
WHERE xa.OrderNumber IS NULL
OR cdef.ShortName IS NULL
;



CREATE TABLE tab_exhibitorallotment	
	([OrderNumber] int, [RegClass] varchar(4))
;
	
INSERT INTO tab_exhibitorallotment	
	([OrderNumber], [RegClass])
VALUES
	(30395, 'abc'),
	(30395, 'cdef'),
	(30395, '123'),
	(30395, '456')
;

CREATE TABLE tab_ss_classdefinitions
	([ShortName] varchar(4))
;
	
INSERT INTO tab_ss_classdefinitions
	([ShortName])
VALUES
	('abc'),
	('cdef'),
	('123'),
	('456'),
	('DET')
;

Open in new window

http://sqlfiddle.com/#!3/538f5/4
0
 

Author Comment

by:myst_black
ID: 40282944
hi, yes, table2 and cdef are the same, and ok, I understand the not null allowed in the field. Thank you! And I will try the full outer join again and see what happens. However, I think my best bet is to check with the users to see if this is actually something they expect to work, because I don't think it IS in fact what they want. :) I really appreciate the assistance with this query. Is there any help on the second query I posted? That one I know they use, which has both a left outer and right outer (old 2000 joins in it). THANK YOU!!!!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40283632
I tried that and it only returns records that actually have a matching value in t2, that's been the issue,
Then you cannot have written it correctly.  The query you posted afterwards proves that.  If it is truly a LEFT JOIN you cannot do this:
WHERE
      (cdef.exhibitor = 1)

By doing that, you have just made it an INNER JOIN.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 750 total points
ID: 40283644
This should be a good translation of your query you posted earlier:
SELECT  o.OrderNumber,
        o.companyname Company,
        cdef.shortname RegClass,
        x.Allotment,
        ISNULL((
                SELECT  SUM(qty)
                FROM    tab_item
                WHERE   OrderNumber = x.OrderNumber
                        AND ItemCode = x.Purchase_Allotment_Item
                        AND ItemStatus = 'A'
               ), 0) Purchased,
        ISNULL((
                SELECT  COUNT(*)
                FROM    tab_reg
                WHERE   regclass = x.regclass
                        AND OrderNumber = x.OrderNumber
               ), 0) Used
FROM    tab_Order o
        LEFT JOIN (
                   SELECT   xa.ordernumber,
                            xa.regclass,
                            cdef.shortname,
                            ISNULL(xa.Allotment, 0) Allotment,
                            cdef.Purchase_Allotment_Item
                   FROM     tab_exhibitorallotment xa
                            LEFT JOIN tab_ss_classdefinitions cdef ON xa.regclass = cdef.shortname
                                                                      AND cdef.exhibitor = 1
                  ) x ON o.ordernumber *= x.ordernumber
WHERE   o.ordernumber <> 30000
        AND o.OrderType = 1
ORDER BY o.companyname

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question