CODEWITHSUNDEEP
sqlpostgresqldatabase-designjoinnaming-conventions
van_folmert
van_folmert

Reputation: 4517

Display table where foreign keys are values from second column of referenced tables

Is it possible to write such a universal query? By universal I mean that I won't need to type column names that are foreigns keys, so that I can use this query for other tables. Simple example:

[table workers]
ID [PK] | worker | department [FK]
--------+--------+-----------
1       | Smith  | 2

[table departments]
ID [PK] | department
--------+------------+
1       | Paris      |
2       | Oslo       |

After SELECT/JOIN query it would be displayed as:

ID [PK] | worker | department [FK]
--------+--------+-----------
1       | Smith  | Oslo

But instead of writing:

SELECT workers.ID, workers.worker, departments.department
FROM workers JOIN departments ON workers.department = departments.ID;

which would work only for workers and departments I'd prefer something more universal like:

SELECT * FROM [tableName] JOIN [referenced table#1], 
[referenced table#2]...[#n] 
ON [tableName].FK#1 = [referenced table#1].[2nd column], 
[tableName].FK#2 = [referenced table#2].[2nd column]...#n

where #n - number of foreign keys and their referenced tables which varies for every table.

Upvotes: 1

Views: 4633

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658352

Not exactly, at least not without a function using sophisticated dynamic SQL.

But with a proper naming convention, you can simplify things along these lines:

[table worker]
worker_id [PK] | worker | department_id [FK]
----------------+--------+-----------
1               | Smith  | 2
[table department]
department_id [PK] | department
---------------------+------------
1                    | Paris
2                    | Oslo

Using id as column name is generally a very bad idea. It is not descriptive. Unfortunately, some ORMs spread this anti-pattern.

Now, if only columns that are supposed to, to the left and right of a JOIN, have matching names then you could employ a rarely used standard-SQL feature: NATURAL JOIN:

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.

Makes for very simple code:

SELECT *
FROM   worker
NATURAL JOIN department

I hardly ever use this, though. It can become a trap if you change table columns later and introduce unintended matches. If that can't happen in your case, go for it.

At least you can use the simpler JOIN syntax with USING:

SELECT *
FROM   worker     
JOIN   department USING (department_id)

There was a closely related question on dba.SE recently.

Upvotes: 4

Related Questions