0

Is there a method to automatically join tables that have primary to foreign relationship rather then designate joining on those values?

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
brddawg
  • 434
  • 8
  • 19
  • 1
    You mean you want to write something like `SELECT * FROM a AUTO JOIN b` and the database will use an already defined relationship between a and b to create the join? Closest thing I can think of is oracle's NATURAL JOIN where it auto joins the tables on columns that are named identically. Please don't tag a question with every RDBMS under the sun if it's about a specific RDBMS – Caius Jard Oct 05 '17 at 15:28
  • You could write a script that parses the table constraints and derives the candidate key / foreign key relationship, then dynamically build your join based on that. – nicomp Oct 05 '17 at 15:31
  • @CaiusJard outside of migrating my data to oracle this won't work – brddawg Oct 05 '17 at 15:31
  • Well, the oracle version doesn't use a defined relationship, it just looks at the column names.. I know of no RDBMS that does use a defined relationship – Caius Jard Oct 05 '17 at 15:32
  • @brddawg my way will work and you don't migrate any data – nicomp Oct 05 '17 at 15:34
  • @nicomp god bless python. I'm going to search github first then write that tomorrow. It's just surprising that after respecting all architectural rules there is no way to capitalize on that effort in my programming. – brddawg Oct 05 '17 at 15:38
  • see my just-added answer before you go and reinvent the wheel :) – Caius Jard Oct 05 '17 at 15:41

3 Answers3

2

The out and out answer is "no" - no RDBMS I know of will allow you to get away with not specifying columns in an ON clause intended to join two tables in a non-cartesian fashion, but it might not matter...

...because typically multi tier applications these days are built with data access libraries that DO take into account the relationships defined in a database. Picking on something like entity framework, if your database exists already, then you can scaffold a context in EF from it, and it will make a set of objects that obey the relationships in the frontend code side of things

Technically, you'll never write an ON clause yourself, because if you say something to EF like:

context.Customers.Find(c => c.id = 1) //this finds a customer
  .Orders //this gets all the customer's orders
    .Where(o => o.date> DateTIme.UtcNow.AddMonths(-1)); //this filters the orders

You've got all the orders raised by customer id 1 in the last month, without writing a single ON clause yourself... EF has, behind the scenes, written it but in the spirit of your question where there are tables related by relation, we've used a framework that uses that relation to relate the data for the purposes thtat the frontend put it to.. All you have to do is use the data access library that does this, if you have an aversion to writing ON clauses yourself :)

It's a virtual certaintythat there will be some similar ORM/mapping/data access library for your front end language of choice - I just picked on EF in C# because it's what I know. If you're after scouting out what's out there, google for {language of choice} ORM (if you're using an OO language) - you mentioned python,. seems SQLAlchemy is a popular one (but note, SO answers are not for recommending particular softwares)

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • thank you - this does answer my question plus a little direction on how to work around. I was tempted to add a python tag to my original question to find something closer to my application. that being said, I can't mark this as the answer without testing myself and I don't have anything built to do so. – brddawg Oct 05 '17 at 15:55
0

If you mean can you write a JOIN at query time that doesn't need an ON clause, then no.

There is no way to do this in SQL Server.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Just being an awful pedant for the benefit of the world community, this answer isn't true 100% of the time as SQL server supports CROSS JOIN and non ansi `SELECT * FROM a,b` that are joins, but have no ON clause :) – Caius Jard Oct 05 '17 at 15:31
  • That thought occurred to me as I was writing, but I decided that in the context of the question, the meaning was clear enough. : ) – Tab Alleman Oct 05 '17 at 15:32
0

I am not sure if you are aware of dbForge; it may help. It recognises joinable tables automatically in following cases:

  1. The database contains information that specifies that the tables are related.
  2. If two columns, one in each table, have the same name and data type.
  3. Forge Studio detects that a search condition (e.g. the WHERE clause) is actually a join condition.
Shaswar Baban
  • 21
  • 4