Special query needed
Posted 12 August 2006 - 07:32 PM
gameid int(11) No
week int(5) No
gametime int(16) No
vid varchar(5) No
vscore int(5) No
hid varchar(5) No
hscore int(5) No
ot enum('Y','N') No
result varchar(5) No
spread float(5,1) No
sresults varchar(5) No
user varchar(30) No
gameid int(3) No
pick varchar(5) No
The gameid from both tables are the same
The pick field from the picks table can equal either the vid or hid of the schedule table
What I'm attempting to do is create a query that will return the total number of picks for both hid and vid. Currently I can get only one total but not both. I'm using the following query:
select s.hid, count(p.pick) as total, s.week from lvb_picks as p, lvb_schedule as s where p.gameid = s.gameid and s.hid = p.pick or s.result group by s.hid order by total desc;
Any help would be greatly appreciated.
Posted 12 August 2006 - 09:43 PM
SELECT (SELECT count(*) FROM scheduleTable WHERE args here for hid) AS hidTotal, (SELECT count(*) FROM scheduleTable WHERE args here for vid) AS vidTotal everything else you want to grab;
Or if you want to add them then this might work:
SELECT ((SELECT count(*) blah blah) + (SELECT count(*) blah blah)) AS total, everything else
Then again you might want to look in the MySQL manual about "OUTER JOIN" (LEFT JOIN, etc.) It could possibly yield some quicker queries, though the ones I posted should work just fine.
Hope that helps..
Posted 12 August 2006 - 10:04 PM
The schedule table contains information about the games for the season it contains hid (the id of the home team), vid (the id of the visiting team) and the date/time of the game it also contains a score once the game has finished.
The picks table contains information about user picks for a game it contains gameid which points to the schedule gameid then it contains the pick which is equal to the vid or hid from the schedule table. There is a entry for each game that the user selected to win.
So what I need is a query that would show total number of picks (from the picks table) for home team (hid) and visiting team (vid) for each game.
the returned table could look like:
gameid vid hid
1 4 3
2 2 5
I have a test site with a query function that connects to my database at http://lvbash.com/ph.../mysql_send.php If you'd like to test.
Thanks again for your help I really appreciate it.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users