JOIN Question

• hayekmichel

  SSC Enthusiast

  Points: 156

  More actions

  September 5, 2017 at 2:26 am

  #325131

  hello guys,
  i am trying to build a query based on a join between 2 tables but i need to display all the left table column and from the second table the answers based on 2 where criteria.

  
  so i would like to do (Select * from LEFT) JOIN / UNION (Select * from RIGHT where InvestorID=@InvID AND BusinessID=@BusID)
  the right should return empty or null in case no records found
  Thanks for your help

• Thom A

  SSC Guru

  Points: 99143

  More actions

  September 5, 2017 at 2:48 am

  #1958050

  When you want to return data from another table, but retain rows if there are no matching results in the joining table, you would use a LEFT JOIN. You would also need to put the criteria for that table in your ON clause, not your WHERE clause, as that would effectively convert your LEFT JOIN into an INNER JOIN.

  Your SQL above is malformed, so here's an example:, and a little explanation:
  USE Sandbox;
GO
CREATE TABLE SampleData (ID int, ItemName varchar(50));
CREATE TABLE SampleJoinData (ID int, ParentID int, Colour varchar(50));

  INSERT INTO SampleData
VALUES
  (1, 'Banana'),
  (2, 'Apple');
INSERT INTO SampleJoinData
VALUES
  (1, 2, 'Green'),
  (1, 2, 'Red');
GO
--All data
SELECT SD.ItemName, SJD.Colour
FROM SampleData SD
    LEFT JOIN SampleJoinData SJD ON SD.ID = SJD.ParentID;
--LEFT JOIN using ON Clause
--Will return both Banana, with no colours and Apple a colour
DECLARE @Colour varchar(50);
SET @Colour = 'Red';

  SELECT SD.ItemName, SJD.Colour
FROM SampleData SD
    LEFT JOIN SampleJoinData SJD ON SD.ID = SJD.ParentID
                              AND SJD.Colour = @Colour;

  --Now, putting the criteria in the WHERE, which will return NO rows
--This is because the WHERE clause effectively turns the LEFT JOIN into an INNER JOIN
SET @Colour = 'Yellow';

  SELECT SD.ItemName, SJD.Colour
FROM SampleData SD
    LEFT JOIN SampleJoinData SJD ON SD.ID = SJD.ParentID
WHERE SJD.Colour = @Colour;

  GO
DROP TABLE SampleData;
DROP TABLE SampleJoinData;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jeff Moden

  SSC Guru

  Points: 1004645

  More actions

  September 6, 2017 at 8:42 pm

  #1958475

  Thom A - Tuesday, September 5, 2017 2:48 AM

  When you want to return data from another table, but retain rows if there are no matching results in the joining table, you would use a LEFT JOIN.

  I'm sure that you know this and will agree... RIGHT JOINs do the same thing but to the other table.  They're just more confusing for folks that read left to right and top to bottom.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)