Get item_name instead of item_id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get item_name instead of item_id

Author

Topic

RafaCarvalhido
Starting Member

2 Posts

Posted - 2014-01-11 : 10:34:56

Hello gurus!

I´m trying to get a query where I´ll get an answer like this:


 +--------------+----------------+----------------+------------------+-----------------+
 |  champion_id | champion_name  | champion_item1 | champion_item... | champion_item16 |
 +--------------+----------------+----------------+------------------+-----------------+
 |       1      | Legolas        |     boots      |      ...         |      sword      |
 +--------------+----------------+----------------+------------------+-----------------+

This is the scenario how I set up my tables (let me know if I should change it):

table champions


 +--------------+----------------+
 |champion_id   | champion_name  | 
 +--------------+----------------+
 |     1        | Legolas        | 
 +--------------+----------------+

table equipment


 +--------------+----------------+------------------+-----------------+
 |  champion_id | champion_item1 | champion_item... | champion_item16 |
 +--------------+----------------+------------------+-----------------+
 |       1      |        5       |      ...         |        22       |
 +--------------+----------------+------------------+-----------------+

table items


 +--------------+----------------+
 |   item_id    |    item_name   | 
 +--------------+----------------+
 |       5      |       boots    | 
 +--------------+----------------+
 |       22     |       sword    | 
 +--------------+----------------+

But I still don´t know how to call the equipment table and get champion name and item names for the numbers. What did I do wrong and how can I fix it?

Thanks for the input.

P.S.: The foreign keys are in place.

Sir, answer me or you may get a gipsy curse so bad that no leprechaun will get it off.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-11 : 11:55:03

Try this:

select c.champion_id
      ,c.champion_name
      ,i1.item_name
      ,i2.item_name
      ,i3.item_name
      ,i4.item_name
      ,i5.item_name
      ,i6.item_name
      ,i7.item_name
      ,i8.item_name
      ,i9.item_name
      ,i10.item_name
      ,i11.item_name
      ,i12.item_name
      ,i13.item_name
      ,i14.item_name
      ,i15.item_name
      ,i16.item_name
  from champions as c
       left outer join equipment as e
                    on e.champion_id=c.champion_id
       left outer join items as i1
                    on i1.item_id=b.chamption_item1
       left outer join items as i2
                    on i2.item_id=b.chamption_item2
       left outer join items as i3
                    on i3.item_id=b.chamption_item3
       left outer join items as i4
                    on i4.item_id=b.chamption_item4
       left outer join items as i5
                    on i5.item_id=b.chamption_item5
       left outer join items as i6
                    on i6.item_id=b.chamption_item6
       left outer join items as i7
                    on i7.item_id=b.chamption_item7
       left outer join items as i8
                    on i8.item_id=b.chamption_item8
       left outer join items as i9
                    on i9.item_id=b.chamption_item9
       left outer join items as i10
                    on i10.item_id=b.chamption_item10
       left outer join items as i11
                    on i11.item_id=b.chamption_item11
       left outer join items as i12
                    on i12.item_id=b.chamption_item12
       left outer join items as i13
                    on i13.item_id=b.chamption_item13
       left outer join items as i14
                    on i14.item_id=b.chamption_item14
       left outer join items as i15
                    on i15.item_id=b.chamption_item15
       left outer join items as i16
                    on i16.item_id=b.chamption_item16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-11 : 12:48:14

[code]
SELECT champion_id,champion_name,champion_item1,champion_item,...,champion_item16
FROM
(
SELECT c.champion_id,c.champion_name,i.item_name,m.champion_item
FROM
(
SELECT champion_id,champion_item,equip_val
FROM equipment e
unpivot (equip_val FOR champion_item in (champion_item1,champion_item,...,champion_item16))u
)m
inner join items i
on i.item_id = m.equip_val
inner join champions c
on c.champion_id = m.champion_id
)r
PIVOT(MAX(item_name) FOR champion_item IN (champion_item1,champion_item,...,champion_item16))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2014-01-12 : 21:04:38

You will find it much easier to work with if you normalise your tables.

table equipment

+--------------+------------------+--------------------------+
| champion_id | champion_item_ID | champion_item_sequence |
+--------------+------------------+--------------------------+
| 1 | 5 | 1 |
+--------------+------------------+--------------------------+
| 1 | 22 | 16 |
+--------------+------------------+--------------------------+

FK on champion ID
FK on champion_item_ID
Unique constraint on all columns

RafaCarvalhido
Starting Member

2 Posts

Posted - 2014-01-13 : 06:12:09

After a few tweaks, bitsmed´s query worked exactly as I wished!

Thank you!

Answer me or you may get a gipsy curse so bad that no leprechaun will get it off.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 07:17:12

quote:
Originally posted by RafaCarvalhido

After a few tweaks, bitsmed´s query worked exactly as I wished!

Thank you!

Answer me or you may get a gipsy curse so bad that no leprechaun will get it off.

you can save some joins if you use PIVOT approach I posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources