Query in a Query.. help me make it one.

[altendew]

Hi.. I currently have these queries.

	$q = $_db->Query('SELECT id,DATE_FORMAT(start, \'%M %d, %Y\') as start,DATE_FORMAT(end, \'%M %d, %Y\') as end FROM refContest ORDER BY id DESC LIMIT 10');
	while($r = $_db->Fetch($q))
	{
		$q = $_db->Query('SELECT w.*,m.username FROM refContestWinners w LEFT JOIN members m ON w.winner=m.id ORDER BY w.place');
		while($winner = $_db->Fetch($q))
		{
		}
	}

Now that actually runs 11 queries, because the first says get only 10 results, then each one of those results gets a query.

I know of a way to combine the queries, but then I would not be able to control the LIMIT. Is there a way to-do this?

 

[r937]

for those of us who don't read whatever language that is (c? perl? java? asp?) could you please explain how you want the queries combined?

r937.com | rudy.ca

 

[altendew]

Sorry, this is in PHP, but that is irrelevant, lets focus on the queries.

here are my tables.

refContest
------------------
id
start
end

refContestWinners
------------------
cid // This is refContest id
place
winner
referrals
prize
paid

There is at least 5 winners(refContestWinners) for each contest(refContest).

I would like to get a result returning something like this.

cid(1),start,end,place,winner(1),referrals,prize,paid
cid(1),start,end,place,winner(2),referrals,prize,paid
cid(1),start,end,place,winner(3),referrals,prize,paid
cid(1),start,end,place,winner(4),referrals,prize,paid
cid(2),start,end,place,winner(1),referrals,prize,paid
cid(2),start,end,place,winner(2),referrals,prize,paid
cid(3),start,end,place,winner(1),referrals,prize,paid
cid(3),start,end,place,winner(2),referrals,prize,paid
* The data between () represent values.

I could make it do what I declared above, but how could I make it limit to only 10 contests, because if I apply limit.. it will just limit the winners combined.

Thanks,
Andrew

 

[r937]

you want all winners for only 10 contests? or 10 winners for any contests? or 5 winners for only 10 contests?



r937.com | rudy.ca

 

[altendew]

if all winners for only 10 contests

 

[r937]

select c.id
     , DATE_FORMAT(c.start
          , \'%M %d, %Y\') as start
     , DATE_FORMAT(c.end
          , \'%M %d, %Y\') as end 
     , w.*
     , m.username 
  from (
       select X.id
            , X.start
            , X.end
         from refContest X
       left outer
         join refContest Y
           on Y.id > X.id
       group 
           by X.id
            , X.start
            , X.end
       having count(*) < 10
       ) as c
inner
  join refContestWinners w 
    on w.cid = c.id
inner 
  join members m 
    on m.id = w.winner
order 
    by w.place

r937.com | rudy.ca

 

[altendew]

ok still a problem, great work so far. I have it broken up into pages, so the next page will be LIMIT 11,20. How could I do this because you are using the HAVING.

 

[r937]

ah, the old paging problem, i wish you had said that earlier

i'm afraid you'd better stick to your two queries, just throw my ten minutes of careful coding out the window

r937.com | rudy.ca

 

[altendew]

Your coding style is very complex.. ive never seen it done in such a way. I know so much about MySQL to, but is the difference between left join and left outer join, and whats the difference between left join and inner join. I read the documentation but it's confusing.

I do appreciate your hard work though.