Jump to content

Special query needed


ghgarcia

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.