# The best way to connect several tables? - case
**URL:** https://forum.codewithmosh.com/t/the-best-way-to-connect-several-tables-case/8063
**Category:** SQL
**Created:** [October 20, 2021, 4:12pm UTC](https://forum.codewithmosh.com/t/the-best-way-to-connect-several-tables-case/8063 "2021-10-20T16:12:32Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@GM\_sql](https://forum.codewithmosh.com/u/GM_sql)
#### Post date: [October 20, 2021, 4:12pm UTC](https://forum.codewithmosh.com/t/the-best-way-to-connect-several-tables-case/8063/1 "2021-10-20T16:12:32Z")
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?
```auto
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;
```
---
### Author:  [@some\_random\_coder](https://forum.codewithmosh.com/u/some_random_coder)
#### Post date: [October 21, 2021, 9:56pm UTC](https://forum.codewithmosh.com/t/the-best-way-to-connect-several-tables-case/8063/2 "2021-10-21T21:56:23Z")
> [@GM\_sql](#):
>
> Is it ok, or maybe my database should be improved somehow?
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.
---
### Author:  [@GM\_sql](https://forum.codewithmosh.com/u/GM_sql)
#### Post date: [October 22, 2021, 7:39am UTC](https://forum.codewithmosh.com/t/the-best-way-to-connect-several-tables-case/8063/3 "2021-10-22T07:39:12Z")
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.
---
### Author:  [@some\_random\_coder](https://forum.codewithmosh.com/u/some_random_coder)
#### Post date: [October 22, 2021, 1:54pm UTC](https://forum.codewithmosh.com/t/the-best-way-to-connect-several-tables-case/8063/4 "2021-10-22T13:54:11Z")
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.
---
### Author:  [@kavfam](https://forum.codewithmosh.com/u/kavfam)
#### Post date: [October 28, 2021, 1:18pm UTC](https://forum.codewithmosh.com/t/the-best-way-to-connect-several-tables-case/8063/5 "2021-10-28T13:18:40Z")
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.
---
### Author:  [@GM\_sql](https://forum.codewithmosh.com/u/GM_sql)
#### Post date: [October 28, 2021, 5:41pm UTC](https://forum.codewithmosh.com/t/the-best-way-to-connect-several-tables-case/8063/6 "2021-10-28T17:41:51Z")
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).