SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Victor: Jun 18, 2004

Xae, joins will possibly increase the number of fields returned, especially if one is performing a semi-join (joining table A against B, but only selecting columns from A). In fact, incorrectly using semi-joins causes people to "fix" the results by slapping in a DISTINCT, where the proper behavior would be to change from a join to an IN subquery.

# ? Jan 2, 2008 13:49

Adbot: ADBOT LOVES YOU

# ? Apr 23, 2024 18:16

Azzmodan: Mar 13, 2004

Xae posted:

code:
SELECT * FROM entries 
WHERE ID = (SELECT entryID FROM metadata 
                        WHERE fieldID = (SELECT ID FROM metafields WHERE name=:name))
Isn't that the same as
code:
SELECT	*
FROM	entries
    INNER JOIN metadata ON metadata.entryID = entries.ID
    INNER JOIN metafields ON metafields.ID = metadata.fieldID
WHERE	metafields.name = :name
It seems like you are using sub-queries instead of just doing a join, unless my still hung-over rear end is missing something.

No, the first one only grabs the data from entries where the id matches that what is searched.

The second joins the entries, metadata, metafields tables into a single set (that we will now work on), and then grabs from that only the 'set'(that includes metadata/metafields in addition to the entries fields) that is searched.

if you had done "SELECT entries.*" in the second you would have gotten the same result as from the first, but there's still a difference in joining tables and filtering from that then doing lookups on an id.

I use the first if I just need data from the first table and the others are mere lookups(like in this example) and I use joins when I need data from both tables or are working on master/detail type tables.

I do dislike plural names on my tables though, if each row contains only a single entry it should be called entry, and if it contains more then one you need to rethink the whole thing

# ? Jan 2, 2008 13:55

m5: Oct 1, 2001

Bitruder posted:

Thanks for the previous reply!

Another question:
The following works fine when the subqueries only return 1 item, but when a subquery returns more than 1 items, I'd like to have all the results work by ORing them together, but it errors out now. Is there a way to treat multiple subquery rows as ORs?
code:
SELECT * FROM entries 
WHERE ID = (SELECT entryID FROM metadata 
                        WHERE fieldID = (SELECT ID FROM metafields WHERE name=:name))
EDIT:
SOLVED! Instead of =, I want "IN".

For some servers, expressing that as an "EXISTS" clause results in a better query plan.

code:

SELECT * FROM entries
 WHERE EXISTS (
   SELECT null FROM metadata
    WHERE metadata.entryID = entries.ID
      AND EXISTS (
        SELECT null FROM metafields
         WHERE metafields.fieldID = metadata.fieldID
           AND name = :name
      )
 )

# ? Jan 2, 2008 15:56

Xae: Jan 19, 2005

Victor posted:

Xae, joins will possibly increase the number of fields returned, especially if one is performing a semi-join (joining table A against B, but only selecting columns from A). In fact, incorrectly using semi-joins causes people to "fix" the results by slapping in a DISTINCT, where the proper behavior would be to change from a join to an IN subquery.

Hrm, aside from the cock-up about not prefacing the table name on the * I'm still not sure. I'm installing Oracle on my machine right now to check this out. drat server taking a poo poo :argh:

Edit:
Ran it against the now default HR Schema] and I didn't see a difference between the results of these two queries.

code:

Select  locations.*
FROM	locations
    INNER JOIN countries ON countries.country_id = locations.country_Id
    INNER JOIN regions on regions.region_id = countries.region_id
Where	regions.region_id = 2
ORDER BY locations.location_id

code:

Select	*
FROM	locations
WHERE	country_id IN (SELECT country_id FROM countries WHERE region_Id = (Select region_id from regions where region_id = 2))
ORDER BY locations.location_id

There are some minor differences in the explain plan, but nothing I would get worked up about, as they both have similar costs and execution plans
.

Xae fucked around with this message at 00:49 on Jan 3, 2008

# ? Jan 3, 2008 00:18

Victor: Jun 18, 2004

This uses SQL2005 Common Table Expressions, but you can easily just build the two tables manually.

code:

with A as (
    select id = 1, value = 2 union
    select id = 2, value = 4
), B as (
    select id = 1, a_id = 1, value = 'A' union
    select id = 1, a_id = 1, value = 'B'
)
select  A.*
from    A
--inner join B on B.a_id = A.id
where   id in (select a_id from B)

Results:

code:

id  value
--  -----
1   2

