Help with SELECT statement

Question

Hi, I have two tables: players and cards2

In each cards2 row, there is at least one player id (pid, pid2, pid3, pid4). I'm trying to come up with a select statement to get all fname's and lname's if there is more than one player id (pid's that are not 0). There is always a pid, but not always a pid2, pid3, etc. Does this make sense?

Here are the structures.

Players table

CREATE TABLE IF NOT EXISTS `players` (
  `player_id` mediumint(10) NOT NULL AUTO_INCREMENT,
  `sport_id` tinyint(4) NOT NULL,
  `fname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `hof` tinyint(4) NOT NULL,
  PRIMARY KEY (`player_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=421 ;

Cards2 table

CREATE TABLE IF NOT EXISTS `cards2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` mediumint(10) NOT NULL,
  `pid2` mediumint(10) NOT NULL,
  `pid3` mediumint(10) NOT NULL,
  `pid4` mediumint(10) NOT NULL,
  `num` smallint(4) NOT NULL DEFAULT '0',
  `year` year(4) NOT NULL DEFAULT '0000',
  `notes` text,
  `new_manuf` varchar(50) NOT NULL,
  `sportid` tinyint(4) NOT NULL DEFAULT '0',
  `fname` varchar(25) NOT NULL,
  `lname` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=572 ;

Thanks in advance!

Answer 1

Use:

   SELECT CONCAT(p1.fname, ' ', p1.lname) AS player1,
          CONCAT(p2.fname, ' ', p2.lname) AS player2,
          CONCAT(p3.fname, ' ', p3.lname) AS player3,
          CONCAT(p4.fname, ' ', p4.lname) AS player4,
     FROM CARDS2 c
     JOIN PLAYERS p1 ON p1.playerid = c.pid
LEFT JOIN PLAYERS p2 ON p2.playerid = c.pid2
LEFT JOIN PLAYERS p3 ON p3.playerid = c.pid3
LEFT JOIN PLAYERS p4 ON p4.playerid = c.pid4

Answer 2

You could do it like this:

SELECT
    id,
    P1.fname AS fname1,
    P1.lname AS lname1,
    P2.fname AS fname2,
    P2.lname AS lname2,
    P3.fname AS fname3,
    P3.lname AS lname3,
    P4.fname AS fname4,
    P4.lname AS lname4
FROM cards2
LEFT JOIN players P1 ON pid = P1.player_id
LEFT JOIN players P2 ON pid2 = P2.player_id
LEFT JOIN players P3 ON pid3 = P3.player_id
LEFT JOIN players P4 ON pid4 = P4.player_id

You might want to consider if it is a good idea to normalize your database though. Having four columns for four players seems like a bad idea. What if you later want to allow 6 players?

Answer 3

About making sense, wouldn't it be cleaner creating simple n:m players_tables_map table? http://en.wikipedia.org/wiki/First_normal_form

Answer 4

You might want to consider redesigning your tables if that's a possibility. The repeating player ID's in the CARD2 table will make querying difficult and artificially limit how many players can be associated with a particular card. If you're still in the design stage you can save yourself some grief later by redoing things now. Here's a quick shot at it:

First, redo the 'cards2' table to eliminate the player references:

CREATE TABLE IF NOT EXISTS `cards2` ( 
  `card_id` int(11) NOT NULL AUTO_INCREMENT, 
  `num` smallint(4) NOT NULL DEFAULT '0', 
  `year` year(4) NOT NULL DEFAULT '0000', 
  `notes` text, 
  `new_manuf` varchar(50) NOT NULL, 
  `sportid` tinyint(4) NOT NULL DEFAULT '0', 
  PRIMARY KEY (`card_id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=572;

Then create what's called a "junction table" to map the many-to-many relationship between cards and players (i.e. one player can appear on many cards, and many players can appear on one card):

CREATE TABLE IF NOT EXISTS 'card_player' (
  'card_id' int(11) NOT NULL,
  'player_id' mediumint(10) NOT NULL,
  PRIMARY KEY ('card_id', 'player_id'),
  FOREIGN KEY 'card_id' REFERENCES card2.card_id,
  FOREIGN KEY player_id REFERENCES players.player_id
) ENGINE=MYISAM  DEFAULT CHARSET=latin1;

Eliminating the repeated player_id's in the CARDS2 table should make querying easier. For example,

SELECT c.card_id, p.fname, p.lname
  FROM players p,
       cards2 c,
       card_player x
  WHERE x.card_id = c.card_id AND
        p.player_id = x.player_id
  ORDER BY c.card_id;

This also eliminates the limitation of having at most four players associated with a particular card, so if you have a team card with 20 players on it this new set of table definitions can handle it.

I hope this helps.