I have a table that looks like this. Is there a way to construct a query / join that gets the row with a primary key that matches

document_id

in a table that matches the

document_type

?

Hello @benten! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.

Essentially I'm asking if there is a way to conduct a join without there being a foreign key relationship

I think the bigger issue is you want to join with a table that is named based on a column, if I understand... If so, this is one of many post on how to approach that and warns of not trying to do that in a single query. https://stackoverflow.com/questions/20469687/dynamic-table-name-in-postgresql-9-3 If you are trying to do this from the client it is a non starter and just do two selects.

benten (2022-05-07)

Hmm, what do you mean by two selects?

You fetch the row you show, then use that data to fetch from the table in your column name the row with the id.

Oh I see

By splitting up your document types into separate tables you are making it difficult to handle cleanly. But almost anything can be done with functions on Postgres side, but gets messy.

Right but I'm not sure it would have been realistic to have a single table for all types of documents

Depends on what you need to do with the data. Normally a jsonb column can be quite flexible to handle various differences. I use a single table for 5 note types with a few common columns and the rest in jsonb column. If your tables are limited and fixed then you can certainly do several selects to each of them to get your data the rest would return nothing or null.

So you're saying to chain selects?

This would all have to be done on the server and likely involve functions. I fear it will be slow also querying all the tables. Or you can use the example link I posted for a dynamic query, or google more on that. If you want to tackle it in sql then you probably should ask in the sql thread and hope someone more versed in sql can help.

Ok thanks for your help 🙂