CODEWITHSUNDEEP
sqlsql-serverjoin
Jaguar
Jaguar

Reputation: 419

Conditional Join SQL Server

I have a scenario as below:

Source1

Column1 
Column2

Source2

Column1
Column2

Output - I need a view;

All the records in Source1 where Column2 is not empty must be in the view.

All the records in Source1 where Column2 is empty must be joined with Source2 (on Column1, the reference between both tables). Wherever it finds a match then Column2 of Source2 should also be included in the view.

Any pointers please..

Upvotes: 2

Views: 51

Answers (3)

spencer7593
spencer7593

Reputation: 108400

Use an outer join between Source1 and Source2.

The specification is a little loose. Did you want the Column2 from Source2 to be returned as a separate (third) column, or did you want the value from that in the second column, replacing the "empty" value of Column2 from Source1?

What are the datatypes of Column2 in Source1 and Source2? Is that character type, numeric, datetime?

How do you define "empty"? For character types, does that include both NULL values and zero length strings?

Also, what is the cardinality of the relationship between the tables, is it one to one, one to many (which way). Is it it mandatory, or possibly one to zero?

Assuming you want all the rows (if there are multiple rows from Source2 that match a row from Source1, and assuming you want a third column, and assuming the datatype of Column2 is character, and assuming "empty" means NULL or zero-length string (that's a whole lot of assumptions)... then something like this:

  SELECT s.column1
       , s.column2
       , IF(IFNULL(s.column2,'')='',t.column2,'') AS t_column2
    FROM source1 s
    LEFT
    JOIN source2 t
      ON t.column1 = s.column1
     AND IFNULL(s.column2,'') = ''
   ORDER BY s.column1, 2, 3

... will return a result that meets the specification. That query can be altered/tweaked to adjust to a tighter (more precise) specification.

EDIT

Ooops.

The example query above was based on yet another assumption: that this was specific to MySQL.

The syntax of the statement above won't "work" in other databases. Here's an equivalent statement, using more ANSI-standards compliant syntax:

  SELECT s.column1
       , s.column2
       , CASE WHEN s.column2 IS NULL OR s.column2 = ''
           THEN t.column2
           ELSE ''
         END AS t_column2
    FROM source1 s
    LEFT
    JOIN source2 t
      ON t.column1 = s.column1
     AND (s.column2 IS NULL OR s.column2 = '')
   ORDER BY s.column1, s.column2

FOLLOWUP

Added an example that demonstrates the behavior:

SQL Fiddle here: http://sqlfiddle.com/#!9/113e6/1

Setup tables and sample rows:

  CREATE TABLE source1
  ( id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
  , column1 INT
  , column2 VARCHAR(8)
  );
  CREATE TABLE source2
  ( id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
  , column1 INT
  , column2 VARCHAR(8)
  );
  INSERT INTO source1 (id, column1, column2) VALUES
   (1,NULL,NULL)
  ,(2,NULL,'foo')
  ,(3,113,'fee')
  ,(4,114,'fi')
  ,(5,115,'')
  ,(6,116,NULL)
  ,(7,122,'fo')
  ,(8,122,'fum')
  ;
  INSERT INTO source2 (id, column1, column2) VALUES
   (21,NULL,'doh')
  ,(22,113,'rey')
  ,(23,113,'mii')
  ,(24,114,'fah')
  ,(25,115,'sew')
  ,(26,115,'lah')
  ,(27,116,NULL)
  ,(28,116,'')
  ,(29,116,'tea')
  ,(30,116,'doh')
  ;

Example query (same as query above):

  SELECT s.column1
       , s.column2
       , IF(IFNULL(s.column2,'')='',t.column2,'') AS t_column2
    FROM source1 s
    LEFT
    JOIN source2 t
      ON t.column1 = s.column1
     AND IFNULL(s.column2,'') = ''
   ORDER BY s.column1, 2, 3

Example query - same as query above PLUS extra id columns 

  SELECT s.column1
       , s.column2
       , IF(IFNULL(s.column2,'')='',t.column2,'') AS t_column2
      -- ---------------
       , s.id AS s_id
       , t.id AS t_id
      -- ---------------
    FROM source1 s
    LEFT
    JOIN source2 t
      ON t.column1 = s.column1
     AND IFNULL(s.column2,'') = ''
   ORDER BY s.column1, 2, 3

Returns:

    column1  column2  t_column2    s_id    t_id
    -------  -------  ---------  ------  --------
     (NULL)  (NULL)   (NULL)          1    (NULL)
     (NULL)  foo                      2    (NULL)
        113  fee                      3    (NULL)
        114  fi                       4    (NULL)
        115           lah             5        26
        115           sew             5        25
        116  (NULL)   (NULL)          6        27
        116  (NULL)                   6        28
        116  (NULL)   doh             6        30
        116  (NULL)   tea             6        29
        122  fo                       7    (NULL)
        122  fum                      8    (NULL)

Note that this example includes "duplicate" values in column1, of both source1 and source2, and shows the results that are returned. (The s_id and t_id columns are included to aid in deciphering the rows returned.)

Upvotes: 1

Mike K
Mike K

Reputation: 486

So to recap: only include lines where Source1.Column2 is populated, and include Column2 of Source2 as well if that is also populated?

What you're looking for is a LEFT JOIN. Learn it and love it, because it's one of the handiest things in the whole of SQL.

   SELECT s1.Column1, s1.Column2, s2.Column2
     FROM source1 s1
LEFT JOIN source2 s2 ON s1.Column1 = s2.Column1
    WHERE s1.Column2 IS NOT NULL

Upvotes: 1

OriolBG
OriolBG

Reputation: 2037

The easiest way I see is with an union of both queries:

SELECT * FROM Source1 WHERE Column2 IS NOT NULL
UNION
SELECT S2.* FROM Source1 S1
INNER JOIN Source2 S2
ON S1.Column1 = S2.Column1
WHERE S1.Column2 IS NULL

Upvotes: 1

Related Questions