CODEWITHSUNDEEP
mysqlsqljoin
townie
townie

Reputation: 787

How do I combine these Selects/Joins?

So I've been looking in to doing this and have come up with I guess it is the basics of what I am wanting to do. I have four tables. One is just the table with the user info which will say "this is the user you are getting info for." The other three have the actual information I need to pull together.

table_1 has the info on which user. So the user id.

table_2 has a title and description unique to the user in relation to the contents of table_3. table_3 has all the basic info. Any user could have the same row in table_3. table_3 says "this is the content" and table_2 says "This is how this specific user has a relation to table_3.

table_4 is groups of table_2 rows.

So A user can say I want this picture, video, whatever in the "Cool stuff" group. Once all the info is added to the database you will get the basic content info (table_3) inside the group the user wanted it in (table_4) and it will be the users specific content because the will have their added stuff with table_2.

Actually getting all the info into the database is easy. Pulling it out and putting it together is where I am getting confused. Each of the ON parts is related. My DB is innodb and I have set foreign keys so for example on the first SELECT table_2.userId is a foreign key of table_1.id.

And what I want at the end is I guess an array of title, description, link, groupName. I am using php with mysql but I think I could figure out what I need to do on that end if I can actually get the right query or queries I need.

If any more info is needed, please let me know so I can make things more clear or whatever.

SELECT tile, description
FROM table_2
INNER JOIN table_1
ON table_2.userId = table_1.id

SELECT link
FROM table_3
INNER JOIN table_2
ON table_3.id = table_2.table_3Id

SELECT groupName
FROM table_4
INNER JOIN table_2
ON table_4.id = table_2.table_4Id

Upvotes: 0

Views: 71

Answers (2)

Robert Harvey
Robert Harvey

Reputation: 180787

Combine your queries into a single query with multiple joins.

Something like this:

SELECT tile, description, link, groupName
FROM table_2
INNER JOIN table_1  
ON table_2.userId = table_1.id
INNER JOIN table_2   
ON table_3.id = table_2.table_3Id    
INNER JOIN table_2   
ON table_4.id = table_2.table_4Id

Upvotes: 1

AndreKR
AndreKR

Reputation: 33678

SELECT tile, description, link, groupName
FROM table_2
JOIN table_1 ON table_2.userId = table_1.id
JOIN table_3 ON table_3.id = table_2.table_3Id
JOIN table_4 ON table_4.id = table_2.table_4Id

Upvotes: 1

Related Questions