### Thread: Complex UNION, or not?

1. #### 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?

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 03:48 PM.
2. 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 */```
3. 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.
4. 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?
5. 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 06:08 PM.
6. 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 )```
7. 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 01:34 AM.
8. ambiguous? not really... it's a correlated subquery

you can do it your way if you want, though