1

EDIT: Is there a way or any methods/algorithms to find path of joins from one table to another?

Database tables example

Example:

I want to select first_name from person, name from country where country is Slovakia. User will only choose fields that he wants to select and I need some kind of algorithm that will find out that I need to join from person to city to country.

Vahanito
  • 11
  • 1
  • 2
  • 1
    This is basic SQL as far as I can see. Yes join all 3 of the tables. With the `join` ... `on` ... syntax it is pretty simple (instead of cross joining with comma and filtering in the where clause, although it would work too). – maraca Dec 03 '17 at 11:32
  • But, I want to create this programmatically. Yes it is pretty easy to do it yourself, but I want it to be universal and dynamic for any tables. – Vahanito Dec 03 '17 at 11:39
  • Why? Databases are optimized for those kind of queries. Can't you just dynamically replace the table and column names? Or is the real question which tables have to be joined? Something like a breadth first search over the foreign keys? – maraca Dec 03 '17 at 12:07
  • Which tables have to be joined, thats the question – Vahanito Dec 03 '17 at 12:48
  • https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server this looks useful, but it will only work if all foreign keys are there, you can also omit them and queries still work the same... which happens way too often in practice. – maraca Dec 03 '17 at 14:30
  • Thanks, I think I know a way how to do this now. – Vahanito Dec 03 '17 at 18:01

1 Answers1

0

Try this query,

Select first_name from person p, city c, country cn where p.city_id = c.city_id and c.country_id = cn.country_id and cn.name = 'Slovakia'
Santosh
  • 874
  • 11
  • 21