1

I have a mySQL table with a reference set of data in it. I have another table with data in it that describes updates or additional entries to this reference data. For various reasons I won't get into, it's important that the reference data stay unaltered as specific users come along and create records (hence the second table). The schema between these tables is identical with the exception of the "custom" table having a 2-key composite primary key. One of those columns is shared between the "custom" and reference tables.

The business logic I want to execute in a single SQL statement on these tables is as follows:

If records exist in the "custom" table with the same key value as what's in the reference table, then take the "custom". Otherwise, take the reference value.

I thought maybe I could use a RIGHT JOIN, or perhaps even some twist on the UNION operator to control what was coming back. I keep scrambling up the logic of the SQL though. The JOIN is going to fetch info from one table to join with the other, which I'm really not looking for. The UNION will simply remove duplicates between tables....which is close.

Something like:

SELECT * FROM custom UNION SELECT * FROM reference

Except this will simply drop duplicates if all columns are the same. If any is different (which there will be since that's why the custom entry exists), then the UNION does nothing but munge both tables' contents together.

Am I going about this all wrong?

Thanks for any insights you can provide.

Improve this question

3 Answers 3

Reset to default
3

There are a few ways to do this. Here is a union that should get what you need, at least according to my interpretation of your situation:

select KeyCol, Col2, Col3
from CustomTable
union all
select KeyCol, Col2, Col3
from RefTable
where KeyCol not in (select KeyCol from CustomTable)

I'm assuming your RefTable and CustomTable have a one-to-zero-or-one relationship, as in, you don't have multiple Custom records for any Ref (but this will still work either way).

Update:

You might also want your extra key column from Custom:

select KeyCol, Col2, Col3, CustomKey
from CustomTable
union all
select KeyCol, Col2, Col3, null as CustomKey
from RefTable
where KeyCol not in (select KeyCol from CustomTable)
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

So that grabs all records from both tables, then in the where clause attempts to filter out the records in the result set that have their key existing in the custom table. Right? Doesn't that mean the final result set will be missing any records whose key value exists in the custom table?
Wellll, sort of. My custom table has an additional primary key column which means technically there could be multiple records in custom with the same keyVal as one record in the reference table.
The where clause is applicable to each individual select, not the entire unioned query. An order by could be at the end for the whole thing, though.
Ah, so the WHERE is transitive. Got it.
So I'm thinking the only change here is to add WHERE Project = 'ProjectX' in your subquery.
|
1

Sounds like a left outer join with COALESCE() of the right and left values.

SELECT COALESCE(c.val, r.val) as val, ...
FROM reference as r LEFT OUTER JOIN custom as c ON c.ID = r.ID AND c.Project = 'ProjectX'
Improve this answer

11 Comments

My SQL-foo is modest. I think you just caused my brain to explode. I'll start googling.
I re-read your question and I don't understand how your tables are related since you mention a two-part key that's only in the custom table. I also see your confusion between joins and unions. Lossely, joins combine tables horizontally and unions combine vertically.
There are many users who have "projects" working on this data. The custom table holds the entries of all updated/new records of the reference data. Hence the composite key of ID, Project. The reference table has no project column.
I changed the query so that it matches on the custom table based on ID while also looking for a specific project. I think that's what you described. The UNION approach may feel a little cleaner to you because you don't end up need the series of COALESCE() expressions.
Little fuzzy still on the coalesce usage. What terms do you enter in the coalesce-ing? Why create the variable "val" in the SQL statement? Doesn't Coalesce just grab the first non-null? In my case all fields could be populated in both tables...I just need the reference record excluded from the result set. Apologies if I'm making this extra tedious.
|
0

With attribution to @TimLehner, what you probably want is a slight modification of his query. My guess is that you want the most recent of the customer modifications. I'll further guess that this has the maximum custom key.

To get this, you need another clause on the query:

select KeyCol, Col2, Col3, CustomKey
from CustomTable ct join
     (select max(CustomKey) as maxCustomKey from CustomTable group by KeyCol, Col2 Col3
     ) lastkey
     on ct.KeyCol = lastkey.KeyCol and ct.Col2 = lastkey.Col2 and ct.Col3 = lastkey.Col3
union all
select KeyCol, Col2, Col3, null as CustomKey
from RefTable
where KeyCol not in (select KeyCol from CustomTable)

By the way, in most other databases you would do this using a simpler query and a function called row_number(), but mysql does not support this type of function.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.