Uncommenting inner... and commenting where...:

code:

id  value
--  -----
1   2
1   2

# ? Jan 3, 2008 00:48

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Can somebody post an example of using sql_calc_found_rows with PDO? I still can't get mine to work. It always returns 1.

# ? Jan 3, 2008 02:53

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

I am using MySQL 5.0.32 in Debian Etch. The application accessing the database is written in Perl.

I have a (FULLTEXT indexed) TEXT column in a table that will contain data similar to this:

code:

[url]http://www.google.com[/url]
[url]http://www.yahoo.com[/url]
[url]http://www.altavista.com[/url]

I have a string "$string" that will contain data in the same format as the above. I must discover if any line of $string exists in any row, for the column in question. I also must know which line of $string matched successfully.

So, given some row in the table like so:

code:

Apple
Orange
Pear

And a value of $string as such:

code:

Orange
Tomato

I want a "good" (MySQL) solution for discovering that "Orange" (from $string) exists somewhere in my table.

If I cannot do this "well", I will do a rather slow and lovely loop that will execute one query per line of $string. Doing this will make me feel like a bad programmer. Or, I could generate a query that would SELECT COUNT(something) ... MATCH ... AGAINST for each line and return a row that would let me pinpoint any line that matches. (SELECT (SELECT COUNT(...), (SELECT COUNT(...) ...).

Any suggestions are appreciated.

# ? Jan 3, 2008 09:56

Azzmodan: Mar 13, 2004

Freezy posted:

I have a string "$string" that will contain data in the same format as the above. I must discover if any line of $string exists in any row, for the column in question. I also must know which line of $string matched successfully.

code:

SELECT myfield, COUNT(*) AS Recordcount 
FROM mytable 
WHERE myfield IN ('Orange', 'Tomato') 
GROUP BY myfield

That should give you every string that matched, with the number of matches

# ? Jan 3, 2008 11:02

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

Azzmodan posted:

That should give you every string that matched, with the number of matches

This seems to only match when myfield is exactly identical to 'Orange' or 'Tomato', whereas my 'myfield' will contain several unrelated terms separated by newlines. I must see if Orange or Tomato is on one of those lines, for any sql-row.

# ? Jan 3, 2008 21:40

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

code:

SELECT myfield, COUNT(*) AS Recordcount 
FROM mytable 
WHERE MATCH (myfield) AGAINST ('orange apple' IN BOOLEAN MODE);

# ? Jan 3, 2008 21:48

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

deimos posted:

code:

SELECT myfield, COUNT(*) AS Recordcount 
FROM mytable 
WHERE MATCH (myfield) AGAINST ('orange apple' IN BOOLEAN MODE);

Yes, but then I would not know specifically which of orange/apple matched

I suppose I could take the matching myfield and parse it myself, outside of MySQL. It would just be nice to have a pure MySQL solution.

# ? Jan 3, 2008 21:58

m5: Oct 1, 2001

Freezy posted:

Yes, but then I would not know specifically which of orange/apple matched

I suppose I could take the matching myfield and parse it myself, outside of MySQL. It would just be nice to have a pure MySQL solution.

You could dynamically build the query. You'd need a "WHERE" clause predicate to select rows that match one or more of the search strings. You'd then have columns with a "CASE" clause for each input string separately, counting up ones or zeros depending on whether an individual string matches the row. Your result set would be a single row with separate values for each of the separate input strings.

# ? Jan 3, 2008 22:40

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

m5 posted:

You could dynamically build the query.

This is what I have done to solve the problem. It excludes input sanitation and application-specific terms. $Database is a reference to a custom DBI wrapper object that I made for convenience.

This code is in Perl, and I provide it for the benefit of anyone with a similar problem.

code:

my @things = split /\n/, $CGI->param('things');
my $query = "SELECT ";
for (@things) {
 # [b]this line is dumb because it breaks (SA forum) tables otherwise[/b]
 $query .= 
     "(SELECT COUNT(id) FROM table WHERE MATCH(column) AGAINST('$_' IN BOOLEAN MODE)),";
}  
chop($query); # Remove trailing ','

my @duplicates = @{$Database->fetch_row($query)};
for (my $i = 0; $i <= $#things; $i++) {
   if ($duplicates[$i] != 0) {
      print "$things[$i] is duplicated.<br/>";
   }
}

A bit less tidy than I would like, but it gets the job done.

Freezy fucked around with this message at 01:08 on Jan 4, 2008

# ? Jan 4, 2008 01:04

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

How do I limit the number of rows I get for a query in Sybase? In Oracle I surrounded my query with a select and used rownum, and in MSSQL I used TOP...

Edit: My coworker mentioned something about setting a variable and then unsetting it, but that sounds suspcious and sloppy.

Edit^2: Well, "set rowcount N" and then "set rowcount 0" (to reset) works, but again, that feels wrong.

Triple Tech fucked around with this message at 17:51 on Jan 4, 2008

# ? Jan 4, 2008 17:47

bloodsoup: Jun 7, 2005

I work at a job where they've shown me how to run the database reports I need to run, but not much else. It's a temp job at a place that has been bought out by another company, so it's kind of a dead end. I've gotten to the point where I can do the reporting very quickly and am now bored most of the day. But I would like to learn more things and move up in the world. So I figure, this is a great place to explore some sql, since I've always learned better by doing.

So, I would like to find out:

What version of sql am I using? (I connect to AS/400 with Sequel Viewpoint, if that helps.)

What are the names of all the tables, and the last time they were accessed? (There are something like 10,000 of them.)

What are the names and descriptions of the fields in the tables I use? (I use maybe 20.)

How are these tables linked (what fields do they have in common)?

Basically, I would like to understand all the data available to me so that when someone asks me to give them something that's not in a report already, I can actually do it. Please pardon if any of these questions are too stupid or strange, I'm trying to get up to speed quickly.

# ? Jan 4, 2008 18:05

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

Triple Tech posted:

Edit^2: Well, "set rowcount N" and then "set rowcount 0" (to reset) works, but again, that feels wrong.

I have never used Sybase but perhaps this is useful:

http://forum.softpedia.com/lofiversion/index.php/t150162.html

quote:

SET ROWCOUNT is the simplest way to limit the number of rows in the result set. It only applies to your connection. Don't forget to SET ROWCOUNT 0 to reset the option to allow all rows.

There is no "rownum" by default in ASE, though another approach is to select the result set plus an identity column into a temp table and then use the identity column as a rownum to select a subset of the result set. Example:

select *, identity(9) as "rownum"
into #foobar
from sysindexes
select id, name from #foobar
where rownum between 22 and 42
drop table #foobar

quote:

SET ROWCOUNT is session based. Only valid on the connection you establish. Need to SET ROWCOUNT back to 0 as Bret suggests. Better to create a sp that runs the select as it may be easier to maintain.

Create Procedure sp_Test
As
Begin
SET ROWOUNT 10

select * from MyTable

SET ROWCOUNT 0

# ? Jan 4, 2008 19:20

benisntfunny: Dec 2, 2004; I'm Perfect.

bloodsoup posted:

So, I would like to find out:

What version of sql am I using? (I connect to AS/400 with Sequel Viewpoint, if that helps.)

What are the names of all the tables, and the last time they were accessed? (There are something like 10,000 of them.)

What are the names and descriptions of the fields in the tables I use? (I use maybe 20.)

How are these tables linked (what fields do they have in common)?

I'm not 100% Sure on this but it sounds like your database is probably DB2.

If that's the case, and you have all these tables this page might answer some questions:
http://www.devx.com/dbzone/Article/29585/1954?pf=true

Descriptions of the fields you use? I doubt that it's in the database itself, given there's 10,000 tables as you claim, but does your company use a data modeling program? If your database is in there it might provide descriptions in the event that the database had to integrate with a new one.

# ? Jan 5, 2008 02:00

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

The following search:

code:

 SELECT ... WHERE MATCH (text fields) AGAINST ('"test range"' IN BOOLEAN MODE)

will return all results with that exact phrase. So, what if I want to find 'test ranges'? Is there a way to smarten up the mysql literal string search, or do I have to hack in plurals? Note that this gives me 65 results, but if I take it out of boolean mode - so that it's looking for each word individually - I get thousands of results, because a lot of records have either test or range. Any ideas?

# ? Jan 7, 2008 04:26

chocojosh: Jun 9, 2007; D00D.

Golbez posted:

The following search:
code:
 SELECT ... WHERE MATCH (text fields) AGAINST ('"test range"' IN BOOLEAN MODE) 
will return all results with that exact phrase. So, what if I want to find 'test ranges'? Is there a way to smarten up the mysql literal string search, or do I have to hack in plurals? Note that this gives me 65 results, but if I take it out of boolean mode - so that it's looking for each word individually - I get thousands of results, because a lot of records have either test or range. Any ideas?

I think you want the between clause.

http://www.w3schools.com/sql/sql_between.asp

# ? Jan 7, 2008 14:43

benisntfunny: Dec 2, 2004; I'm Perfect.

Golbez posted:

The following search:
code:
 SELECT ... WHERE MATCH (text fields) AGAINST ('"test range"' IN BOOLEAN MODE) 
will return all results with that exact phrase. So, what if I want to find 'test ranges'? Is there a way to smarten up the mysql literal string search, or do I have to hack in plurals? Note that this gives me 65 results, but if I take it out of boolean mode - so that it's looking for each word individually - I get thousands of results, because a lot of records have either test or range. Any ideas?

I don't work with MySQL but if I were writing this in SQL Server it would be

code:

SELECT ... WHERE TEXT_FIELDS LIKE 'Test Range%'

Of course you could always use _ for a single wildcard. I doubt MySQL has stemming built into it.. but you could probably create a function to stem words.

EDIT:
oh and depending on my data and the word I was searching I might consider...

code:

SELECT ... WHERE TEXT_FIELDS IN ('Test Range','Test Ranges','Test Ranger',Testing Range','Tested Range')

This of course only works if you know the initial values before hand. The wildcard approach of course will work with variables.. you could say

code:

SELECT ... WHERE TEXT_FIELDS LIKE @WORD+'%'

but you might get things like 'Test Range." Then again, depending on the fields you're searching ( I assume it is literally a TEXT type ) you could have the option to do a replace first and remove all periods, replace them with spaces. This doesn't work on TEXT types in SQL Server.. you'd have to convert them to VARCHAR. I don't know if MySQL is the same way.

I think you have a lot of different options to choose from depending on exactly what kind of data you're looking at, and how this Query will be executed.

benisntfunny fucked around with this message at 15:00 on Jan 7, 2008

# ? Jan 7, 2008 14:45

benisntfunny: Dec 2, 2004; I'm Perfect.

chocojosh posted:

I think you want the between clause.

http://www.w3schools.com/sql/sql_between.asp

Call me crazy but I don't think that's at all what he's looking for. He's asking how to stem words, not look for the items that fall in the middle of them.

# ? Jan 7, 2008 14:47

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

imBen posted:

I don't work with MySQL but if I were writing this in SQL Server it would be
...

Yeah, I used to use LIKE %whatever% but the fulltext search is much faster and, generally, more intelligent, unless you want it to search for more than one word. (normally, the search will check for each word individually; by putting quotes around it I force it to search for the literal string, but then I'm stuck with exactly that, the literal string. Sigh. There seems to be no way, using the fulltext search, to have a fuzzy search for a string, only a fuzzy search for a word.

Is this something Lucene would be useful with?

# ? Jan 7, 2008 17:36

chocojosh: Jun 9, 2007; D00D.

imBen posted:

Call me crazy but I don't think that's at all what he's looking for. He's asking how to stem words, not look for the items that fall in the middle of them.

My bad. I got confused by his example of "test range" and "test ranges". For some reason I thought he wanted to search a *test range* (i.e. between two values).

# ? Jan 7, 2008 18:42

Victor: Jun 18, 2004

Shouldn't there be some documentation on MySQL full text search on how to do this stuff? I would be shocked if this were the first time such functionality has been required.

# ? Jan 7, 2008 18:50

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

Victor posted:

Shouldn't there be some documentation on MySQL full text search on how to do this stuff? I would be shocked if this were the first time such functionality has been required.

That would make too much sense. Problem is that their documentation isn't that awesome.

Victor posted:

Clever, but I don't believe that documentation actually addresses "how to do this stuff".

I wasn't being smarmy, their documentation sucks.

deimos fucked around with this message at 22:25 on Jan 7, 2008

# ? Jan 7, 2008 19:31

Victor: Jun 18, 2004

Clever, but I don't believe that documentation actually addresses "how to do this stuff".

# ? Jan 7, 2008 20:27

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

deimos posted:

That would make too much sense. Problem is that their documentation isn't that awesome.

I wasn't being smarmy, their documentation sucks.

It does suck a bit. I was a bit happy about 'in natural language mode' but then I saw that was the default.

What I need: It to find a literal string (i.e. more than one word) with *s on each side without the stopword or 50% limitation. Is this possible with fulltext in mysql, or should I just accept the 50% limit and/or go back to using LIKE?

Golbez fucked around with this message at 22:42 on Jan 7, 2008

# ? Jan 7, 2008 22:38

zootm: Aug 8, 2006; We used to be better friends.

Golbez posted:

What I need: It to find a literal string (i.e. more than one word) with *s on each side without the stopword or 50% limitation. Is this possible with fulltext in mysql, or should I just accept the 50% limit and/or go back to using LIKE?

The closest thing to doing this in MySQL that I can think of would be to use a regular expression with the "RLIKE" clause.

# ? Jan 7, 2008 23:18

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

I have a table of survey submission answers set up like this,

ID| answers
--|---------
1 | 1,3,4
2 | 3,4,7
3 | 2
4 | 3,7

where each number in the answers matches up to a question that they said yes to.

I need to pull the count for each time an answer shows up, like 4 shows up twice, 3 shows up 3 times, and so on, I can do a count for each different answer (ie SELECT count(answer1), count(answer2), count(answer3)..), but I can't even get that far, all I can seem to wrap my head around is:

SELECT count(IF(find_in_set(1, answers)),"what goes here?") ....

I am pretty sure this is the way to go but not positive, can anyone help me fix this or suggest something better.

# ? Jan 8, 2008 21:08

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

noonches posted:

I have a table of survey submission answers set up like this,

where each number in the answers matches up to a question that they said yes to.

I think you're better off having a unique record per survey per answer. Then you would only need to do a COUNT(*) WHERE answer_id = 1 to get what you're looking for.

# ? Jan 8, 2008 21:27

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

cletus42o posted:

I think you're better off having a unique record per survey per answer. Then you would only need to do a COUNT(*) WHERE answer_id = 1 to get what you're looking for.

Unfortunately, I did not set up the table or the framework I'm using for this. So I can't change the way the table is set up, and I can't use a PHP script to tear it apart and get the count because I need to feed a single query into a class that spits out a styled html table for the website. Is this as hard as it seems to me, or am I just having an off day?

am I any closer with this?:
SUM(IF(find_in_set(1, answers),1,0))

# ? Jan 8, 2008 21:37

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

noonches posted:

Unfortunately, I did not set up the table or the framework I'm using for this. So I can't change the way the table is set up, and I can't use a PHP script to tear it apart and get the count because I need to feed a single query into a class that spits out a styled html table for the website. Is this as hard as it seems to me, or am I just having an off day?

am I any closer with this?:
SUM(IF(find_in_set(1, answers),1,0))

I don't have access to MySQL to test it out, but it seems like you could do a COUNT(*) with WHERE find_in_set(1, answers) > 0. I don't know if what you wrote would work, or how it would compare performance-wise.

# ? Jan 8, 2008 22:02

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

cletus42o posted:

I don't have access to MySQL to test it out, but it seems like you could do a COUNT(*) with WHERE find_in_set(1, answers) > 0. I don't know if what you wrote would work, or how it would compare performance-wise.

Thanks for your help, I managed to hack this one out somehow, but through alot of shots in the dark. The SUM works, but for some reason it gave me a row for each question and some numbers in each row that added up to the answer, but that was fixed with a GROUP BY {blank} for some reason, my final query:

SELECT SUM(IF(find_in_set(1, s.answers),1,0)) as count1 ,
SUM(IF(find_in_set(2, s.answers),1,0)) as count2,
SUM(IF(find_in_set(3, s.answers),1,0)) as count3
FROM survey_submissions s
LEFT JOIN survey_questions q
ON q.id IN (s.answers)
WHERE s.answers <> ''
GROUP BY ''

That was awful, and to think if the rear end in a top hat who set up table had done it right, I would have been done long ago. Also, I'm lucky, performance doesn't make a bit of difference for most of the things I do, in this case just display survey submissions (so far a grand total of 30 over the past month) for a mid to low traffic website that an on-site admin will visit once every couple days.

# ? Jan 8, 2008 22:56

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

You shouldn't have to join to your questions table, that's probably why you're having to do the group by.

# ? Jan 8, 2008 23:48

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

cletus42o posted:

You shouldn't have to join to your questions table, that's probably why you're having to do the group by.

Without a join there was no way it was working, and honestly, I don't want to mess with something that works, because it's almost 6 and I'm about to leave, but if someone has suggestion on a better/simpler/more elegant way I'd of course like to hear it, but also if I never think about this task again, I'll be happy.

# ? Jan 8, 2008 23:59

Sebastian Flyte: Jun 27, 2003; Golly

I get some really weird counts with some simple queries (on a MS SQL server 2000):

code:

select count(*) from message

-----------
2318728

select count(*) from message where messageIsRead = 0

-----------
7745733

select messageIsRead, count(*) from message group by messageIsRead

messageIsRead 
------------- -----------
0             6718298
1             51088
0             228957
1             25544
0             175248
1             51088
0             623230
1             25544

messageIsRead is a bit column, which apparently has four different types of 1s and 0s. And the number of unread messages is more than 5 million higher than the total message count.

What the gently caress is happening here?

Edit: It turned out the indexes were seriously wonky. Rebuilding them got everything back to normal.

Sebastian Flyte fucked around with this message at 18:31 on Jan 9, 2008

# ? Jan 9, 2008 11:41

Pookster: Jul 4, 2007

I'm creating a PHP/MySQL based website dealing primarily with students. One of the pages shows staff members a student's profile and this data is retrieved from a number of tables, at least 7 at the moment. The main students table contains mostly keys to rows in the other tables.

I have 2 options for getting the required data, either one big query full of joins or multiple queries, the results of which are then processed with PHP.

In a client/server application, rather than keeping this PHP and MySQL specific, which option would generally be better? In the case of readability, multiple queries might be better but what are the other pros and cons of each option?

# ? Jan 9, 2008 13:35

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

So, I have a site where users can make predictions on events.

Users can be grouped into leagues.

When I calculate the score for a league, I only want to take the 15 top-scoring users from the league and total only their scores to store as the league's score. (to prevent leagues with <20 users having zero chance to rank against a league with 50+ users)

code:

select
	l.id,
	SUM(points) as total_points,
	SUM(num_correct) as total_num_correct
from
	fw_league l
inner join
	(
		select
			TOP 15
			t.points,
			t.num_correct
		from
			full_fw_user u
		inner join
			fw_user_total t
		on
			t.fw_user_id = u.fw_user_id	
		[b]where
			u.fw_league_id = l.id[/b]
		order by
			t.points DESC	
	) AS tempTable
on
	tempTable.fw_league_id = l.id

The bolded part seems to be the problem - I guess the table with alias l isn't accessible.

I got around it by just getting a list of league IDs, and looping over that and only summing up the results a league at a time, but I'd prefer to have it all in one query. Anyone have any ideas?

# ? Jan 9, 2008 17:40

benisntfunny: Dec 2, 2004; I'm Perfect.

cletus42o posted:

I got around it by just getting a list of league IDs, and looping over that and only summing up the results a league at a time, but I'd prefer to have it all in one query. Anyone have any ideas?

Well, I would have wrote a loop myself with SQL injection just doing a UNION over and over. Probably a horrible approach. I did a quick search and found:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

By the way, I don't think your inner join works at all. Your inner join selects 15 points... unless I'm reading something wrong. I probably am.

# ? Jan 10, 2008 01:58

Adbot: ADBOT LOVES YOU

# ? Apr 23, 2024 18:16

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

imBen posted:

By the way, I don't think your inner join works at all. Your inner join selects 15 points... unless I'm reading something wrong. I probably am.

Hm. The only reason why I'm doing it that way is because I need to only get the top 15 scorers (so I need to order by DESC along with the TOP 15) - and then I have to sum them in the main query.

Maybe if I put the tempTable in the FROM and then join that to the league table, I could get it to work.

Since I can just loop over the items, it's not a huge issue - and these are cached totals so it's not like I'm too concerned about efficiency - I'd just like to learn the way to do that. I haven't done too much SQL like this before, so I have a hard time visualizing what I need to do.

edit - here's the SQL we're talking about

code:

select
	l.id,
	SUM(points) as total_points,
	SUM(num_correct) as total_num_correct
from
	fw_league l
inner join
	(
		select
			TOP 15
			t.points,
			t.num_correct
		from
			full_fw_user u
		inner join
			fw_user_total t
		on
			t.fw_user_id = u.fw_user_id	
		where
			u.fw_league_id = l.id
		order by
			t.points DESC	
	) AS tempTable
on
	tempTable.fw_league_id = l.id

I'm trying to select the scores of the top 15 users in each league and get that total per league.

cletus42o fucked around with this message at 02:46 on Jan 10, 2008

# ? Jan 10, 2008 02:43

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »