CODEWITHSUNDEEP
mysqlsql
pyius
pyius

Reputation: 145

Pulling multiple tables with MySQL

Ok, I have multiple tables that I'm trying to basically form one SQL string on. Example of tables:

Table1

CardID    CardName    SetID    Rarity
1         Card #1     1        1
2         Card #2     1        2
3         Card #3     1        2
4         Card #4     2        2

Table 2

SetID     SetName     BID
1         Set #1      1
2         Set #2      2

Table 3

BID       BName
1         BName #1
2         BName #2

What I would like is an SQL string that would count the cards for a set and show the rarity for each card in that table. I want an output something similar to: SetID, SetName, BName, TotalCards, Total of Rarity 1, Total of Rarity 2

So output would be similar to returning columns:

1, Set #1, BName #1, 3, 1, 2
2, Set #2, BName #2, 1, 0, 1

Upvotes: 1

Views: 103

Answers (1)

Johan
Johan

Reputation: 76703

SELECT 
  c.SetID
  , s.SetName
  , b.BName 
  , count(*) as TotalCards
  , sum(if(c.rarity = 1,1,0) as Total_of_Rarity_1
  , sum(if(c.rarity = 2,1,0) as Total_of_Rarity_2
FROM card c
INNER JOIN `set` s ON (s.SetID = c.SetID)
INNER JOIN b on (b.BID = s.BID)
GROUP BY c.SetID

Links: http://dev.mysql.com/doc/refman/5.5/en/join.html

Upvotes: 2

Related Questions