I have a table whose matching type of clothes and the name of the model for one owner. The table is given by a software I work with, I can't change the design of my model.
The owner of this wardrobe is Paul who has poa as a personal ID
| ID | owner_id | cid | Name | Type | Date_own_from | Date_own_to |
|---|---|---|---|---|---|---|
| 1 | poa | sho-1 | The dragon | Shoes | 01-01-2021 | 01-10-2021 |
| 2 | poa | shi-1 | SimpleT | Shirt | 01-01-2021 | 31-12-2099 |
| 3 | poa | so-21 | White model | Sockets | 01-01-2021 | 31-12-2099 |
| 4 | poa | sho-3 | Snake speed 3 | Shoes | 01-10-2021 | 31-12-2099 |
An Owner can only own one pair of each type of clothes. If Date_own_to is 31-12-2099 it means that the owner didn't bring the clothe back.
Each type correspond of one table which has more information about the product
Here is an example for Shoes :
| ID | Clothes_ID | Size |
|---|---|---|
| 1 | sho-1 | 42 |
Here is for Shirts :
| ID | Clothes_ID | Size |
|---|---|---|
| 1 | shi-1 | M |
And for Sockets :
| ID | Clothes_ID | Size |
|---|---|---|
| 1 | so-21 | M |
What I'm trying to do is to list every clothes for each person like this :
| ID | Owner | Shoes | Shirt | Sockets |
|---|---|---|---|---|
| 1 | poa | SnakeSpeed | SimpleT | White model |
So I tried to do this :
SELECT * FROM wardrobe w
JOIN shoes sho
on sho.clothes_id = w.cid
where owner_id = 'poa'
With this request I got this :
| ID | Owner | Shoes |
|---|---|---|
| 1 | poa | SnakeSpeed |
But now when I try to join an other table table with this request
SELECT * FROM wardrobe w
JOIN shoes sho
on sho.clothes_id = w.cid
JOIN shirt shi
on shi.clothes_id = w.cid
where owner_id = 'poa'
And the result of this is 0 rows.
I don't know how to properly join the values.
What I am doing wrong?
Multiple Rows
Since I only have one t-shirt owned for one period. If I want to look for the clothes owned by a person for a given period I would like to do something like this :
date_own_from = '01-01-2021' and date_own_to = '31-12-2021'
So the desired results would be :
| ID | Name | Shoes | Shirt | Sockets |
|---|---|---|---|---|
| 1 | Paul | SnakeSpeed | SimpleT | White model |
| 2 | Paul | The Dragon | SimpleT | White model |
A row 2 differents shoes owned during the year with the Shirt and the Socket owned when the persons owned the shoes.