sql join 2 rows

pagegen · August 19, 2010

"                                                                                        SELECT 	event.*,
													epr1.participantId AS team_1,
													epr1.participantRoleId AS team_1_role,
													epr2.participantId AS team_2,
													epr2.participantRoleId AS team_2_role
										  FROM 		bb_EventParticipantRelation epr1,
										  			bb_EventParticipantRelation epr2,
													bb_Event event
										  WHERE 
										  			event.parentId='".$leage_type."' AND 
													epr1.id != epr2.id AND
										  			epr1.parentParticipantId = '0' AND
													epr2.parentParticipantId = '0' AND
													epr1.eventId=event.id AND
													epr2.eventId=event.id

													ORDER BY epr1.participantRoleId ASC, event.startTime, team_1, team_2 ASC

													"

This sql gets 2 rows and combines them as one, it does work but the issue is it seems to be taking too long in loading the page also I just need some advice on a better version

Thank you

mikosiko · August 19, 2010

- table descriptions and how many records each?

- Indexes / PK's ?

- what your EXPLAIN PLAN shows?

DJTim666 · August 19, 2010

We need some code to work with..?

pagegen · August 20, 2010

Hi guys,

I have a table events, this table stores all the games

the 'id' col is the pk for this table

I need to read each row of this table

as I go down the table, I use the event_id, and look in

bb_EventParticipantRelation this table stores all the teams for the game, it can have 2/ 3 teams but I just need two..

This table has a field event_id which is the forigen key for event

what I am trying to do is,

create 1 row of the 2 rows in table bb_EventParticipantRelation so I can echo team one and team two at once

the current sql

joins the event table with bb_EventParticipantRelation, and then joins bb_EventParticipantRelation with bb_EventParticipantRelation

am not sure why it takes soo long to load

thank once again

kickstart · August 20, 2010

Hi

I think you JOIN is basically trying to do a massive cross join between bb_EventParticipantRelation and itself, hence slowing to a crawl

You say you have 2~3 teams on bb_EventParticipantRelatio for each event. Do you have a way of identifying them, ie a marker to say the 1st or 2nd team for the event?

All the best

Keith

pagegen · August 20, 2010

hey

yes the way I know which teams I want is by

in the bb_EventParticipantRelation table (the table with 2 rows) I have a field parentParticipantId, the teams I want have the value '0'

I do have this in my sql parentParticipantId = '0'

only 2 teams should be 0 each event

kickstart · August 20, 2010

Hi

Try something like this (not tested so please excuse any typos)

SELECT *
FROM bb_Event a
INNER JOIN bb_EventParticipantRelation b ON a.eventId = b.eventId
INNER JOIN (SELECT eventId, MAX(participantId) AS maxParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) c ON b.eventId = c.eventId AND b.participantId = c.maxParticipantId
INNER JOIN bb_EventParticipantRelation d ON a.eventId = d.eventId
INNER JOIN (SELECT eventId, MIN(participantId) AS minParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) e ON d.eventId = e.eventId AND d.participantId = e.minParticipantId

All the best

Keith

pagegen · August 20, 2010

Hi Keith,

Thanks for you help, but not sure why its not giving the corrent results..

kickstart · August 20, 2010

Hi

Could you export the table definitions and a couple of rows so I can have a play?

All the best

Keith

pagegen · August 20, 2010

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 20, 2010 at 02:07 PM
-- Server version: 5.0.91
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `games`
--

-- --------------------------------------------------------

--
-- Table structure for table `bb_EventParticipantRelation`
--

