#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    218
    Rep Power
    17

    Complex UNION, or not?


    Suppose I have three tables (or more, but for sure more than two)...

    Each table has a key, let's say 'Puid', that is a key, and there is a "master" table that holds the one-to-one relationship.

    Is Puid=x in Table A, B, or C? No? Delete Puid=x form Master table. In the case I'm thinking of here, if neither Tables A, B, and C, contained a record with a Puid also in the Master table, the query would return no rows.

    I'm thinking UNION of SELECT statements, but I'm not sure how to do this beyond a UNION with one table.

    Or maybe a compound SELECT?

    Ideas?

    [Edit]

    Or how about this:

    Code:
    SELECT Master.Puid
    FROM Master
    JOIN TableA ON TableA.Puid = Master.Puid
    JOIN TableB ON LZ_TableB.Puid = Master.Puid
    JOIN TableC ON TableC.Puid = Master.Puid
    Last edited by Arty Zifferelli; January 26th, 2017 at 04:48 PM.
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    What? The master tables holds a relationship? Relationships are between two tables so one cannot "hold" it.

    If the Puid is the key in the master table then you use that in other related tables as foreign keys.

    And I don't know what UNIONs have to do with any of this, but if you want to still return records when there aren't matching rows in other tables then use a LEFT JOIN - meaning if there's no match in the JOIN condition then the query will return data from the left table (master) and NULLs from the right table (A/B/C).
    Code:
    SELECT Master.Puid, TableA.Puid AS a, LZ_TableB.Puid AS b, TableC.Puid AS c
    FROM Master
    LEFT JOIN TableA ON TableA.Puid = Master.Puid
    LEFT JOIN TableB ON LZ_TableB.Puid = Master.Puid
    LEFT JOIN TableC ON TableC.Puid = Master.Puid
    /* returns Puid, and a/b/c will be NULL if TableA/LZ_TableB/TableC does not have a corresponding row */
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    218
    Rep Power
    17
    I didn't phrase that right.

    The Puid in the Master table is a Primary Key, and the Foreign Key in the others. If NONE of the other tables have a record with a Foreign Key that matches a Master.Puid, I don't need the Master.Puid record anymore.
  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    But if at least one does then you do?

    Still use LEFT JOINs, but add a condition that one of the tables has to be non-NULL.
    Code:
    WHERE (TableA.Puid IS NOT NULL OR LZ_TableB.Puid IS NOT NULL OR TableC.Puid IS NOT NULL)
    This is starting to sound more complicated than just having three foreign tables. I mean, why do some of these tables have values and others don't? And you don't know which ones will and won't?
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    218
    Rep Power
    17
    The tables record information about (in this example three, but it could be more) distinct different activities where a specific group might do more than one. The thing that binds them together is that they are being done by the same group, and that in a sense the Primary Key. The group might do one thing or five. Or maybe we start with five and decide only three. And then we just cancel the whole thing, no more group.

    Usually a series of simple steps compound into a complex solution or state, many times interdependent with other processes. At some point, things coalesce different collections which are the different tables. Table rows may be linked in such a way that when one disappears, so does another (or not). But if all of the rows in the different tables linked to a specific Primary Key do go away, I don't need the record in the table where the Primary Key lives.

    This is a planning tool, so if we plan to do something, but the plan changes and we end up doing nothing, we don't need to keep the data in (this particular) planning tool database, archive it or anything for statistics for example, because it didn't happen. Maybe some other interest like the people in training will keep that data, but this isn't the tool for that, this assists in operational planning.

    It's a little hard to describe without going into eye-glazing details... The fun part for me was building the algorithms because I know how to do that. The database, I'm still short on knowledge. HOWEVER, I think the records and tables are actually pretty optimised.
    Last edited by Arty Zifferelli; January 28th, 2017 at 07:08 PM.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,734
    Rep Power
    4288
    Originally Posted by Arty Zifferelli
    Is Puid=x in Table A, B, or C? No? Delete Puid=x form Master table.
    test this for me, would you please...
    Code:
    DELETE 
      FROM Master
     WHERE NOT EXISTS
           ( SELECT Puid 
               FROM TableA
              WHERE Puid = Master.Puid )
       AND NOT EXISTS
           ( SELECT Puid 
               FROM TableB
              WHERE Puid = Master.Puid )
       AND NOT EXISTS
           ( SELECT Puid 
               FROM TableC
              WHERE Puid = Master.Puid )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    218
    Rep Power
    17
    That does exactly what I'm talking about.

    Although I was expecting the server to tell me that Puid was ambiguous, that
    Code:
    .... ( SELECT Puid 
               FROM TableA
              WHERE Puid = Master.Puid ) ....
    should be

    Code:
    ( SELECT TableA.Puid 
               FROM TableA
              WHERE  TableA.Puid = Master.Puid )
    Last edited by Arty Zifferelli; January 29th, 2017 at 02:34 AM.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,734
    Rep Power
    4288
    ambiguous? not really... it's a correlated subquery

    you can do it your way if you want, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo