|
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 |
|
|
# ? May 25, 2024 13:11 |
|
Xae posted:
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 |
|
Bitruder posted:Thanks for the previous reply! For some servers, expressing that as an "EXISTS" clause results in a better query plan. code:
|
# ? Jan 2, 2008 15:56 |
|
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 Edit: Ran it against the now default HR Schema] and I didn't see a difference between the results of these two queries. code:
code:
. Xae fucked around with this message at 00:49 on Jan 3, 2008 |
# ? Jan 3, 2008 00:18 |
|
This uses SQL2005 Common Table Expressions, but you can easily just build the two tables manually.code:
code:
code:
|
# ? Jan 3, 2008 00:48 |
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 |
|
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:
So, given some row in the table like so: code:
code:
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 |
|
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:
|
# ? Jan 3, 2008 11:02 |
|
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 |
|
code:
|
# ? Jan 3, 2008 21:48 |
|
deimos 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.
|
# ? Jan 3, 2008 21:58 |
|
Freezy posted:Yes, but then I would not know specifically which of orange/apple matched 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 |
|
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:
Freezy fucked around with this message at 01:08 on Jan 4, 2008 |
# ? Jan 4, 2008 01:04 |
|
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 |
|
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 |
|
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. 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.
|
# ? Jan 4, 2008 19:20 |
|
bloodsoup posted:So, I would like to find out: 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 |
|
The following search:code:
|
# ? Jan 7, 2008 04:26 |
|
Golbez posted:The following search: I think you want the between clause. http://www.w3schools.com/sql/sql_between.asp
|
# ? Jan 7, 2008 14:43 |
|
Golbez posted:The following search: I don't work with MySQL but if I were writing this in SQL Server it would be code:
EDIT: oh and depending on my data and the word I was searching I might consider... code:
code:
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 |
|
chocojosh posted:I think you want the between clause. 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 |
|
imBen posted:I don't work with MySQL but if I were writing this in SQL Server it would be Is this something Lucene would be useful with?
|
# ? Jan 7, 2008 17:36 |
|
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 |
|
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 |
|
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 |
|
Clever, but I don't believe that documentation actually addresses "how to do this stuff".
|
# ? Jan 7, 2008 20:27 |
|
deimos posted:That would make too much sense. Problem is that their documentation isn't that awesome. 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 |
|
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?
|
# ? Jan 7, 2008 23:18 |
|
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 |
|
noonches posted:I have a table of survey submission answers set up like this,
|
# ? Jan 8, 2008 21:27 |
|
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 |
|
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?
|
# ? Jan 8, 2008 22:02 |
|
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 |
|
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 |
|
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 |
|
I get some really weird counts with some simple queries (on a MS SQL server 2000):code:
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 |
|
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 |
|
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:
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 |
|
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 |
|
|
# ? May 25, 2024 13:11 |
|
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. 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:
cletus42o fucked around with this message at 02:46 on Jan 10, 2008 |
# ? Jan 10, 2008 02:43 |