1

Using SQL server, I have a Table that can join with more than one table using the same column. This table has two columns, SourceType and SourceID. SourceType is the table to join with and SourceID is the primary key of the table we join to. This produces queries like this:

select * 
from MyTable join TableOne
where MyTable.SourceId = TableOne.ID
   and MyTable.SourceType = 'TableOne';

select * 
from MyTable join TableTwo
where MyTable.SourceId = TableOne.ID
   and MyTable.SourceType = 'TableTwo';

I need to do some research on this. What is this approach called?

elizabk
  • 480
  • 2
  • 11
Don Chambers
  • 3,798
  • 9
  • 33
  • 74
  • 1
    not sure what your question actually is. Your join statements quite looks like a mix of [old fashioned join](https://blog.sqlauthority.com/2015/10/08/sql-server-why-should-you-not-to-use-old-style-join/) *(no `join`, only two tables in `from` statement and a `where` statement)* and an actual join. Not only are the queries weird to read, but they probably don't actually do what we would think they would – Sirmyself Feb 27 '19 at 19:20
  • And what is the question? – Eric Feb 27 '19 at 20:36
  • Also probably Google how to use `JOIN` properly. – Eric Feb 27 '19 at 20:37
  • I was just asking what the method was called and it's been answered. I know how to join - this was just some fast typing to illustrate the concept. – Don Chambers Mar 01 '19 at 00:25

2 Answers2

3

If I understand correctly, you are trying to use one column to reference the primary keys of 2 different tables. I believe the approach is called polymorphic associations. The concept is valid but using your solution to implement it is not the best way. Here's some other ways to do it.

elizabk
  • 480
  • 2
  • 11
  • Your answer is what I am looking for - the name of the approach. Unfortunately, I am stuck with the second option in the post you linked to. The tables already exist and I can't change them. – Don Chambers Mar 01 '19 at 00:24
0

I think you want something like this:

Select *
FROM Mytable AS myt
RIGHT JOIN TableOne AS tb1 ON myt.SourceId = tb1.ID

SELECT *
FROM MyTable AS myt
RIGHT JOIN TableTwo AS tb2 ON myt.SourceId = tb2.ID

More details you can find here: https://www.w3schools.com/sql/sql_join.asp

Alexandru-Codrin Panaite
  • 574
  • 6
  • 9