CREATE TABLE IF NOT EXISTS `bb_EventParticipantRelation` (
  `id` int(10) unsigned NOT NULL,
  `eventId` int(10) unsigned NOT NULL,
  `eventPartId` int(10) unsigned NOT NULL,
  `participantId` int(10) unsigned NOT NULL,
  `participantRoleId` int(10) unsigned NOT NULL,
  `parentParticipantId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `participantId` (`participantId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `bb_EventParticipantRelation`
--

INSERT INTO `bb_EventParticipantRelation` (`id`, `eventId`, `eventPartId`, `participantId`, `participantRoleId`, `parentParticipantId`) VALUES
(2034921, 186722046, 2, 1457, 2, 0),
(2034922, 186722046, 2, 1415, 1, 0),
(2034923, 186722047, 2, 516, 1, 0),
(2034924, 186722047, 2, 3954, 2, 0),
(2034928, 186722048, 2, 1621, 2, 0),
(2034929, 186722048, 2, 459, 1, 0),
(2034932, 186722049, 2, 1492, 2, 0),
(2034933, 186722049, 2, 451, 1, 0),
(2035473, 186559094, 1, 56387, 3, 38),
(2035474, 186661528, 2, 56387, 3, 38),
(2035475, 186559094, 1, 370535, 3, 23),
(2035476, 186661528, 2, 370535, 3, 23),
(2035477, 186559094, 1, 66192, 3, 210),
(2035500, 186715666, 2, 57165, 3, 158),
(2035501, 186715666, 2, 63920, 3, 158),
(2035505, 186715666, 2, 1800, 3, 158),
(2035506, 178705395, 1, 294816, 3, 158),
(2035507, 186715666, 2, 294816, 3, 158),
(2035508, 186715666, 2, 57201, 3, 158),
(2035510, 178705395, 1, 57193, 3, 168),
(2035511, 186715666, 2, 57193, 3, 168),
(2035512, 178705395, 1, 63892, 3, 1047),
(2035514, 178705395, 1, 53799, 3, 1047),
(2035516, 186715666, 2, 54568, 3, 158),
(2035518, 178705395, 1, 53803, 3, 168),
(2035519, 186715666, 2, 53803, 3, 168),
(2035520, 186715666, 2, 7893, 3, 158),
(2035521, 186715666, 2, 63902, 3, 158),
(2035522, 178705395, 1, 53571, 3, 168),
(2035523, 186715666, 2, 53571, 3, 168),
(2035525, 178705395, 1, 66727, 3, 168),
(2035526, 186715666, 2, 66727, 3, 168),
(2035527, 178705395, 1, 66140, 3, 168),
(2035528, 186715666, 2, 66140, 3, 168),
(2035529, 178705395, 1, 87930, 3, 168),
(2035530, 186715666, 2, 87930, 3, 168),
(2035531, 178705395, 1, 57316, 3, 1047),
(2035533, 186715666, 2, 45443, 3, 158),
(2035534, 186715666, 2, 88694, 3, 158),
(2035535, 186715666, 2, 66861, 3, 158),
(2035536, 178705395, 1, 56263, 3, 168),
(2035537, 186715666, 2, 56263, 3, 168),
(2035539, 178705395, 1, 82530, 3, 168),
(2035540, 186715666, 2, 82530, 3, 168),
(2035541, 186715666, 2, 57169, 3, 158),
(2035542, 178705395, 1, 63899, 3, 1047),
(2035544, 178705395, 1, 81501, 3, 168),
(2035545, 186715666, 2, 81501, 3, 168),
(2035546, 178705395, 1, 82506, 3, 1047),
(2035548, 186715666, 2, 57166, 3, 158),
(2035549, 178705395, 1, 55295, 3, 168),
(2035550, 186715666, 2, 55295, 3, 168),
(2035553, 186722111, 2, 289, 1, 0),
(2035554, 186722111, 2, 3639, 2, 0),
(2035555, 186715645, 2, 82365, 3, 255),
(2035556, 186715645, 2, 82368, 3, 255),
(2035557, 186715645, 2, 56246, 3, 255),
(2035558, 186715645, 2, 56502, 3, 255),
(2035559, 186715645, 2, 295963, 3, 255),
(2035560, 186715645, 2, 295964, 3, 255),
(2035561, 186715645, 2, 295962, 3, 255),
(2035562, 186715645, 2, 295965, 3, 255),
(2035563, 186715645, 2, 82376, 3, 255),
(2035564, 186715645, 2, 82364, 3, 255),
(2035565, 186715645, 2, 295969, 3, 255),
(2035566, 186715645, 2, 295959, 3, 255),
(2035567, 186715645, 2, 55376, 3, 255),
(2035568, 186715645, 2, 295966, 3, 255),
(2035572, 186715644, 2, 45516, 3, 257),
(2035573, 186715644, 2, 289360, 3, 257),
(2035574, 186715644, 2, 68006, 3, 257),
(2035575, 186658883, 1, 289357, 3, 257),
(2035576, 186715644, 2, 289357, 3, 257),
(2035577, 186715644, 2, 289359, 3, 257),
(2035578, 186715644, 2, 82359, 3, 257),
(2035579, 186715644, 2, 289358, 3, 257),
(2035580, 186715644, 2, 68008, 3, 257),
(2035581, 186715644, 2, 89044, 3, 257),
(2035582, 186715644, 2, 67976, 3, 257),
(2035583, 186715644, 2, 89282, 3, 257),
(2035584, 186715644, 2, 67997, 3, 257),
(2035585, 186715644, 2, 45529, 3, 257),
(2035593, 186719144, 2, 73793, 3, 254),
(2035594, 186719144, 2, 73791, 3, 254),
(2035595, 186719144, 2, 289990, 3, 254),
(2035596, 186719144, 2, 55835, 3, 254),
(2035597, 186719144, 2, 73780, 3, 254),
(2035598, 186719144, 2, 53934, 3, 254),
(2035599, 186658883, 1, 82387, 3, 254),
(2035600, 186719144, 2, 82387, 3, 254),
(2035601, 186719144, 2, 73792, 3, 254),
(2035602, 186719144, 2, 289992, 3, 254),
(2035603, 186719144, 2, 73779, 3, 254),
(2035604, 186719144, 2, 73783, 3, 254),
(2035615, 186708404, 2, 292873, 3, 222),
(2035616, 186708404, 2, 292872, 3, 222),
(2035617, 186708404, 2, 147689, 3, 222),
(2035618, 186708404, 2, 57368, 3, 222),
(2035619, 186708404, 2, 63930, 3, 222),
(2035620, 186708404, 2, 88886, 3, 222),
(2035621, 186708404, 2, 67608, 3, 222),
(2035622, 186708404, 2, 105687, 3, 222),
(2035623, 186708404, 2, 67510, 3, 222),
(2035624, 186708404, 2, 102850, 3, 222),
(2035625, 186708404, 2, 67376, 3, 222),
(2035626, 186708404, 2, 147699, 3, 222),
(2035627, 186708404, 2, 102852, 3, 222),
(2035628, 186708404, 2, 125804, 3, 222),
(2035629, 186708404, 2, 373063, 3, 222),
(2035630, 186708410, 2, 71092, 3, 232),
(2035631, 186708410, 2, 126197, 3, 232),
(2035632, 186708410, 2, 87698, 3, 232),
(2035633, 186708410, 2, 71109, 3, 232),
(2035634, 186708410, 2, 126186, 3, 232),
(2035635, 186708410, 2, 126191, 3, 232),
(2035636, 186708410, 2, 71108, 3, 232),
(2035637, 186708410, 2, 126194, 3, 232),
(2035643, 186714415, 2, 293416, 3, 68),
(2035644, 186714415, 2, 102907, 3, 68),
(2035724, 186714427, 2, 7568, 3, 54),
(2035731, 186722124, 2, 412056, 2, 0),
(2035732, 186722124, 2, 1029, 1, 0),
(2035733, 186722125, 2, 118060, 1, 0),
(2035734, 186722125, 2, 1573, 2, 0),
(2035791, 186722126, 2, 1576, 1, 0),
(2035792, 186722126, 2, 118065, 2, 0),
(2035801, 186714431, 2, 87310, 3, 66),
(2035802, 186714411, 2, 89439, 3, 45),
(2035809, 186714432, 2, 70714, 3, 55),
(2035813, 186708406, 2, 82013, 3, 217),
(2035814, 186708406, 2, 72202, 3, 217),
(2035815, 186708406, 2, 57147, 3, 217),
(2035816, 186708406, 2, 68726, 3, 217),
(2035817, 186708406, 2, 297533, 3, 217),
(2035818, 186708406, 2, 297541, 3, 217),
(2035819, 186708406, 2, 63712, 3, 217),
(2035820, 186708406, 2, 45468, 3, 217),
(2035821, 186708406, 2, 89510, 3, 217),
(2035822, 186708406, 2, 82017, 3, 217),
(2035823, 186708406, 2, 297542, 3, 217),
(2035824, 186708406, 2, 82034, 3, 217),
(2035825, 186708406, 2, 72205, 3, 217),
(2035826, 186708411, 2, 293247, 3, 539),
(2035827, 186708411, 2, 87746, 3, 539),
(2035828, 186708411, 2, 293231, 3, 539),
(2035829, 186708411, 2, 67961, 3, 539),
(2035830, 186708411, 2, 293253, 3, 539),
(2035831, 186708411, 2, 293236, 3, 539),
(2035832, 186708411, 2, 293235, 3, 539),
(2035833, 186708411, 2, 293246, 3, 539),
(2035834, 186708411, 2, 293239, 3, 539),
(2035835, 186708411, 2, 293245, 3, 539),
(2035836, 186708411, 2, 293242, 3, 539),
(2035837, 186708411, 2, 293234, 3, 539),
(2035838, 186708411, 2, 293254, 3, 539),
(2035839, 186708411, 2, 293233, 3, 539),
(2035840, 186708411, 2, 293232, 3, 539),
(2035841, 186708411, 2, 293248, 3, 539),
(2035842, 186708411, 2, 293238, 3, 539),
(2035843, 186708412, 2, 150128, 3, 229),
(2035844, 186708412, 2, 125879, 3, 229),
(2035845, 186708412, 2, 125887, 3, 229),
(2035846, 186708412, 2, 72270, 3, 229),
(2035847, 186708412, 2, 125894, 3, 229),
(2035848, 186708412, 2, 125877, 3, 229),
(2035849, 186708412, 2, 125874, 3, 229),
(2035850, 186708412, 2, 150317, 3, 229),
(2035851, 186708412, 2, 125878, 3, 229),
(2035852, 186708412, 2, 125886, 3, 229),
(2035853, 186708409, 2, 88933, 3, 536),
(2035854, 186708409, 2, 72250, 3, 536),
(2035855, 186708409, 2, 125888, 3, 536),
(2035856, 186708409, 2, 290825, 3, 536),
(2035857, 186708409, 2, 290822, 3, 536),
(2035858, 186708409, 2, 87635, 3, 536),
(2035859, 186708409, 2, 290807, 3, 536),
(2035860, 186708409, 2, 290814, 3, 536),
(2035861, 186708409, 2, 290820, 3, 536),
(2035862, 186708409, 2, 290815, 3, 536),
(2035863, 186708409, 2, 290816, 3, 536),
(2035864, 186708409, 2, 290823, 3, 536),
(2035865, 186708409, 2, 56505, 3, 536),
(2035866, 186708409, 2, 290808, 3, 536),
(2035887, 186708408, 2, 125851, 3, 215),
(2035888, 186708408, 2, 150397, 3, 219),
(2035889, 186708408, 2, 72220, 3, 219),
(2035890, 186708408, 2, 88820, 3, 219),
(2035891, 186708408, 2, 147694, 3, 215),
(2035892, 186708408, 2, 67875, 3, 219),
(2035893, 186708408, 2, 67874, 3, 219),
(2035894, 186708408, 2, 90200, 3, 219),
(2035895, 186708408, 2, 291472, 3, 219),
(2035896, 186708408, 2, 67959, 3, 219),
(2035897, 186708408, 2, 88816, 3, 219),
(2035898, 186708408, 2, 45424, 3, 215),
(2035899, 186708408, 2, 87883, 3, 215),
(2035900, 186708408, 2, 125836, 3, 215),
(2035901, 186708408, 2, 125852, 3, 215),
(2035902, 186708408, 2, 67962, 3, 219),
(2035903, 186708408, 2, 125857, 3, 215),
(2035904, 186708408, 2, 291474, 3, 219),
(2035905, 186708408, 2, 66860, 3, 219),
(2035906, 186708408, 2, 63503, 3, 219),
(2035907, 186708408, 2, 67418, 3, 219),
(2035908, 186708408, 2, 125839, 3, 215),
(2035909, 186580366, 1, 291512, 3, 321),
(2035910, 186703507, 2, 291512, 3, 321);

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 20, 2010 at 02:03 PM
-- Server version: 5.0.91
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `games`
--

-- --------------------------------------------------------

--
-- Table structure for table `bb_Event`
--

CREATE TABLE IF NOT EXISTS `bb_Event` (
  `id` int(10) unsigned NOT NULL,
  `typeId` int(10) unsigned NOT NULL,
  `isComplete` tinyint(1) unsigned NOT NULL,
  `sportId` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `startTime` int(10) unsigned NOT NULL,
  `endTime` int(10) unsigned NOT NULL,
  `deleteTimeOffset` bigint(20) unsigned NOT NULL,
  `venueId` int(10) unsigned NOT NULL,
  `statusId` int(10) unsigned NOT NULL,
  `rootPartId` int(10) unsigned NOT NULL,
  `url` varchar(255) NOT NULL,
  `note` varchar(255) NOT NULL,
  `parentId` int(10) unsigned NOT NULL,
  `parentPartId` int(10) unsigned NOT NULL,
  `popularity` int(10) unsigned NOT NULL,
  `promotionId` int(10) unsigned NOT NULL,
  `currentPartId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `typeId` (`typeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `bb_Event`
--

INSERT INTO `bb_Event` (`id`, `typeId`, `isComplete`, `sportId`, `name`, `startTime`, `endTime`, `deleteTimeOffset`, `venueId`, `statusId`, `rootPartId`, `url`, `note`, `parentId`, `parentPartId`, `popularity`, `promotionId`, `currentPartId`) VALUES
(186722046, 1, 1, 1, '', 1282410900, 0, 0, 0, 1, 2, '', '', 152261683, 1, 0, 0, 0),
(186722047, 1, 1, 1, '', 1282497300, 0, 0, 0, 1, 2, '', '', 152261683, 1, 0, 0, 0),
(186722048, 1, 1, 1, '', 1282410000, 0, 0, 0, 1, 2, '', '', 152261683, 1, 0, 0, 0),
(186722049, 1, 1, 1, '', 1282492800, 0, 0, 0, 1, 2, '', '', 152261683, 1, 0, 0, 0);

thats the tables dump file

btw I have another table called bb_Participant

this table stored the name of each team, the participantRoleId in the table 'bb_EventParticipantRelation', id the 'id' for that table,

I guess thats a diffrent matter for now

Thank you

kickstart · August 20, 2010

Hi

Just created those tables with that data and (other than me looking for eventId on the events table, now changed to Id) it appears to work fine:-

SELECT *
FROM bb_Event a
INNER JOIN bb_EventParticipantRelation b ON a.eventId = b.eventId
INNER JOIN (SELECT eventId, MAX(participantId) AS maxParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) c ON b.eventId = c.eventId AND b.participantId = c.maxParticipantId
INNER JOIN bb_EventParticipantRelation d ON a.eventId = d.eventId
INNER JOIN (SELECT eventId, MIN(participantId) AS minParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) e ON d.eventId = e.eventId AND d.participantId = e.minParticipantId

You might want to narrow down the data returned to something like this:-

SELECT a.*,
b.participantId AS team_1,
b.participantRoleId AS team_1_role,
d.participantId AS team_2,
d.participantRoleId AS team_2_role
FROM bb_Event a
INNER JOIN bb_EventParticipantRelation b ON a.Id= b.eventId
INNER JOIN (SELECT eventId, MAX(participantId) AS maxParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) c ON b.eventId = c.eventId AND b.participantId = c.maxParticipantId
INNER JOIN bb_EventParticipantRelation d ON a.Id= d.eventId
INNER JOIN (SELECT eventId, MIN(participantId) AS minParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) e ON d.eventId = e.eventId AND d.participantId = e.minParticipantId

All the best

Keith

pagegen · August 20, 2010

Hey mate

cheers for that

I need to

WHERE a.parentId='".$leage_type."

but it does nt seem to wrk

I added that right on the end?

pagegen · August 20, 2010

btw, I can see your selecting

maxParticipantId

minParticipantId

and also team_1

team_2

I only need team_1 and team_2

these ids I will pass to a function which will get me the team names

kickstart · August 20, 2010

Hi

That should work as well

SELECT a.*,
b.participantId AS team_1,
b.participantRoleId AS team_1_role,
d.participantId AS team_2,
d.participantRoleId AS team_2_role
FROM bb_Event a
INNER JOIN bb_EventParticipantRelation b ON a.Id= b.eventId
INNER JOIN (SELECT eventId, MAX(participantId) AS maxParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) c ON b.eventId = c.eventId AND b.participantId = c.maxParticipantId
INNER JOIN bb_EventParticipantRelation d ON a.Id= d.eventId
INNER JOIN (SELECT eventId, MIN(participantId) AS minParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) e ON d.eventId = e.eventId AND d.participantId = e.minParticipantId
WHERE a.parentId = 152261683

ParentId is an integer field so no need for the quotes around the value (although that doesn't affect it working or not).

It doesn't put out max or min participant directly, it is just using that to make sure that the 2 different participants are brought back for one event. Would be easier if (say) the team role was always 1 and 2 (hence asking earlier if "Do you have a way of identifying them, ie a marker to say the 1st or 2nd team for the event?").

Where are the team names stored? If on another table then it should be easy enough to JOIN to that table and save the function call.

All the best

Keith

pagegen · August 20, 2010

Hi mate

Really appreseate your help with this

I have attached 2 screen shots, one shws the results when I run my script, you will notic the fixture diffrent in the send image which is the new script,

I have to admin your script is very very fast, but the fixtures are wring

Thank you

[attachment deleted by admin]

kickstart · August 20, 2010

Hi

Bit stumped, and think I would need all the data to create the 2 pages to work it out.

Only thing that springs to mind would be the ORDER BY clause. I never put one in the SQL I wrote, but that could give you the issue you are having if you haven't added one.

SELECT a.*,
b.participantId AS team_1,
b.participantRoleId AS team_1_role,
d.participantId AS team_2,
d.participantRoleId AS team_2_role
FROM bb_Event a
INNER JOIN bb_EventParticipantRelation b ON a.Id= b.eventId
INNER JOIN (SELECT eventId, MAX(participantId) AS maxParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) c ON b.eventId = c.eventId AND b.participantId = c.maxParticipantId
INNER JOIN bb_EventParticipantRelation d ON a.Id= d.eventId
INNER JOIN (SELECT eventId, MIN(participantId) AS minParticipantId FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId) e ON d.eventId = e.eventId AND d.participantId = e.minParticipantId
WHERE a.parentId = 152261683
ORDER BY team_1_role ASC, a.startTime, team_1, team_2 ASC

All the best

Keith

pagegen · August 20, 2010

Hi mate

have attached the sql for 3 tables containing fill data

the 3rd table is the bb_Participant

the participantId in the bb_events table is the id in bb_Participant to get the team name

this table has all the team names

Thank you

[attachment deleted by admin]

pagegen · August 20, 2010

sorry that was wrong

the participantId in the bb_events table is the id in bb_Participant to get the team name

the 'participantId' in the 'bb_EventParticipantRelation' table is the id in bb_Participant to get the team name

kickstart · August 20, 2010

Hi

Spotted the problem.

The problem is due to the ordering, and then I think how your script processes it. The ones that appear are the ones where the home team is team 1 and the away team is team 2. When returned the other way round I think your script drops them.

I have a feeling your current script might be returning every line twice, once with the teams one way round and again with the teams the other way round (probably why epr1.participantRoleId ASC is in the sort clause, to push the duplicates to the end).

This should give you what you want, but you will have to add an index on participantRoleId on the bb_EventParticipantRelation table (otherwise it will run like a dog). Also an index on eventId on the bb_EventParticipantRelation table.

SELECT a . * ,  b.participantId AS team_1, b.participantRoleId AS team_1_role, f.name, d.participantId AS team_2, d.participantRoleId AS team_2_role, g.name
FROM bb_Event a
INNER JOIN bb_EventParticipantRelation b ON a.Id = b.eventId
INNER JOIN (SELECT eventId, MIN( participantRoleId ) AS minParticipantRoleId 
FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId )c ON b.eventId = c.eventId
AND b.participantRoleId = c.minParticipantRoleId
INNER JOIN bb_EventParticipantRelation d ON a.Id = d.eventId
INNER JOIN (SELECT eventId, MAX( participantRoleId ) AS maxParticipantRoleId
FROM bb_EventParticipantRelation WHERE parentParticipantId = '0' GROUP BY eventId )e ON d.eventId = e.eventId
AND d.participantRoleId = e.maxParticipantRoleId
INNER JOIN bb_participant f ON b.participantId = f.Id
INNER JOIN bb_participant g ON d.participantId = g.Id
WHERE a.parentId =186563308
ORDER BY team_1_role ASC , a.startTime, team_1, team_2 ASC

If participantRoleId is always 1 or 2 for a team then you could simplify it to

SELECT a . * ,  b.participantId AS team_1, b.participantRoleId AS team_1_role, f.name, d.participantId AS team_2, d.participantRoleId AS team_2_role, g.name
FROM bb_Event a
INNER JOIN bb_EventParticipantRelation b ON a.Id = b.eventId AND participantRoleId = 1
INNER JOIN bb_EventParticipantRelation d ON a.Id = d.eventId AND participantRoleId = 2
INNER JOIN bb_participant f ON b.participantId = f.Id
INNER JOIN bb_participant g ON d.participantId = g.Id
WHERE a.parentId =186563308
ORDER BY team_1_role ASC , a.startTime, team_1, team_2 ASC

All the best

Keith

pagegen · August 20, 2010

hmm getting error

Table 'games.bb_participant' doesn't exist

but the table does exist

participantRoleId is always 1 or 2

yes 1 is home

2 is away

kickstart · August 20, 2010

Hi

Only thing that strikes me is whether the bb_participant table is within the games database or it is bb_Participant (ie, case sensitive).

All the best

Keith

pagegen · August 20, 2010

Hi mate

the 1st sql showed loding after case sensitive issue

SELECT a . * ,  b.participantId AS team_1, b.participantRoleId AS team_1_role, f.name, d.participantId AS team_2, d.participantRoleId AS team_2_role, g.name
FROM bb_Event a
INNER JOIN bb_EventParticipantRelation b ON a.Id = b.eventId AND participantRoleId = 1
INNER JOIN bb_EventParticipantRelation d ON a.Id = d.eventId AND participantRoleId = 2
INNER JOIN bb_participant f ON b.participantId = f.Id
INNER JOIN bb_participant g ON d.participantId = g.Id
WHERE a.parentId =186563308
ORDER BY team_1_role ASC , a.startTime, team_1, team_2 ASC

trying this geting

Column 'participantRoleId' in on clause is ambiguous

thanks

kickstart · August 20, 2010

Hi

Doh, my fault. Copied the wrong version from where I typed it and missed the bits to specify the tables.

SELECT a . * , b.participantId AS team_1, b.participantRoleId AS team_1_role, f.name, d.participantId AS team_2, d.participantRoleId AS team_2_role, g.name

FROM bb_Event a

INNER JOIN bb_EventParticipantRelation b ON a.Id = b.eventId AND b.participantRoleId = 1

INNER JOIN bb_EventParticipantRelation d ON a.Id = d.eventId AND d.participantRoleId = 2

INNER JOIN bb_participant f ON b.participantId = f.Id

INNER JOIN bb_participant g ON d.participantId = g.Id

WHERE a.parentId =186563308

ORDER BY team_1_role ASC , a.startTime, team_1, team_2 ASC

All the best

Keith

pagegen · August 20, 2010

this script is super fast

thank you, I will do more testing to make sure all games are right

I think u was right about my script, it was returnign dupes

btw it is very important for me to have the team_1 as home team

is this happening in this case?

kickstart · August 20, 2010

Hi

Glad I could help.

btw it is very important for me to have the team_1 as home team

is this happening in this case?

Yes, team_1 will always have a role id of 1 which is home and team_2 a role_id of 2 which is away.

All the best

Keith

Sign In

sql join 2 rows

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information