0

I have 3 SQL SERVER data tables :

TBL_HOUSE :

|"ID_HOUSE"|"ID_PERSONS"|"QTY_PERSONS"|  
|"1"       |"|1|2|3|"   |"|1|2|1|"    |  
|"2"       |"|2|"       |"|3|"        |

TBL_PERSON :

|"ID_PERSON"|"ID_PETS"|"QTY_PETS"|  
|"1"        |"|3|1|"  |"|1|2|"   |  
|"2"        |"|1|2|"  |"|3|1|"   |

TBL_PET :

|"ID_PET"|"PET_TYPE"|"PET_PRICE"|  
|"1"     |"DOG"     |"500"    |  
|"2"     |"CAT"     |"200"    |

I have to make two queries.
The first for retrieve the number of each PET in a house.

ie. : In the HOUSE"2", there is 3 PERSON"2"
for each PERSON"2" there is 3 DOG and 1 CAT
In total in the HOUSE"2" is 9 DOG and 3 CAT.

The second to get the total value of pets in a house.
In HOUSE"2", the total value is 5100. (3*(3*500+1*200) = 5100)

Can you help me to write these queries?
Thanks a lot.

Alexander
  • 3,129
  • 2
  • 19
  • 33
Chalumeau
  • 101
  • 10
  • 5
    This a horrible design for your needs – Lamak Mar 05 '14 at 13:39
  • 2
    Is there any chance you can alter the table design? – Taryn Mar 05 '14 at 13:40
  • 1
    Do you really store Ids this way? – wumpz Mar 05 '14 at 13:42
  • @bluefeet Unfortunately not. These datatables are used by an other soft that I can't change. – Chalumeau Mar 05 '14 at 13:42
  • @wumpz, yes the data are stored like this. – Chalumeau Mar 05 '14 at 13:43
  • 5
    Ask/demand the "designer" of this horror, to provide you with the query. – ypercubeᵀᴹ Mar 05 '14 at 13:44
  • 2
    @Chalumeau If you cannot change the design you are really going to have issues trying to query the data. The only suggestion I have would be to look at using a [Split Spring function](http://stackoverflow.com/questions/2647/split-string-in-sql) to separate the data. But I **strongly advise** to push to change the design. – Taryn Mar 05 '14 at 13:47
  • 2
    Yeah there is no easy way to do what you want to do because whoever built these tables didn't know how to use a database. – JNK Mar 05 '14 at 13:57

1 Answers1

0

Sorry, it's all I can do for you. Your DataBase design makes me feel sick.

This query return you TBL_Person table in a NORMAL form. I hope you are smart enough to do the same with another tables and count all amounts that you need. 

 WITH Split(id_person, stpos_pets, stpos_qpets,endpos_pets, endpos_qpets) 
    AS(
        SELECT id_person, 0 AS stpos_pets, 0 AS stpos_qpets, CHARINDEX('|',id_pets) AS endpos_pets, CHARINDEX('|',qty_pets) AS endpos_qpets
        from TBL_PERSON
        UNION ALL
        SELECT TBL_PERSON.id_person, endpos_pets+1,endpos_qpets+1, CHARINDEX('|',id_pets,endpos_pets+1),CHARINDEX('|',qty_pets,endpos_qpets+1)
            FROM TBL_PERSON,Split
            WHERE endpos_pets > 0 AND #t.id_person=split.id_person
    )

    SELECT 'Id' = TBL_PERSON.id_person,
        'Pets' = SUBSTRING(id_pets,stpos_pets,COALESCE(NULLIF(endpos_pets,0),LEN('|3|1|')+1)-stpos_pets),
        'Quantity' = SUBSTRING(qty_pets,stpos_qpets,COALESCE(NULLIF(endpos_qpets,0),LEN('|3|1|')+1)-stpos_qpets)
    FROM Split,TBL_PERSON
    Where stpos_pets>0 and endpos_pets>0 and stpos_qpets>0 and endpos_qpets>0 and TBL_PERSON.id_person=split.id_person
MikkaRin
  • 3,026
  • 19
  • 34