locked
Query to return all results from table1 and matching results from tbl2 RRS feed

  • Question

  • I want to return all records from #maindata where supervisorname = 'Michael Bubble' AND (in addition to those records) return all records from #cancelledsales where companyid = 82.  A total of 3 records should be returned, but only the 1 is being returned, probably bc my join is incorrect....

    Create Table #maindata
    (
          id int,
          efname varchar(25),
          elname varchar(25),
          ephone varchar(10),
          saledate datetime,
          itemsold varchar(20),
          supervisorname varchar(50)
    )
    Insert Into #maindata Values
    (1, 'James', 'Peach', '1234561111', '06/01/2015', 'hair dryer', 'Michael Bubble'),
    (2, 'Fred', 'French', '2221233333', '05/01/2015', 'dishwashwer', 'Michael Bubble'),
    (3, 'Mitch', 'Mitchell', '4342323311', '05/15/2015', 'LCD TV', 'Michael Bubble'),
    (4, 'Hector', 'Velasquez', '5551239999', '06/01/2015', 'stove', 'Jess James'),
    (5, 'Peso', 'Pesquez', '0001230000', '05/19/2015', 'microwave', 'Jess James')
    Create Table #cancelledsales
    (
          cid int,
          id int,
          cfname varchar(25),
          clname varchar(25),
          cancelledreason varchar(50),
          companyid int
    )
    Insert Into #cancelledsales Values
    (1, 1, 'Frankie', 'Snooks', 'Found cheaper', 14),
    (2, 3, 'James', 'Richards', 'Changed Mind', 82)
     
    Select md.*, cs.cancelledreason, cs.companyID from #maindata md
    INNER JOIN #cancelledsales cs
    ON md.id = cs.id
    where md.supervisorname = 'Michael Bubble'
    and cs.companyid = 82
     
    Drop Table #cancelledsales
    Drop Table #maindata

    Monday, June 22, 2015 3:41 PM

Answers

  • Hello!

    You need to use "FULL OUTER JOIN" and replace "AND" with "OR".

    Create Table #maindata
    (
          id int,
          efname varchar(25),
          elname varchar(25),
          ephone varchar(10),
          saledate datetime,
          itemsold varchar(20),
          supervisorname varchar(50)
    )
    Insert Into #maindata Values
    (1, 'James', 'Peach', '1234561111', '06/01/2015', 'hair dryer', 'Michael Bubble'),
    (2, 'Fred', 'French', '2221233333', '05/01/2015', 'dishwashwer', 'Michael Bubble'),
    (3, 'Mitch', 'Mitchell', '4342323311', '05/15/2015', 'LCD TV', 'Michael Bubble'),
    (4, 'Hector', 'Velasquez', '5551239999', '06/01/2015', 'stove', 'Jess James'),
    (5, 'Peso', 'Pesquez', '0001230000', '05/19/2015', 'microwave', 'Jess James')
    Create Table #cancelledsales
    (
          cid int,
          id int,
          cfname varchar(25),
          clname varchar(25),
          cancelledreason varchar(50),
          companyid int
    )
    Insert Into #cancelledsales Values
    (1, 1, 'Frankie', 'Snooks', 'Found cheaper', 14),
    (2, 3, 'James', 'Richards', 'Changed Mind', 82)
     
    Select md.*, cs.cancelledreason, cs.companyID from #maindata md
    FULL OUTER JOIN #cancelledsales cs
    ON md.id = cs.id
    where md.supervisorname = 'Michael Bubble'
    or cs.companyid = 82
     
    Drop Table #cancelledsales
    Drop Table #maindata




    Monday, June 22, 2015 4:08 PM