11

I'm wondering if this its even posible.

I want to join 2 tables based on the data of table 1. Example table 1 has column food with its data beeing "hotdog".

And I have a table called hotdog.

IS it possible to do a JOIN like.

SELECT * FROM table1 t join t.food on id = foodid

I know it doesnt work but, its even posible, is there a work arround?.

Thanks in advance.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user1052347
  • 113
  • 1
  • 1
  • 4
  • 1
    this seems like more of a design problem - how will you ever enforce referential integrity if you don't know your joins until runtime? – tomfumb Nov 17 '11 at 17:37

2 Answers2

13

No, you can't join to a different table per row in table1, not even with dynamic SQL as @Cade Roux suggests.

You could join to the hotdog table for rows where food is 'hotdog' and join to other tables for other specific values of food.

SELECT * FROM table1 JOIN hotdog ON id = foodid WHERE food = 'hotdog'
UNION
SELECT * FROM table1 JOIN apples ON id = foodid WHERE food = 'apples'
UNION
SELECT * FROM table1 JOIN soups  ON id = foodid WHERE food = 'soup'
UNION 
...

This requires that you know all the distinct values of food, and that all the respective food tables have compatible columns so you can UNION them together.

What you're doing is called polymorphic associations. That is, the foreign key in table1 references rows in multiple "parent" tables, depending on the value in another column of table1. This is a common design mistake of relational database programmers.

For alternative solutions, see my answers to:

I also cover solutions for polymorphic associations in my presentation Practical Object Oriented Models In SQL, and in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Only with dynamic SQL. It is also possible to left join many different tables and use CASE based on type, but the tables would be all have to be known in advance.

It would be easier to recommend an appropriate design if we knew more about what you are trying to achieve, what your design currently looks like and why you've chosen that particular table design in the first place.

--    Say you have a table of foods:
    
    id INT
    foodtype VARCHAR(50) (right now it just contains 'hotdog' or 'hamburger')
    name VARCHAR(50)
    
--    Then hotdogs:
    
    id INT
    length INT
    width INT
    
--    Then hamburgers:
    
    id INT
    radius INT
    thickness INT

Normally I would recommend some system for constraining only one auxiliary table to exist, but for simplicity, I'm leaving that out.

SELECT f.*, hd.length, hd.width, hb.radius, hb.thickness
FROM foods f
LEFT JOIN hotdogs hd
    ON hd.id = f.id
    AND f.foodtype = 'hotdog'
LEFT JOIN hamburgers hb
    ON hb.id = f.id
    AND f.foodtype = 'hamburger'

Now you will see that such a thing can be code generated (or even for a very slow prototype dynamic SQL on the fly) from SELECT DISTINCT foodtype FROM foods given certain assumptions about table names and access to the table metadata.

The problem is that ultimately whoever consumes the result of this query will have to be aware of new columns showing up whenever a new table is added.

So the question moves back to your client/consumer of the data - how is it going to handle the different types? And what does it mean for different types to be in the same set? And if it needs to be aware of the different types, what's the drawback of just writing different queries for each type or changing a manual query when new types are added given the relative impact of such a change anyway?

nomadSK25
  • 2,350
  • 3
  • 25
  • 36
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Thnks for the replay.... I like the union solution thought im not sure hot to implement it. So heres the detail. Student makes a request on some tipe of letter, on table "request", the data of the student and the "letter type" are saved. Theres 10 different posible values for the "letter type". So I have 10 tables with the specific letter type name, like.. Table recomendation, Table transfer, So I wanted A way to do all the SELECT on 1 query Based on the student letter, to call the specific table and get the data from it. – user1052347 Nov 17 '11 at 19:09
  • @user1052347 Bill Karwin gives several links to designs. Ultimately, because the associated data for each type is different, an actual "SELECT *" is not likely to work, since the auxiliary information in each of the tables is going to be different. You need to decide what you want the result set to look like. At that point, you can code by hand (or even potentially code generate/dynamic SQL - thus somewhat eliminating the maintenance problems with changing schemas). I will give an example in my answer. – Cade Roux Nov 17 '11 at 20:26