[cfaussie] Re: DISTINCT not so distinct
I think categoryid needs to be in the group by clause if you are going to use max() on it. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
MAX(dc.categoryID) needs to be aliased so MAX(dc.categoryID), AS CategoryID otherwise the column will come back as a temp named column -Original Message-From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED]On Behalf Of Seona BellamySent: Monday, May 08, 2006 3:57 PMTo: cfaussie@googlegroups.comSubject: [cfaussie] Re: DISTINCT not so distinctOn 08/05/06, Lucas [EMAIL PROTECTED] wrote: yeah, as you are using a MAX() you are going to need a group by...try...group by d.docTitle, d.docIntro, d.docContent, d.docImage, d.ID Hmm I changed the query to this: SELECT DISTINCT(d.docTitle), d.docIntro, d.docContent, d.docImage, MAX(dc.categoryID), d.ID FROM r_document AS d,r_docByType AS dt,r_docByCategory AS dc WHERE dt.docTypeID = 1 AND dt.documentID = d.ID ANDdc.documentID = d.ID GROUP BY d.docTitle, d.docIntro, d.docContent, d.docImage, d.ID ORDER BY d.dateEntered DESC LIMIT 3What I got was the following error:Variable CATEGORYID is undefined. brThe error occurred on line 22. This is referring to the place where I try and insert the category ID into the "Read more" link in the display. So it looks as though the "Max" bit is stopping the query from finding that one properly.Any suggestions?Seona. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
Can a document have more than one category or type? Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
I think might have to use the distinct in a subquery select ... where d.id = (SELECT DISTINCT(d.ID) from r_document AS d, r_docByType AS dt WHERE dt.docTypeID = 1 AND dt.documentID = d.ID ... LIMIT 3) and then use the that to get the records you need. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
On 08/05/06, Chris Velevitch [EMAIL PROTECTED] wrote: Can a document have more than one category or type? A document will only ever have 1 type, but it can have more than 1 category. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
On 08/05/06, Chris Velevitch [EMAIL PROTECTED] wrote: I think might have to use the distinct in a subqueryselect ... where d.id = (SELECT DISTINCT(d.ID) from r_document AS d,r_docByType AS dt WHERE dt.docTypeID= 1 ANDdt.documentID = d.ID ... LIMIT 3)and then use the that to get the records you need. Are we able to do this? I seem to remember running into this recently and finding that our version of mySQL didn't support subqueries? Or have we upgraded? Cheers, Seona. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
I think Chris has probably hit on the problem with the mutliple categories. If you are selecting the category as one of the fields, then your query will return a separate record for each category. The records aren't affected by DISTINCT when the records aren't actually the same. BlairOn 5/8/06, Seona Bellamy [EMAIL PROTECTED] wrote: On 08/05/06, Chris Velevitch [EMAIL PROTECTED] wrote: I think might have to use the distinct in a subqueryselect ... where d.id = (SELECT DISTINCT( d.ID) from r_document AS d,r_docByType AS dt WHERE dt.docTypeID= 1 ANDdt.documentID = d.ID ... LIMIT 3)and then use the that to get the records you need. Are we able to do this? I seem to remember running into this recently and finding that our version of mySQL didn't support subqueries? Or have we upgraded? Cheers, Seona. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
On 08/05/06, Lucas [EMAIL PROTECTED] wrote: Ok, that is what is playing up for you - ask you can have more than one category per article, dc.categoryID will make two records that appear the same to be different (two different categoryIDs)you may want to wrap it in a Max() so you only get one categoryID. L.Ah, good call. I hadn't considered that aspect of it. I tried changing the query to this: SELECT DISTINCT(d.docTitle), d.docIntro, d.docContent, d.docImage, MAX(dc.categoryID), d.ID FROMr_document AS d,r_docByType AS dt,r_docByCategory AS dc WHERE dt.docTypeID = 1 ANDdt.documentID = d.ID ANDdc.documentID = d.ID ORDER BY d.dateEntered DESC LIMIT 3 The error it gave me said something about a problem with the GROUP BY clause - which is strange, because there isn't one. Does there need to be? I've looked in the online manual, and I can't see anything that suggests Max() can only be used with a GROUP BY. Cheers, Seona. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
yeah, as you are using a MAX() you are going to need a group by...try...group by d.docTitle, d.docIntro, d.docContent, d.docImage, d.IDOn 5/8/06, Seona Bellamy [EMAIL PROTECTED] wrote: On 08/05/06, Lucas [EMAIL PROTECTED] wrote: Ok, that is what is playing up for you - ask you can have more than one category per article, dc.categoryID will make two records that appear the same to be different (two different categoryIDs)you may want to wrap it in a Max() so you only get one categoryID. L.Ah, good call. I hadn't considered that aspect of it. I tried changing the query to this: SELECT DISTINCT(d.docTitle), d.docIntro, d.docContent, d.docImage, MAX(dc.categoryID), d.ID FROMr_document AS d,r_docByType AS dt,r_docByCategory AS dc WHERE dt.docTypeID = 1 ANDdt.documentID = d.ID ANDdc.documentID = d.ID ORDER BY d.dateEntered DESC LIMIT 3 The error it gave me said something about a problem with the GROUP BY clause - which is strange, because there isn't one. Does there need to be? I've looked in the online manual, and I can't see anything that suggests Max() can only be used with a GROUP BY. Cheers, Seona. -- Lucashttp://www.thebitbucket.net --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: DISTINCT not so distinct
On 08/05/06, Lucas [EMAIL PROTECTED] wrote: yeah, as you are using a MAX() you are going to need a group by...try...group by d.docTitle, d.docIntro, d.docContent, d.docImage, d.ID Hmm I changed the query to this: SELECT DISTINCT(d.docTitle), d.docIntro, d.docContent, d.docImage, MAX(dc.categoryID), d.ID FROM r_document AS d,r_docByType AS dt,r_docByCategory AS dc WHERE dt.docTypeID = 1 ANDdt.documentID = d.ID ANDdc.documentID = d.ID GROUP BY d.docTitle, d.docIntro, d.docContent, d.docImage, d.ID ORDER BY d.dateEntered DESC LIMIT 3 What I got was the following error: Variable CATEGORYID is undefined. brThe error occurred on line 22. This is referring to the place where I try and insert the category ID into the Read more link in the display. So it looks as though the Max bit is stopping the query from finding that one properly. Any suggestions? Seona. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
