CODEWITHSUNDEEP
mysqlsql
user1930226
user1930226

Reputation: 157

Combining three tables to only deal with one

I have these three tables in SQL for a game:

user_stats with primary key id

| userid | kills | deaths |
|--------|-------|--------|
|   10   |   5   |   4    |

user_items (the items the user has bought) with regular integer primary key id - when the user buys a new item, a row is added

| id | userid | itemid |
|----|--------|--------|
|  1 |   10   |   1    | 
|  2 |   10   |   2    | 
|  3 |   10   |   3    |

user_details (the items the user has equipped) with primary key userid

| userid | item1 | item2 |
|--------|-------|-------|
|   10   |   1   |   3   |

I only want to deal with one table, but I need all information when the user starts. I'd like to combine the three tables into one, but user_items has multiple rows for 1 user, because you can buy multiple items. Being a total stranger to SQL, how do I go about this?

Upvotes: 0

Views: 34

Answers (1)

Rahul
Rahul

Reputation: 77926

Do a join on all three tables and get the specific data you want like below

select us.*
from user_stats us
inner join user_item ui on us.userid = ui.userid
inner join user_details ud on us.userid = ud.userid

Since all the tables have a common column userid you can as well use NATURAL JOIN

select *
from user_stats
natural join user_item
natural join user_details

Upvotes: 2

Related Questions