Post reply

In Both Tables, In Table #1 But Not Table #2, In Table #2 But Not Table#1

  • August 20, 2020 at 2:35 pm

    #3781977

    I've two tables and each has a key set.  Each table has a user first name and a user last name and a 'key' which is lastname + firstname.  I know that isn't ideal, but given the underlying data, that is the best that can be done.

    I'd like to show a report that shows the user name and then 3 fields that are boolean such:

    name, inBoth inA, inB

    I can imagine sloppy ways to accomplish this but I have to think I'm missing out on an elegant solution somewhere.  My first thought is to create a table variable that holds all the unique names then just do subqueries off that to fill in the true/false values.  Is there a better way?

  • August 20, 2020 at 4:10 pm

    #3781996

    1. You can use 3 operators to accomplish this.

      1. Intersect operator to get names in both
      2. A except B to get names in A only
      3. B except A to get names in B only

    2. Or you can use LEFT OUTER JOIN on key columns and use where clause to get three sets of data.

    Regards
    VG

  • August 20, 2020 at 4:43 pm

    #3782010

    Or a CASE. I think this is what you want

    CREATE TABLE A
    (   fullname  VARCHAR(100) NOT NULL CONSTRAINT aPk PRIMARY KEY
      , firstname VARCHAR(50)
      , lastname  VARCHAR(50));
    GO
    INSERT dbo.A
        (fullname, firstname, lastname)
    VALUES
        ('Steve Jones', 'Steve', 'Jones'),
        ('Bobby Jones', 'Bobby', 'Jones'),
        ('Ed Jones', 'Ed', 'Jones')

    CREATE TABLE B
    (   fullname  VARCHAR(100) NOT NULL CONSTRAINT bPk PRIMARY KEY
      , firstname VARCHAR(50)
      , lastname  VARCHAR(50));
    GO
    INSERT dbo.b
        (fullname, firstname, lastname)
    VALUES
        ('Steve Jones', 'Steve', 'Jones'),
        ('Billy Jones', 'Billy', 'Jones'),
        ('Sally Jones', 'Sally', 'Jones')
    GO

    SELECT CASE WHEN a.fullname IS NOT NULL THEN a.fullname ELSE b.fullname END
    AS FullName,
    CASE WHEN a.fullname IS NOT NULL AND b.fullname IS NOT NULL THEN 1 ELSE 0 END AS inBoth,
    CASE WHEN a.fullname IS NOT NULL AND b.fullname IS NULL THEN 1 ELSE 0 END AS inA,
    CASE WHEN a.fullname IS NULL AND b.fullname IS NOT NULL THEN 1 ELSE 0 END AS inB
    FROM
           a FULL OUTER JOIN b ON a.fullname = b.fullname;

  • August 21, 2020 at 3:16 pm

    #3782329

    Many thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply