#mysql
05 November 2007
Total 21 pages. You are browsing page 2/21.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
02:04 <****> no.. just don't work :/
02:05 <****> add water
02:05 <****>. But the problem is that we have just 78 openings..
02:05 <****> arjenAU: where exactly does that go? after the SELECT?
02:05 <****> Judge--: meaning it returned no rows as written? ;^)
02:05 <****> So I just wanna select the courses that have less then 98 subscriptions
02:05 <****> sqlnoob: instead of the other count
02:05 <****> yes, nothing returned
02:05 <****> Judge--: you can do that without a subquery
02:06 <****> and could you help me please? The website is locked down becacause of this query
02:06 <****> no shit
02:06 <****> what's the foreign key for tutoriais in inscricoes_tutoriais?
02:06 <****> tutorial_id ?
02:07 <****> yes.. user_id and tutorial_id
02:07 <****> ok
02:07 <****> and the normal id for controlling
02:07 <****> id, user_id, tutorial_id these are the complete fields
02:08 <****> arjenAU: this is what I get:
02:08 <****> SELECT MONTH(listing_date),MIN(COUNT(*)) AS lowmonth, MAX(COUNT(*)) AS highmonth FROM Listing
02:08 <****> SQL0119N An expression starting with "" specified in a SELECT clause, HAVING
02:08 <****> clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in
02:08 <****> a SELECT clause, HAVING clause, or ORDER BY clause with a column function and
02:08 <****> no GROUP BY clause is specified. SQLSTATE=42803
02:09 <****> SELECT t.* from tutoriais t,COUNT(it.*) AS cnt LEFT JOIN inscricoes_tutoriais it ON (t.tutorial_id = it.tutorial.id) HAVING cnt < 98
02:09 <****> nevermindddddddd
02:09 <****> sqlnoob: you left out the GROUP BY
02:09 <****> sorry xP
02:09 <****> lemme try to understand.. thanks arjenAU
02:10 <****> Judge--: well this one also deals with the possibility that noone registered. hence the left join.
02:10 <****> and it has to be a count(*), because a count on a specific col will only count non-null cols. and the left join produces nulls.
02:11 <****> arjenAU well people can choose if the want a coruse or not
02:11 <****> so it may or may not have a row in inscricoes_tutoriais
02:11 <****> Judge--: exactly. so the query deals with that
02:11 <****> sqlnoob: actually my query may be wrong ;-) but see how you go
02:12 * arjenAU doesn't have his sql brain today
02:12 <****> hu. thanks!
02:12 <****> very much
02:12 <****> arjenAU: what you think of this?:
02:12 <****> SELECT MONTH(listing_date),MIN(COUNT(*)) AS lowmonth, MAX(COUNT(*)) AS highmonth FROM Listing GROUP BY MONTH(listing_date) ORDER BY month_count DESC
02:12 <****> SQL0112N The operand of the column function "MIN" includes a column function,
02:13 <****> a scalar fullselect, or a subquery. SQLSTATE=42607
02:13 <****> yea
02:13 <****> exactly
02:13 <****> to get this without the orderby and only the low and high, you need either a join or a subquery.
02:14 <****> what LEFT JOIN does?
02:14 <****> and I don't have time for this right now
02:14 <****> Judge--: an outer join also joins if there's no match, putting nulls on the side where there's no match. for a left join, it'd put nulls on the right for no match. i.e. that'll count to 0
02:15 <****> arjenAU: uh... so there's no other way to get this?
02:16 <****> yes, at least 2, and I don'thave time for this right now sorry. perhaps kimseong or Xgc
02:16 <****> arjenAU it's not working..
02:17 <****> arjenAU: np thanks anyways =)
02:17 <****> probably I gave you wrog fields
02:17 <****> I'm trying to fix it
02:18 <****> Judge--: possibly you did. or I made a mistake. it helps to understand what it does, so you can engineer it yourself. my query was just an example, cut&pasting blindly is not a good idea
02:18 <****> yes
02:20 <****> sqlnoob: #1) You can use the form I showed to obtain the min and separately the max and then just JOIN or UNION the results, depending on the form you want the results.
02:22 <****> sqlnoob: #2) You can wrap the GROUP BY query to obtain the min and max month count and then join that with the original set to obtain the matching months. The problem with this approach (and the other for that matter) is there could be several months matching the min or max value.
02:22 <****> Xgc: you mean when you said: SELECT MONTH(date), COUNT(*) AS month_cnt FROM ... GROUP BY MONTH(date) ORDER BY month_cnt DESC; ?
02:22 <****> sqlnoob: Yes.
02:22 <****> arjenAU I'm sorry.. can't make it work
02:22 <****> :/
02:23 <****> Xgc: ok that's the one im using, i get all the months and their count
02:23 <****> sqlnoob: I don't think you've considered how to handle the case where there are multiple months matching the max or min count.
02:23 <****> ahhh arjenAU
02:23 <****> Xgc: oh yeah you're right argh!
02:24 <****> sqlnoob: Correct. To find the min, just ORDER BY cnt LIMIT 1; To find the max count, ORDER BY cnt DESC LIMIT 1;
02:24 <****> is there an equivalent for php's explode in mysql?
02:24 <****> there more then 78 rows in inscricoes_tutoriais
02:24 <****> i'd like to break a string using space as a separator
02:25 <****> Xgc: ok so i have to do that query twice, once in desc and the other on asc order.. and then union their result (which is the limit 1)?
02:25 <****> sqlnoob: The other mthod is to wrap the GROUP BY like this: SELECT MAX(cnt), MIN(cnt) FROM (the original select with GRUOP BY here) AS v1; This finds the min and max counts. JOIN this with the original set to obtain the corresponding months.
02:26 <****> sqlnoob: The LIMIT 1 is a way to obtain the first row of the set.
02:26 <****> ok let me try that, thanks a lot Xgc
02:26 <****> which one do you think is better?
02:26 <****> Xgc: a union is probably the quickest
02:26 <****> which of the solutions?
02:26 <****> ok
02:27 <****> or, easiest. quick depends on the dataset and the rest of the query
02:27 <****> as the query does not use an index. it'll have to do a tablescan, twice.
02:31 <****> Xgc: here:
02:31 <****> (SELECT MONTH(listing_date),count(*) AS month_count FROM Listing GROUP BY MONTH(listing_date) ORDER BY month_count LIMIT 1 DESC) UNION (SELECT MONTH(listing_date),count(*) AS month_count FROM Listing GROUP BY MONTH(listing_date) ORDER BY month_count LIMIT 1 DESC)
02:31 <****> SQL0104N An unexpected token "LIMIT" was found following "ORDER BY
02:31 <****> month_count". Expected tokens may include: "CONCAT".
02:31 <****> ahhhhhhhhh nvm! wait i didnt even put ASC order lol
02:32 <****> which aint gonna help that error anyways
02:33 <****> sqlnoob: default ordering is ASC if you don't specify in ORDER BY.
02:33 <****> could someone explain to my why I am getting "ERROR 1136 (21S01) at line 159: Column count doesn't match value count at row 1" when I run this script? http://pastebin.com/m3e502e1f
02:34 <****> arjenAU: yeah got ride of it, i still have same problem
02:35 <****> (SELECT MONTH(listing_date),count(*) AS month_count
02:35 <****> FROM Listing GROUP BY MONTH(listing_date) ORDER BY month_count LIMIT 1 DESC)
02:35 <****> UNION
02:35 <****> (SELECT MONTH(listing_date),count(*) AS month_count
02:35 <****> FROM Listing GROUP BY MONTH(listing_date) LIMIT 1);
02:36 <****> crackintosh: Don't use an implicit insert field list, nor an implicit select list (*).
02:37 <****> crackintosh: Check those tables. The column count probably doesn't match.
02:37 <****> where is it stating the column count?
02:37 <****> crackintosh: INSERT INTO tbl SELCET * FROM ...;
02:37 <****> is it referencing the the column count given to it by mysqld
02:37 <****> crackintosh: That implies two column lists.
02:38 <****> how do I put that on a procedure... suppose I receive a search argument in a variable, for example "searchthis searchthat". I'd like to break that into two strings and make a search like: AND ( Field1 LIKE '%searchthis%' OR Field2 LIKE '%searchthat%' ). But I don't know a function that would that string work
Total 21 pages. You are browsing page 2/21.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last