CODEWITHSUNDEEP
sqlsql-serverunion
Nol
Nol

Reputation: 364

How to use a UNION of two tables as a Column in another Table?

I have two tables:

I need a table that needs to reference both of these with a Foreign Key in another table where either can be a valid input.
How can I do this? Or is there a better practice for doing something like this?

My current method is just storing a vChar of up to 3 and then searching a View of the two other tables.

"this question is less about the "union" keyword and more about how to have a foreign key on a column that points to TWO tables. Something that's not possible. So, OP wants to know what is the best practice for doing something "like" this."
As aquinas said, this is exactly correct.

More on what I'm trying to do
Since I can't figure out how to format a table here's an example of what I want to do with the data:

        Table 1
------------------------
StateCodes  Desc 
------------------------
 AZ         Arizona
 CA         California
 NY         New York 
 etc        etc

        Table 2
------------------------
 Char3Codes  Desc 
------------------------
 JUR        Jurusalem
 ABP        A Black Panda
 QRM        QR Monument 
 etc        etc

        Table 3
------------------------
 ComboColumn No Desc Column
------------------------
    AZ         
    CA        
    NY        
   JUR
   ABP
   QRM

Then with Table3 I should be able to quickly get the Description from the original two tables.

Upvotes: 0

Views: 183

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want speed, create a view and then create an index on it:

create view v_allcodes with shemabinding as
    select StateCode as code, Description
    from table1
    union all
    select Char3Code, Description
    from table2;

create index idx_v_allcodes_code_description on v_allcodes(code, description);

Then, simply do:

select t3.*, c.description
from table3 t3 join
     v_allcodes c
     on t3.combocode = c.code;

The index on the view automatically stays up-to-date when the underlying data changes -- that is the "magic" of materialized views. It is then as fast as using a table.

Upvotes: 1

Hogan
Hogan

Reputation: 70513

If only one can be valid input at a time using one column works. I'd do the joins like this using coalesce and not use a union.

  SELECT COALESCE(J1.VALUE, J2.VALUE) as Lookup
  FROM TABLE T
  LEFT JOIN STATES J1 ON T.FKEY = J1.CODE
  LEFT JOIN OTHERTABLE J2 ON T.FKEY = J2.CODE

Of course you can do the same thing with two keys (which I think is the better solution) This improves your design by having better separation of concerns:

  SELECT COALESCE(J1.VALUE, J2.VALUE) as Lookup
  FROM TABLE T
  LEFT JOIN STATES J1 ON T.FKEY1 = J1.CODE
  LEFT JOIN OTHERTABLE J2 ON T.FKEY2 = J2.CODE

Upvotes: 3

Related Questions