The best way to connect several tables? - case

SQL
1

Since I have some problems with that, I’d like to ask about your opinions. Please have a look at attached picture. The base table (say these are views) it’s the last one with “pubdate”. So, the question is how to connect all these tables in order to produce the result based on WHERE language = statement? Shall I use views, subqueries, cte ??? All of these tables are coded as below schema (taken from “content” output). featured_img = file_id.

update: I’ve managed to do this but I had to use a compound condition JOIN ..... ON core.post_id = content.post_id AND category.language_id = content.language_id
2 times. Is it ok, or maybe my database should be improved somehow?

SELECT * 
FROM (
    SELECT ptr.post_id, ptr.language_id,
    JSON_OBJECT('slug',ptr.slug, 'title',ptr.title) AS content_OBJECT
    FROM post_trans AS ptr
    GROUP BY ptr.post_id, ptr.language_id) AS content;

pst
pst518×658 136 KB

2

To be blunt, this is not ok.

You are using a relational database yet your primary data type is JSON. Why? because you can or because it is convenient? If so, then you really should take a course in a relational database or read at least one book on this subject. I believe Mosh’s course will be sufficient. But if you opt for a book, “SQL in 10 Minutes, Sams Teach Yourself” or “SQL Database Programming by Fehily, Chris” is a good start.

I say this because it somewhat pains me to see this and I don’t have an easy and sweet answer to fix this other than recommending you to another resource. If you are going to use RDBMS, learn database normalization and design your models around it. Or learn MongoDB if what you are doing now makes more sense to you.

If you decide to store something in JSON type in RDBMS, you really have to have a strong reason to do so.

1 Like
3

Thanks for the criticism. I don’t store json in the database at all. What you see (please have a look at a piece of code) these are results of subqueries based on data that are normalized.

4

That’s great! what a relief. So, the first 4 are views you created and only the last one is the table. It wasn’t apparently clear to me those were views.

If your actual tables are normalized and you are constructing JSON using them. I don’t see any problem. As long as, you are consuming that JSON and not mapping it to some other object in your application.

5

HI, would like to help but need some clarification. You show 5 tables/views in your image, no table names, no relationships and they all have “posi_id” field and you ask how you join these tables to get the result of the SELECT statement from table post_trans? There are two tables with fields post_id and language_id. Which table is post_trans? No table/view displayed show fields slug, title.

You say you managed to “JOIN … ON core.post_id = content.post_id AND category.language_id = content.language_id”. One minor point here is you can use USING (post_id) and USING(language_id) in your joins since both related sets of tables have the same name.

It would be helpful to provide table/view names and schemas and show an example of what you would like the result to look like.

6

Thank kavfam for your input. I haven’t provided schemas since there are 10+ tables. This is not a problem any longer for me. I’m not 100% sure but seems a compound join is necessary in my case (I’m connecting post_id and language_id).