#mysql
30 September 2007
Total 19 pages. You are browsing page 1/19.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Sun Sep 30 00:00:27 2007
--- Day changed Sun Sep 30 2007
00:00 <****> hiya
00:38 <****> whats a function to remove decimal points from a number?
00:38 <****> i.e 1.02 = 1
00:41 <****> found it.. it was truncate
00:42 <****> Hi, I have a sql command that results in a few rows, how can I filter those rows so they fit another criteria?
00:43 <****> WHERE cond1 AND cond2 AND ...
00:43 <****> Staz: ^^
00:44 <****> thumbs : I have "Select Title from Recipe,IngredientToRecipe,Ingredient where Name = 'milk' and IngredientID = IngredientRef and RecipeRef = RecipeID; which results in a few recipes
00:44 <****> ok.
00:45 <****> then I want to check each of those resulting recipes to see if they contain only vegetarian ingredients
00:45 <****> meaning I have to go back to the Ingredient table.
00:45 <****> Staz: you need an additional clause in your WHERE section.
00:45 <****> Just not sure how to go about that
00:46 <****> thumbs : how so?
00:46 <****> WHERE `fieldwhereingredientisstored` LIKE `%expression%'
00:46 <****> hmm is there any good article about foreign keys in mysql?
00:47 <****> normalization
00:47 <****> http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html and http://datamodel.org/NormalizationRules.html and http://mysqldump.azundris.com/archives/20-Nermalisation.html and some here http://www.keithjbrown.co.uk/vworks/mysql/
00:47 <****> iElectric: those ^^
00:47 <****> thanks thumbs
00:47 <****> thumbs : I have to add more joins at the end to get back to the Ingredients tabele?
00:47 <****> table*
00:48 <****> Staz: I tought your query already joined it.
00:48 <****> from what I read, anyway
00:48 <****> yeah It does
00:48 <****> heh I see :)
00:48 <****> I tried using MIN(IsVegitarian) = 1
00:48 <****> that sounds like a bad idea.
00:48 <****> but it didn't like that.
00:49 <****> thumbs : why is that ? :)
00:49 <****> Staz: well if you need to find all results which contain veggie ingredients, MIN() won't do it.
00:50 <****> need a subquery or a clever left join
00:50 <****> you need to compare the field against a list of values, or look for a pattern
00:51 <****> where not exists (select 1 from ingredienttable where vegetarian='no' and id=recipe.ingredient_id)
00:52 <****> so you get only recipes that contain only vegetarian ingredients
00:52 <****> Do subqueries work in MYSQL 4.0?
00:52 <****> they should.
00:52 <****> What does the manual say? Weren't they introduced in 4.1 ?
00:53 <****> bugger
00:53 <****> no, I believe 3.x didn't have them
00:53 <****> but 4.x does.
00:53 <****> and you can rewrite that with a left join
00:53 <****> subquery
00:53 <****> subqueries
00:53 <****> rewrite
00:54 <****> I'll take a look thanks
00:57 <****> select * from recipes r left join ingredients i on i.id=r.ingredient_id and i.vegetarian='no' and i.id is null;
00:57 <****> something like that
00:57 <****> see this too http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html
00:57 <****> Thanks Trengo
00:57 <****> cheers
00:58 <****> this works? UPDATE noticias SET Fecha_subida="0000-00-00 00:00:00",Portada=1 WHERE Id_noticia=17 AND NOT Id_autor=1 LIMIT 1
00:59 <****> Yes but it's a very, very bad idea
00:59 <****> MarkR42, bad idea? why?
00:59 <****> Because an UPDATE with a LIMIT but without ORDER BY effectively behaves non-deterministically
00:59 <****> You're sayign "update one row which satisfies these criteria, and I don't care which one"
00:59 <****> an update without a where clause, is a disaster...
00:59 <****> but the Id_noticia is a autonumeric cell, so no duplicate entry
01:00 <****> why the limit 1 then?
01:00 <****> the limit 1 is innecesary i nee
01:00 <****> i think, excuse
01:00 <****> and the id_autor?
01:00 <****> Trengo, =
01:00 <****> you already have id_noticia, which is stricter, yes?
01:00 <****> It is not a replication-safe query, it is also a silly query
01:00 <****> Trengo, yes
01:00 <****> because you're not saying which row to update
01:01 <****> If more than one row matches those criteria, which one is updated will be nondeterministic
01:01 <****> can fecha_subida be null?
01:01 <****> i write the limit 1 just in case
01:01 <****> i'd prefer that
01:01 <****> Trengo, yes
01:01 <****> NOW() - INTERVAL 30 day is that right?
01:01 <****> xota then update noticias set fecha_subida=null,portada=1 where id_noticia=17;
01:01 <****> If Id_noticia has a unique index on it, LIMIT 1 is not necessary
01:02 <****> ok MarkR42 thx but the AND NOT Id_autor=1 can be?
01:02 <****> xota: That part is fine.
01:02 <****> Yes but both need to be true
01:02 <****> ok, thanks for all
01:05 <****> in a sql sentence?
01:05 <****> hullo
01:05 <****> xota: you can
01:05 <****> hello
01:05 <****> 'value'
01:06 <****> anyone here able to help me figure out how to serach myql for a date range for a clumn formatted as a timestamp?
01:06 <****> thumbs: Score?
01:06 <****> seekwill: I've been on the road this week.
01:06 <****> MI, to be precise.
01:06 <****> ok thumbs thx
01:06 <****> XSMerlin: WHERE timestamp BETWEEN '2007-01-01 00:00:00' AND '2007-12-31 23:59:43'
01:06 <****> thumbs: And?
01:07 <****> You were just laying there, on the road, the whole week?
01:07 <****> I had no lappy, so no playing after work
01:07 <****> will try that, thanks
01:07 <****> Excuses
01:07 <****> but not sure if it will work
01:07 <****> I was in a new state... no lappy
01:07 <****> thumbs: Where there's a seekwill, there's a way
Total 19 pages. You are browsing page 1/19.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last