SQL JOINs - from Zero to Many-to-Many JOINs!

[@Don_Data]

Hi friend,
I hope this video helps you improve your understanding of SQL Joins. Do you have any questions?

[@عليالعصفوري-ط4ض]

Can i fetch tables as nested arrays directly or if i must do some loops and conditions in programming language

[@Don_Data]

Hi @@عليالعصفوري-ط4ض ,
I am not 100% sure I understand your question correctly. You can definitely loop over tables, which would be the SQL equivalent to arrays from programming.
Yiu could either use CURSORs to loop over tables, or WHILE loops which fetch one row per loop.
Conditions can be set using IF... THEN.... ELSE.... END.
In general, while possible, in SQL you try to avoid loops and rather use so called set operations, which perform query logic over the whole table at once instead of row by row logic for performance reasons. Does this answer your question?
Best wishes,
Henning aka Don Data

[@عليالعصفوري-ط4ض]

@Don_Data
My question is in real world app
Like social media app if iwhat to send data like posts: {
Id:1,
Content : content ,
User:{
Id:1,
Name: jack
}
}
For all posts table with each user
I fetch all posts the loops by it and fetch every user where primary = forigen
Or i can fetch left join like this structur in some how

[@Don_Data]

Hi @عليالعصفوري-ط4ض,
if I understand correctly, you have two tables, Posts and Users in a relational database and want to expose the data as .json? The part of generating the json structure aside, to fetch the data you could query:
SELECT p.Id AS PostId, p.Content, u.Name AS UserName
FROM Posts p
INNER JOIN Users u ON u.Id = p.UserId
But I am not 100% sure if I understood you correctly. Without know the data structure on the database side, it is difficult to tell.
Hope this helps to point you in the right direction.

[@عليالعصفوري-ط4ض]

@@Don_Data yes you under stand correctly if the query as you writing it will fetch as array of mixed data or left join row
But i want the user data to be in sub array called user
What i did was left join then fetch first table from db then fecth second table from db
Then for loop push first of users in first of posts [user] ,
second in second... etc.
Can i have finall result directly from data db without loop

[@Don_Data]

Hi @عليالعصفوري-ط4ض,
yes, you can create JSON string from SQL queries in most database engine. I have create a browser executable query. You can open it via the following link using T-SQL (e.g. Microsoft SQL Server).
sqlfiddle.com/sql-server/online-compiler?id=542776f0-ff9f-42fa-8bbe-5e55304a092f
If the link does not work, here is the code:
SELECT 'Post 1' AS Id, 'First post of User 1' AS Content, 'User 1' As Userid
INTO #Posts
UNION
SELECT 'Post 2' AS Id, 'Second post of User 1' AS Content, 'User 1' As Userid
UNION
SELECT 'Post 3' AS Id, 'First post of User 2' AS Content, 'User 2' As Userid
UNION
SELECT 'Post 4' AS Id, 'Second post of User 2' AS Content, 'User 2' As Userid
SELECT 'User 1' AS Id, 'First User Name' AS Name
INTO #Users
UNION
SELECT 'User 2' AS Id, 'Second User Name' AS Name
SELECT
p.Id AS Postid,
p.Content,
u.Id AS UserId,
u.Name
FROM #Posts p
INNER JOIN #Users u ON u.id = p.UserId
FOR JSON AUTO;
I hope this brings you on the right track and you can avoid the loops,
Henning