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

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 :

Here is for Shirts :

And for Sockets :

What I'm trying to do is to list every clothes for each person like this :

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 :

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 :

A row 2 differents shoes owned during the year with the Shirt and the Socket owned when the persons owned the shoes.

CodePudding user response：

not sure why you need to join any table because they are not adding anything to your results...unless you have oversimplified.

based on your results this will do.

BUT....if an owner has more than one pair of shoes etc, you need to tell us what you want to see in your results

SELECT
  owner_id AS owner,
  MAX(CASE WHEN type = 'shoes' THEN name END) AS shoes,
  MAX(CASE WHEN type = 'shirt' THEN name END) AS shirt,
  MAX(CASE WHEN type = 'sockets' THEN name END) AS sockets
FROM w
GROUP BY owner_id

CodePudding user response：

Given the poor design you have to work with you have a struggle here.

Here is sample data and ddl in case anybody wants to play along.

create table Wardrobe
(
    ID int
    , owner_id varchar(10)
    , cid varchar(10)
    , Name varchar(20)
    , Type varchar(10)
)

insert Wardrobe
select 1, 'poa', 'sho-1', 'SnakeSpeed', 'Shoes' union all
select 2, 'poa', 'shi-1', 'SimpleT', 'Shirt' union all
select 3, 'poa', 'so-21', 'White model', 'Sockets'

insert Wardrobe
select 4, 'poa', 'so-22', 'green', 'Sockets'

create table Shoes
(
    ID int
    , Clothes_ID varchar(10)
    , Size varchar(10)
)

insert Shoes
select 1, 'sho-1', '42'

create table Shirts
(
    ID int
    , Clothes_ID varchar(10)
    , Size varchar(10)
)

insert Shirts
select 1, 'shi-1', 'M'

create table Sockets
(
    ID int
    , Clothes_ID varchar(10)
    , Size varchar(10)
)

insert Sockets
select 2, 'so-22', 'M'

There are probably some other ways to deal with this. But this example will work even if they a single person has only two shoes and a single shirt with no sockets. I started by isolating each set of rows using some ctes and row_number. Then munge them back together with some conditional aggregation.

declare @Owner varchar(10) = 'poa';

with cteShoes as
(
    select w.ID
        , w.owner_id
        , w.Name
        , sho.Size
        , RowNum = ROW_NUMBER() over(order by w.Name)
    from Wardrobe w     
    join Shoes sho on sho.Clothes_ID = w.cid and w.Type = 'Shoes'
    where w.owner_id = @Owner
)
, cteShirts as
(
    select w.ID
        , w.owner_id
        , w.Name
        , shi.Size
        , RowNum = ROW_NUMBER() over(order by w.Name)
    from Wardrobe w 
    join Shirts shi on shi.Clothes_ID = w.cid and w.Type = 'Shirt'
    where w.owner_id = @Owner
)
, cteSockets as
(
    select w.ID
        , w.owner_id
        , w.Name
        , soc.Size
        , RowNum = ROW_NUMBER() over(order by w.Name)
    from Wardrobe w 
    join Sockets soc on soc.Clothes_ID = w.cid and w.Type = 'Sockets'
    where w.owner_id = @Owner
)

select Owner = coalesce(sho.owner_id, shi.owner_id, soc.owner_id)
    , Shoes = max(case when sho.RowNum is not null then sho.Name end)
    , ShoesSize = max(case when sho.RowNum is not null then sho.Size end)
    , Shirt = max(case when shi.RowNum is not null then shi.Name end)
    , ShirtSize = max(case when shi.RowNum is not null then shi.Size end)
    , Sockets = max(case when soc.RowNum is not null then soc.Name end)
    , SocketsSize = max(case when soc.RowNum is not null then soc.Size end)
from cteShoes sho
full outer join cteShirts shi on shi.RowNum = sho.RowNum
full outer join cteSockets soc on soc.RowNum = sho.RowNum
group by coalesce(sho.ID, shi.ID, soc.ID)
    , coalesce(sho.owner_id, shi.owner_id, soc.owner_id)

CodePudding user response：

Use left joins since there are no shirts for this user

SELECT w.*, shoes.*, shirt.*
FROM (SELECT distinct cid FROM wardrobe) w
LEFT JOIN (SELECT cid, clothes_id
           FROM wardrobe WHERE type ='Shoes') shoelink ON shoelink.cid = w.cid
LEFT JOIN shoes sho on sho.clothes_id = shoelink.cid 
LEFT JOIN (SELECT cid, clothes_id
           FROM wardrobe WHERE type ='Shirt') shirtlink ON shirtlink.cid = w.cid
LEFT JOIN shirt shi on shi.clothes_id = shirtlink.cid
where owner_id = 'poa'