Special query needed

[ghgarcia]

I have two tables that I need to query and get counts. The table structure follows:

Schedule Table
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

Picks table

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.

George

[bigmatt19]

not sure exactly what you're looking for... but something like this might help:

SELECT (SELECT count(*) FROM scheduleTable WHERE [i]args here for [b]hid[/b][/i]) AS hidTotal, (SELECT count(*) FROM scheduleTable WHERE [i]args here for [b]vid[/b][/i]) AS vidTotal [i]everything else you want to grab[/i];

Or if you want to add them then this might work:

SELECT ((SELECT count(*) blah blah) + (SELECT count(*) blah blah)) AS total, [i]everything else[/i]

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..

-matt

[ghgarcia]

I'm a novice in MySql queries. Let me explain a little more I have a football pool which contains several tables i.e. the schedule table and picks table.

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 [url=http://lvbash.com/phpfootball/mysql_send.php]http://lvbash.com/phpfootball/mysql_send.php[/url] If you'd like to test.

Thanks again for your help I really appreciate it.

George