• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Union Query Against A Dozen Tables?

First an explanation of my table structure

There is a characters table.  It has the following (pertanent) fields: char_id, char_name, char_faction, char_server.
The char_server field has possible values of 1 through 15.

There is a servers table.  It has the following fields: server_id and server_name.  The values of server_id correspond with the values in char_server.

There is a badges table.  It has the following (pertanent) fields: badge_id, badge_type.
The badge_type field has possible values of 1 through 6.

Finally there are 15 tables: char_badges_1 through char_badges_15.  They have the following fields: char_id, badge_id.

Typically, a query is simple.  I just say $server="char_badges_".$row['char_server']; after a query of the characters table.  Then I use the $server in my scripts to access the correct table.

I have one query I am stumped on, however.  It needs to query ALL 15 tables and return the results as below:

char_name, server_name, total badges, total badges of type 1, total badges of type 2, total badges of type 3, total badges of type 4, total badges of type 5, total badges of type 6.

The way I would typically do the totalling is like this:

for ($i=1;$i<=$typecount;$i++) {
  $sql = "SELECT COUNT(`badge_type`) AS \"count\" FROM ";
    $sql .= $charserver . ", badges WHERE char_id=" . $row['char_id'];
    $sql .= " AND ".$charserver.".badge_id=badges.badge_id AND badge_type=".$i;
    $sql .= " GROUP BY badge_type";
  $result2 = mysql_query($sql,$connect) or die(mysql_error());
  $row2 = mysql_fetch_array($result2); //50
  $charbadgecount[$i] = $row2['count'];
  if (empty($row2['count'])) $charbadgecount[$i]=0;
  $totalbadges += $row2['count'];
}


This would give me an array with one row for each badge type, and a variable with the total.  I assume I could still do this for every character returned - so what I really need is some kind of UNION query that will give me all the fields I need to run the above code for each character returned.

Any ideas?
0
WarriorPoet42
Asked:
WarriorPoet42
  • 2
  • 2
1 Solution
 
WarriorPoet42Author Commented:
I think I solved my own question.  The answer was this beast:

SELECT COUNT( char_badges_1.badge_id ) AS count, characters . *
FROM char_badges_1, characters
WHERE char_badges_1.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_2.badge_id ) AS count, characters . *
FROM char_badges_2, characters
WHERE char_badges_2.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_3.badge_id ) AS count, characters . *
FROM char_badges_3, characters
WHERE char_badges_3.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_4.badge_id ) AS count, characters . *
FROM char_badges_4, characters
WHERE char_badges_4.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_5.badge_id ) AS count, characters . *
FROM char_badges_5, characters
WHERE char_badges_5.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_6.badge_id ) AS count, characters . *
FROM char_badges_6, characters
WHERE char_badges_6.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_7.badge_id ) AS count, characters . *
FROM char_badges_7, characters
WHERE char_badges_7.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_8.badge_id ) AS count, characters . *
FROM char_badges_8, characters
WHERE char_badges_8.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_9.badge_id ) AS count, characters . *
FROM char_badges_9, characters
WHERE char_badges_9.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_10.badge_id ) AS count, characters . *
FROM char_badges_10, characters
WHERE char_badges_10.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_11.badge_id ) AS count, characters . *
FROM char_badges_11, characters
WHERE char_badges_11.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_12.badge_id ) AS count, characters . *
FROM char_badges_12, characters
WHERE char_badges_12.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_13.badge_id ) AS count, characters . *
FROM char_badges_13, characters
WHERE char_badges_13.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_14.badge_id ) AS count, characters . *
FROM char_badges_14, characters
WHERE char_badges_14.char_id = characters.char_id
GROUP BY char_id
UNION SELECT COUNT( char_badges_15.badge_id ) AS count, characters . *
FROM char_badges_15, characters
WHERE char_badges_15.char_id = characters.char_id
GROUP BY char_id
ORDER BY char_id ASC
0
 
Richard QuadlingSenior Software DeverloperCommented:
The only things I can add to this is give some warnings about unions.

1 - The column types across the unions MUST match.

SELECT 1
UNION
SELECT 'Fred'

will NOT work. On MSSQL I get "Syntax error converting the varchar value 'Fred' to a column of data type int."

2 - Use NULL to fill any trailing blank columns.

SELECT 1,2,3
UNION
SELECT 2

will NOT work. On MSSQL I get "All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.".

3 - You don't have to name the columns when sorting. You can use the column number. Very useful when column names are not present or consistent and the SQL statement is generated.

SELECT 'Total', 60
UNION
SELECT 'Level2 Subtal', 30
UNION
SELECT 'Level1 Subtal', 30
UNION
SELECT 'Level1', 10
UNION
SELECT 'Level1', 10
UNION
SELECT 'Level1', 10
UNION
SELECT 'Level2', 10
UNION
SELECT 'Level2', 10
UNION
SELECT 'Level2', 10
ORDER BY 1

results in ...

Level1      10.00
Level1 Subtal      30.00
Level2      10.00
Level2 Subtal      30.00
Total      60.00


which leads to my final comment.

4 - Only DISTINCT rows are returned from a UNION. See example above. There should be 3 level1 and 3 level2 rows. Unless you include ALL in the UNION statement.

SELECT 'Total', 60
UNION ALL
SELECT 'Level2 Subtal', 30
UNION ALL
SELECT 'Level1 Subtal', 30
UNION ALL
SELECT 'Level1', 10
UNION ALL
SELECT 'Level1', 10
UNION ALL
SELECT 'Level1', 10
UNION ALL
SELECT 'Level2', 10
UNION ALL
SELECT 'Level2', 10
UNION ALL
SELECT 'Level2', 10
ORDER BY 1

results in ...


Level1      10.00
Level1      10.00
Level1      10.00
Level1 Subtal      30.00
Level2      10.00
Level2      10.00
Level2      10.00
Level2 Subtal      30.00
Total      60.00


0
 
WarriorPoet42Author Commented:
i guess you get the points ;p
0
 
Richard QuadlingSenior Software DeverloperCommented:
Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now