Doing Query for with 3 tables and cant get the output how I want it

Question

There is 3 Tables

ITEM
```
itemID PK
```
INVENTORY
```
quantity
charID FK
itemID FK
```
CHARACTERS
```
charID PK
charName
```

.

ITEM 1---many INVENTORY 8----1 CHARACTER

I want to find the top 10 most stacked items that is stacked in the characters inventory and in who has this item stacked in their inventory. So in the end it will be something like itemID 1 has 4 stacks and these charID has the item, itemID 2 has 3 stacks and these charID has the item. In a table.

This is where I am currently at after trying to do this for a while

SELECT COUNT(INVENTORY.itemID) as Stacks, ITEM.itemID, CHARACTERS.charName
FROM INVENTORY
INNER JOIN ITEM
        ON INVENTORY.itemID = ITEM.itemID
INNER JOIN CHARACTERS
        ON INVENTORY.charID = CHARACTERS.charID
WHERE INVENTORY.quantity>1
GROUP BY ITEM.itemID, CHARACTERS.charName
ORDER BY  Stacks DESC
GO

I am instead of getting like data in a single row it splits it up into a bunch of rows for each character. for an example the table headers is

Stacks | ITEMID | charName    
1      | 1      | Char1    
1      | 1      | Char2

but what I want is

Stacks | ITEMID | charName    
2      | 1      | Char1,Char2

Any help is welcome Thanks :). If my format of my question is not up to par please leave a comment of how I can improve.

Which database are you using? Instead of grouping by `charname`, you'll want to use something like `group_concat`... — sgeddes, Jun 22 '16 at 14:15
I use SQL 2014 Management Studio http://stackoverflow.com/users/1073631/sgeddes — Tristan, Jun 22 '16 at 14:19
Looks like your db schema is incomplete. Because in your query you have `INVENTORY.itemID` and `INVENTORY.charID` — Juan Carlos Oropeza, Jun 22 '16 at 14:21
Yes I see I have now added them into the question @JuanCarlosOropeza — Tristan, Jun 22 '16 at 14:29
Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Juan Carlos Oropeza, Jun 22 '16 at 14:32
Yes that exactly what I want to do but I dont understand what is happening there. I wish `group_concat` worked. I am really starting to lose hope on this. @JuanCarlosOropeza — Tristan, Jun 22 '16 at 14:57
You dont understand the duplicated question? is exact what you want. — Juan Carlos Oropeza, Jun 22 '16 at 14:59
I dont know how to apply what they are saying in those answers for that question for my query. @JuanCarlosOropeza — Tristan, Jun 22 '16 at 15:03

Juan Carlos Oropeza · Accepted Answer · 2016-06-22T15:42:30.443

0

Sql Demo

SELECT [ITEMID], 
       LEFT(column_names , LEN(column_names )-1) AS column_names, 
       SUM([Stacks]) total_stacks  -- or COUNT() not sure which you need
FROM Table1 AS extern
CROSS APPLY
(
    SELECT [charName] + ','
    FROM Table1 intern
    WHERE intern.[ITEMID] = extern.[ITEMID]
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY [ITEMID], column_names;

NOTE: I expand your case so include different [itemID]

OUTPUT

edited Jun 22 '16 at 15:42

answered Jun 22 '16 at 15:21

Juan Carlos Oropeza

47,252
12
78
118

This is so close but when I implemented it the total stacks is added up and the names are next to each other but it adds all names thats in the other table instead of only those that has the item `1 Reflect,Reaper,Killer,Troller,Dealer,Reflect,Troller 1 2 Reflect,Reaper,Killer,Troller,Dealer,Reflect,Troller 1 4 Reflect,Reaper,Killer,Troller,Dealer,Reflect,Troller 1 6 Reflect,Reaper,Killer,Troller,Dealer,Reflect,Troller 1 8 Reflect,Reaper,Killer,Troller,Dealer,Reflect,Troller 3` – Tristan Jun 22 '16 at 15:42
Yes, That was the note. I anticipate you may have problem with those and already update it. – Juan Carlos Oropeza Jun 22 '16 at 15:43
Okay man I owe you my life. I cant even begin thanking you for the amount you have now done for me. I dont know how I can repay you if you perhaps play eve online or wished to try it out ill plex your account for 3 months this is not much but this is what I can give but really man thank you so much I F****** love you man :) @JuanCarlosOropeza – Tristan Jun 22 '16 at 15:52
I think i play that one or other very similar, but didnt like it. Now I play Summoner Wars – Juan Carlos Oropeza Jun 22 '16 at 15:53

Doing Query for with 3 tables and cant get the output how I want it

1 Answers1