Jump to content


Photo

Special query needed


  • Please log in to reply
2 replies to this topic

#1 ghgarcia

ghgarcia
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationFlorida

Posted 12 August 2006 - 07:32 PM

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
George

#2 bigmatt19

bigmatt19
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 12 August 2006 - 09:43 PM

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

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

-matt

#3 ghgarcia

ghgarcia
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationFlorida

Posted 12 August 2006 - 10:04 PM

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 http://lvbash.com/ph.../mysql_send.php If you'd like to test.

Thanks again for your help I really appreciate it.

George

George




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users