| Author |
 Topic  |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
 Posted - 11 January 2005 : 10:32:48
|
Just writing a new script and I thought it might be as good a time as any to try and get to grips with SQL joins which I've always head trouble understanding. What I'm trying to do is pull the top 20 members order by their post counts. The kicker is, it's not a straightfoward top 20; I need the top 10 men and the top 10 women. Been fighting with this on and off for about 6 hours now and have made absolutely no progress. Maybe a join just isn't going to work in this situation or - more likely - I don't have a clue what I'm doing when it comes to joins!  So, it's time to ask for help! No point in posting anything I've got so far as it's a complete mess; if anyone can post SQL I'd need to achieve this - hopefully it'd go a long way towards helping me get me head around these darn joins! Cheers 
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 January 2005 : 10:38:56
|
| What's the "join" got to do with it? I would guess your problem is not with the join. I would also guess that you'd need a UNION of two queries: one for the top 10 men and the other for the top 10 women. Can you write each of these? I'll tell ya how to get the UNION after that. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 11 January 2005 : 10:47:07
|
Join's got nothing to do with it really - just thought it might be a good way to try and learn how joins work seeing as it's a simple enough query. Here's the SQL I did have before I tried the join. I know it's wrong - throws up a syntax error - but I could have sworn I'd seen this method used to achieve something like this.
SELECT MEMBER_ID,M_NAME,M_POSTS,M_SEX FROM MEMBERS WHERE (MEMBER_ID IN (SELECT MEMBER_ID FROM MEMBERS WHERE M_SEX='Male' ORDER BY M_POSTS DESC LIMIT 0,10) OR MEMBER_ID IN (SELECT MEMBER_ID FROM MEMBERS WHERE M_SEX='Female' ORDER BY M_POSTS DESC LIMIT 0,10)) AND M_STATUS=1 ORDER BY M_SEX DESC,M_POSTS DESC LIMIT 0,20
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 January 2005 : 12:24:12
|
Hmmm...I'm not too familiar with the SQL syntax for MySQL. One warning, though: most MySQL versions (if not all the current ones, but I can't be sure) do not support subqueries and you're using 2 subqueries in the WHERE clause. Unfortunately, last time I looked UNION queries where not supported either.
I would advise you to check what is supported or not in the MySQL version you're using now.
<corrected a syntactic mistake  > |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 11 January 2005 15:12:06 |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 11 January 2005 : 12:48:15
|
Ah-ha! That would explain why a script I'd used for months with Access that had an SQL subquery wouldn't work when I tried to implement it on Woo.ie. Cheers, Rui!
Will look into what can & can't be done with MySQL over the next few days (still a relative newbie). In the meantime, I've gone with 3 seperate queries - we decided to get the overall top 10 as well - but it's only for an admin page so I'm not going to lose too much sleep over it.
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 12 January 2005 : 06:47:41
|
quote:
Originally posted by ruirib
Glad to be of help (...even if not that much help ).
Well, it'll save me bouncing me brainbox off me keyboard trying to find syntax errors in SQL staements where there are none, so that's a plus! 
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
| |
Topic |
|
Topic Locked
