query problem with null
When I do the following query: SELECT * FROM geo_trivia WHERE city IS NULL; certain columns that DO have 'NULL' value for city and not a '' (blank) value do not show up. I have even gone to the extent of reseting these records value as ='NULL' with UPDATE and they are still are not selected when I run the above query. Can anyone help?
RE: query problem with null
Have you tried to set city = null (i.e. without the quotes)? David. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Friday, March 09, 2012 4:24 PM To: mysql@lists.mysql.com Subject: query problem with null When I do the following query: SELECT * FROM geo_trivia WHERE city IS NULL; certain columns that DO have 'NULL' value for city and not a '' (blank) value do not show up. I have even gone to the extent of reseting these records value as ='NULL' with UPDATE and they are still are not selected when I run the above query. Can anyone help? The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: query problem with null
Ahhh... Thank you, that was exactly what the problem was. I will fix the code that is setting the value of these new records to 'NULL'. Thank you. 2012/3/9 David Lerer dle...@us.univision.com Have you tried to set city = null (i.e. without the quotes)? David. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Friday, March 09, 2012 4:24 PM To: mysql@lists.mysql.com Subject: query problem with null When I do the following query: SELECT * FROM geo_trivia WHERE city IS NULL; certain columns that DO have 'NULL' value for city and not a '' (blank) value do not show up. I have even gone to the extent of reseting these records value as ='NULL' with UPDATE and they are still are not selected when I run the above query. Can anyone help? The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
Re: query problem with null
- Original Message -
From: David Lerer dle...@us.univision.com
Have you tried to set city = null (i.e. without the quotes)?
Spot on, I'd think.
NULL values are not a string with NULL in it - that's only what it looks like
in query results :-) An empty string ('') is to strings what 0 (zero) is for
integers: it says the value of this field is nothing. NULL, on the other
hand, means the value of this field is a total unknown, which is useful, for
example, in a field 'quantity': zero is still a valid, meaningful quantity;
whereas you would use NULL to indicate that you simply do not know the quantity.
It's a bit of a peculiar concept, but as David indicated, IS NULL will not
match fields set to the string NULL - as that is a string, not an unknown.
Another funny attribute of NULL is that NULL != NULL. There simply *is* nothing
to compare, so you cannot ever say it's equal.
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Alphabetical pagination mysql regexp query problem
in my database, if i want to render firstname and lastname match case , am using REGEXP '^[abcd]' WORK FOR SINGLE FIELD, Now i have fname and lastname , How to make the query , which display both and single field satisfieds records , That is my query should return like Name(combined Firstname and lastname) Abbas, Ajai, Ashok,Amar, Abdul,Baskar, Abdul,cagan, Arun,Deena Any ideas On Fri, Oct 22, 2010 at 8:55 AM, Michael Dykman mdyk...@gmail.com wrote: You regular expression is alittle off. You don't need the OR operator '|' inside the character class definition, it is implied. Try this: [php] SELECT * FROM tbl where Fname REGEXP '^[abcd]' [/php] - michael dykman On Thu, Oct 21, 2010 at 11:07 PM, bharani kumar bharanikumariyer...@gmail.com wrote: Hi , Am just trying alphabetical pagination , Yes there is lot of pager class there for this , but in my requirement little but diff , that is totally 4 sets , A-DE-H I-PQ-S (Assume there are pager links) When user click the A-D , then it should show all the customer name starting with A , B, C, D names should be pull out , For that i just used this REGEXP QUERY , [php] SELECT * FROM tbl where Fname REGEXP '^[a]' [/php] This works fine, only rendering the starting with a Names , But if i add the or condition in that [php] SELECT * FROM tbl where Fname REGEXP '^[a|b|c|d]' [/php] This condition not working, the output for the above query , pullout the all records starting wiht what ever,(A-z) So What i want is, if i hit A-D the it should pullout only matched character example Amir Amboss Aron Basker Banu chithra chan dilli but my out put should not like , because this not exact records for that pager linke Amir Amboss Aron Basker Banu hanuman chithra gayathree chan dilli vasanth yakee Even i tried somthing like these query , cFname REGEXP '^[[::]]a.*|b.*[[::]]' cFname REGEXP '^[[::]]a.*|b.*' If i add the OR condition in the REGEXP , then displaying unwanted records, Also main thing , i want solution for case insensitive , Thanks -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/ -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/
Re: Alphabetical pagination mysql regexp query problem
Is this what you meant? SELECT * FROM tbl where Fname REGEXP '^[abcd]' AND Lname REGEXP '^[abcd]' (alternatively, the extression could be simpliefied as REGEXP '^[a-d] ) - md On Fri, Oct 22, 2010 at 11:01 AM, bharani kumar bharanikumariyer...@gmail.com wrote: in my database, if i want to render firstname and lastname match case , am using REGEXP '^[abcd]' WORK FOR SINGLE FIELD, Now i have fname and lastname , How to make the query , which display both and single field satisfieds records , That is my query should return like Name(combined Firstname and lastname) Abbas, Ajai, Ashok,Amar, Abdul,Baskar, Abdul,cagan, Arun,Deena Any ideas On Fri, Oct 22, 2010 at 8:55 AM, Michael Dykman mdyk...@gmail.com wrote: You regular expression is alittle off. You don't need the OR operator '|' inside the character class definition, it is implied. Try this: [php] SELECT * FROM tbl where Fname REGEXP '^[abcd]' [/php] - michael dykman On Thu, Oct 21, 2010 at 11:07 PM, bharani kumar bharanikumariyer...@gmail.com wrote: Hi , Am just trying alphabetical pagination , Yes there is lot of pager class there for this , but in my requirement little but diff , that is totally 4 sets , A-D E-H I-P Q-S (Assume there are pager links) When user click the A-D , then it should show all the customer name starting with A , B, C, D names should be pull out , For that i just used this REGEXP QUERY , [php] SELECT * FROM tbl where Fname REGEXP '^[a]' [/php] This works fine, only rendering the starting with a Names , But if i add the or condition in that [php] SELECT * FROM tbl where Fname REGEXP '^[a|b|c|d]' [/php] This condition not working, the output for the above query , pullout the all records starting wiht what ever,(A-z) So What i want is, if i hit A-D the it should pullout only matched character example Amir Amboss Aron Basker Banu chithra chan dilli but my out put should not like , because this not exact records for that pager linke Amir Amboss Aron Basker Banu hanuman chithra gayathree chan dilli vasanth yakee Even i tried somthing like these query , cFname REGEXP '^[[::]]a.*|b.*[[::]]' cFname REGEXP '^[[::]]a.*|b.*' If i add the OR condition in the REGEXP , then displaying unwanted records, Also main thing , i want solution for case insensitive , Thanks -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/ -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/ -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Alphabetical pagination mysql regexp query problem
Hi , Am just trying alphabetical pagination , Yes there is lot of pager class there for this , but in my requirement little but diff , that is totally 4 sets , A-DE-H I-PQ-S (Assume there are pager links) When user click the A-D , then it should show all the customer name starting with A , B, C, D names should be pull out , For that i just used this REGEXP QUERY , [php] SELECT * FROM tbl where Fname REGEXP '^[a]' [/php] This works fine, only rendering the starting with a Names , But if i add the or condition in that [php] SELECT * FROM tbl where Fname REGEXP '^[a|b|c|d]' [/php] This condition not working, the output for the above query , pullout the all records starting wiht what ever,(A-z) So What i want is, if i hit A-D the it should pullout only matched character example Amir Amboss Aron Basker Banu chithra chan dilli but my out put should not like , because this not exact records for that pager linke Amir Amboss Aron Basker Banu hanuman chithra gayathree chan dilli vasanth yakee Even i tried somthing like these query , cFname REGEXP '^[[::]]a.*|b.*[[::]]' cFname REGEXP '^[[::]]a.*|b.*' If i add the OR condition in the REGEXP , then displaying unwanted records, Also main thing , i want solution for case insensitive , Thanks -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/
Re: Alphabetical pagination mysql regexp query problem
You regular expression is alittle off. You don't need the OR operator '|' inside the character class definition, it is implied. Try this: [php] SELECT * FROM tbl where Fname REGEXP '^[abcd]' [/php] - michael dykman On Thu, Oct 21, 2010 at 11:07 PM, bharani kumar bharanikumariyer...@gmail.com wrote: Hi , Am just trying alphabetical pagination , Yes there is lot of pager class there for this , but in my requirement little but diff , that is totally 4 sets , A-D E-H I-P Q-S (Assume there are pager links) When user click the A-D , then it should show all the customer name starting with A , B, C, D names should be pull out , For that i just used this REGEXP QUERY , [php] SELECT * FROM tbl where Fname REGEXP '^[a]' [/php] This works fine, only rendering the starting with a Names , But if i add the or condition in that [php] SELECT * FROM tbl where Fname REGEXP '^[a|b|c|d]' [/php] This condition not working, the output for the above query , pullout the all records starting wiht what ever,(A-z) So What i want is, if i hit A-D the it should pullout only matched character example Amir Amboss Aron Basker Banu chithra chan dilli but my out put should not like , because this not exact records for that pager linke Amir Amboss Aron Basker Banu hanuman chithra gayathree chan dilli vasanth yakee Even i tried somthing like these query , cFname REGEXP '^[[::]]a.*|b.*[[::]]' cFname REGEXP '^[[::]]a.*|b.*' If i add the OR condition in the REGEXP , then displaying unwanted records, Also main thing , i want solution for case insensitive , Thanks -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/ -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Update query problem
So I'm having a problem with an update query. I have three tables: Table: A Columns: acnt, name, company, email, domain Table: AM Columns: acnt, m_id Table: M Columns: m_id, name, company, email, domain and I want to conditionally update the columns in one to values from the other. i.e., I want to put the value of A.name into M.name, but only if M.name is currently NULL, AND A.name has a usable value (not an empty string). This is what I came up with, but it doesn't work - it only replaces the values where the column in M is not null. update A join AM on A.acnt = AM.acnt joinM on AM.m_id = M.m_id SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name), M.company = IF( (!M.company AND A.company != ''), A.company, M.company), M.email = IF( (!M.email AND A.email != ''), A.email, M.email), M.domain = IF( (!M.domain AND A.domain != ''), A.domain, M.domain) Any thoughts? THanks, andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Update query problem
Try using the IS NULL operator instead of ! -Travis -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Thursday, September 16, 2010 10:47 AM To: mysql@lists.mysql.com Subject: Update query problem So I'm having a problem with an update query. I have three tables: Table: A Columns: acnt, name, company, email, domain Table: AM Columns: acnt, m_id Table: M Columns: m_id, name, company, email, domain and I want to conditionally update the columns in one to values from the other. i.e., I want to put the value of A.name into M.name, but only if M.name is currently NULL, AND A.name has a usable value (not an empty string). This is what I came up with, but it doesn't work - it only replaces the values where the column in M is not null. update A join AM on A.acnt = AM.acnt joinM on AM.m_id = M.m_id SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name), M.company = IF( (!M.company AND A.company != ''), A.company, M.company), M.email = IF( (!M.email AND A.email != ''), A.email, M.email), M.domain = IF( (!M.domain AND A.domain != ''), A.domain, M.domain) Any thoughts? THanks, andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct Query Problem
Hi Sonal, What information do you require ? Basically in the MasterTB we have productID and name, description. In the LookupTB we have the productID from the MasterTB, the productID from ProductTB and a char field for preferred supplier Yes/No. In the ProductTB we have a list of products from each supplier. This is link using the LookupTB. Let me know what other information you require. Thanks Neil On Tue, Dec 23, 2008 at 4:19 AM, Raghani, Sonal (IE10) sonal.ragh...@honeywell.com wrote: Hi, The problem statement needs to be elaborated. Please give the exact columns interms of wat is suppliers which table is it coming from etc. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Monday, December 22, 2008 5:16 PM To: Baron Schwartz; [MySQL] Subject: Re: Distinct Query Problem Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row -per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
OK, I've made further progress by changing GROUP BY ProductTB.ProductID, MasterTB.MasterID to GROUP BY MasterTB.MasterID. However ProductTB.Supplier is showing the incorrect Supplier. Why is this ? Thanks Neil On Mon, Dec 22, 2008 at 11:45 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.comwrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I've the following query which I'm having problems with. Basically I have a 5 tables as follows : MasterTB - Contains list of master records LookupTB - Contains relationship between MasterTB to ProductTB ContentTB - Contains description of product, and location of data files PriceTB - Contains list of prices per day for each product ProductTB - List of products SELECT MasterTB.MasterID, ProductTB.Supplier, MasterTB.Name, ContentTB.Title, ContentTB.FolderName, MIN(PriceTB.Price) As PriceDiscounts FROM MasterTB INNER JOIN LookupTB ON LookupTB.MasterID = MasterTB.MasterID INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID INNER JOIN PriceTB ON ProductTB.ProductID = PriceTB.ProductID INNER JOIN ContentTB ON ProductTB.ProductID = ContentTB.ProductID WHERE MasterTB.Enabled = 'Yes' AND ContentTB.Language = 'ENG' AND ContentTB.Site = 'www' AND PriceTB.Price 0 AND PriceTB.Quantity 0 GROUP BY ProductTB.ProductID, MasterTB.MasterID ORDER BY ProductTB.MarkUp DESC Basically each product is listed in the master table, and can have a number of suppliers linked to it (ProductTB). The query above will show me a list of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. Any ideas ? Thanks, Neil You are actually going to need at least 2 queries, which will be nested. You need to first find the lowest price, then figure out which supplier has that lowest price. If more than one supplier has the same lowest price, you won't be able to do it in a single query and will likely need to do post processing. Just an example to point you in the right direction. First, get the lowest price for the product(s) you are interested in: SELECT PriceTB.ProductID, MIN(PriceTB.Price) As MinPrice FROM PriceTB GROUP BY ProductID Then you use that as a virtual table (MinPriceList) to join on the supplier with that price for that product. SELECT ProductTB.Supplier, MinPriceList.ProductID, MinPriceList.MinPrice As PriceDiscounts FROM MasterTB INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID INNER JOIN ( SELECT PriceTB.ProductID, MIN(PriceTB.Price) As MinPrice FROM PriceTB GROUP BY ProductID ) AS MinPriceList ON ProductTB.ProductID=MinPriceList.ProductID AND ProductTB.Price=MinPriceList.MinPrice INNER JOIN ... Basically what you are doing is creating a virtual table on the fly based on a select statement. It sort of like a temp table, but without having to go through the creation and management of it. Treat the virtual table created from the query as if it was a regular table. As I mentioned, this will break if more than one supplier has the same price. You'll get an arbitrary supplier ID out of those with the minimum price. This is because there is no unique value to join on. Hope that points you in the right direction. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Distinct Query Problem
Hi, I've the following query which I'm having problems with. Basically I have a 5 tables as follows : MasterTB - Contains list of master records LookupTB - Contains relationship between MasterTB to ProductTB ContentTB - Contains description of product, and location of data files PriceTB - Contains list of prices per day for each product ProductTB - List of products SELECT MasterTB.MasterID, ProductTB.Supplier, MasterTB.Name, ContentTB.Title, ContentTB.FolderName, MIN(PriceTB.Price) As PriceDiscounts FROM MasterTB INNER JOIN LookupTB ON LookupTB.MasterID = MasterTB.MasterID INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID INNER JOIN PriceTB ON ProductTB.ProductID = PriceTB.ProductID INNER JOIN ContentTB ON ProductTB.ProductID = ContentTB.ProductID WHERE MasterTB.Enabled = 'Yes' AND ContentTB.Language = 'ENG' AND ContentTB.Site = 'www' AND PriceTB.Price 0 AND PriceTB.Quantity 0 GROUP BY ProductTB.ProductID, MasterTB.MasterID ORDER BY ProductTB.MarkUp DESC Basically each product is listed in the master table, and can have a number of suppliers linked to it (ProductTB). The query above will show me a list of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. Any ideas ? Thanks, Neil
Re: Distinct Query Problem
On Fri, Dec 19, 2008 at 7:03 PM, Tompkins Neil wrote: Basically each product is listed in the master table, and can have a number of suppliers linked to it (ProductTB). The query above will show me a list of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. Search the manual for group-wise maximum. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query problem
I am trying to display results from one or the other part of the query, however, at the moment it is showing results from both parts. The Replace part of the query works fine in that it chooses the correct data to display, but the content relevant to /8/ always displays even when I select, say, /13/ or /22/. I need to hide results for /8/ until it is selected. rsChannelArticles.Source = SELECT * FROM bunker01db1.tbl_allarticles WHERE (fld_category LIKE '%/ + Replace(rsChannelArticles__channel, ', '') + /%' AND fld_show = 1 AND fld_reldate =NOW()) OR (fld_category LIKE '%/8/%' AND fld_reldate =NOW()) ORDER BY fld_reldate DESC The reason for this is that the webpage displays content when a release date/time (or embargo) has passed, however, the section /8/ needs content to disappear once that date/time has passed. Any ideas? Thanks
Query problem
Hi all, Iam having the one table name called AccessDetails and data inside that tables is following, DateTime UserName SiteName ScanType Status Virus_Category | 2008-04-16 | 13:05:31 | 172.16.1.22 | - | www.veer.com |C | A| unclassified | | 2008-04-16 | 13:05:31 | 172.16.1.52 | - | blogactiv.eu |C | O | unclassified | | 2008-04-16 | 13:05:32 | 172.16.1.22 | - | www.veer.com |V | A| Internet | | 2008-04-16 | 13:05:32 | 172.16.1.52 | - | www.verylowsodium.com |C | D| unclassified | | 2008-04-16 | 13:05:32 | 172.16.1.52 | - | blogactiv.eu |V | A | unclassified | In that , I need to calculate the number of total sites , number of total Accessed Sites,number of total Denied Sites and number of total Overriden Sites based on the particular Virus_Category,UserName,Date How can form the query to achieve that?? I have used the following query but the total site is not correctly displayed.. select count(a.UserName),sum(b.totalsites),sum(a.Allow),sum(a.Denied),sum(a.Over),sum(b.totalconn) from (select a.UserName,sum(a.Allow) as Allow,sum(a.Denied) as Denied,sum(a.Over) as Over from (select UserName,case Status when 'A' then count(distinct SiteName) else 0 END as Allow ,case Status when 'D' then count(distinct SiteName) else 0 END as Denied,case Status when 'O' then count(distinct SiteName) else 0 END as Over from AccessDetails where Virus_category = 'unclassified ' and Date='2008-04-16' and Date='2008-04-16' and ScanType='C' group by UserName, Status) a group by a.UserName) a left join (select UserName,count(distinct SiteName)as totalsites, count(Time)as totalconn from AccessDetails where Virus_category = 'unclassified ' and Date='2008-04-16' and Date='2008-04-16' and ScanType='C' and Virus_category '-' and UserName '-' group by UserName)b on a.UserName=b.UserName where b.totalsites is not null Thanks In Advance ,
Re: Query problem
On Wed, Apr 16, 2008 at 4:35 AM, sivasakthi [EMAIL PROTECTED] wrote: Hi all, Iam having the one table name called AccessDetails and data inside that tables is following, [snip=schema] In that , I need to calculate the number of total sites , number of total Accessed Sites,number of total Denied Sites and number of total Overriden Sites based on the particular Virus_Category,UserName,Date How can form the query to achieve that?? I have used the following query but the total site is not correctly displayed.. You may want to look into the ROLLUP modifier. Here's the manual entry: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query problem
Hi, I have built a site with Dreamweaver and I have a problem with a query. I am trying to pass a parameter from one page to another to drill down. Basically, I have one product entry that is in multiple categories on my website. So, say it's a dress, it is therefore related to category 1 which is 'Girls', but it is also more specifically related to category 2 which is 'Girls Dresses'. The way I have set this up is to have a column called MultiCategoryID that holds both the number 1 and 2 like this: /1/2/ When a user clicks a link to look at dresses, the parameter 2 is passed, but my query on the result page is wrong in some way because no records are displaying even though there is content to display. This is what I have so far: SELECT * FROM Products WHERE MultiCategoryID LIKE '/catdrill/' ORDER BY ProductID DESC The parameter settings are: Name: catdrill Type: Numeric Value: Request(MCID) MCID is the url parameter being passed Default value: 2 Only when I test the Default value with an exact match of /1/2/ does any product display. What have I done wrong here? Is there a way to get it to recognise that I want it to pick specific numbers between the slashes rather than the whole lot? I have tried to change the slashes to full stops just in case they are causing problems, but it's still giving the same problem. Thanks. Mat
Re: SQL query problem
Dear Mat, Your mail is not very clear. But I have a feeling that using '%' wildcard in the like operand should help you Regards, Ravi. On 11/14/07, Matthew Stuart [EMAIL PROTECTED] wrote: Hi, I have built a site with Dreamweaver and I have a problem with a query. I am trying to pass a parameter from one page to another to drill down. Basically, I have one product entry that is in multiple categories on my website. So, say it's a dress, it is therefore related to category 1 which is 'Girls', but it is also more specifically related to category 2 which is 'Girls Dresses'. The way I have set this up is to have a column called MultiCategoryID that holds both the number 1 and 2 like this: /1/2/ When a user clicks a link to look at dresses, the parameter 2 is passed, but my query on the result page is wrong in some way because no records are displaying even though there is content to display. This is what I have so far: SELECT * FROM Products WHERE MultiCategoryID LIKE '/catdrill/' ORDER BY ProductID DESC The parameter settings are: Name: catdrill Type: Numeric Value: Request(MCID) MCID is the url parameter being passed Default value: 2 Only when I test the Default value with an exact match of /1/2/ does any product display. What have I done wrong here? Is there a way to get it to recognise that I want it to pick specific numbers between the slashes rather than the whole lot? I have tried to change the slashes to full stops just in case they are causing problems, but it's still giving the same problem. Thanks. Mat
Insert Select query problem
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Thanks
Re: Insert Select query problem
Ed Reed wrote:
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Try this:
INSERT INTO purchase (Source, Item, Qty)
SELECT
1, totals.Item, r.Qty
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty = totals.TotQty
UNION ALL
SELECT
0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
cheers,
Jay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Select query problem
Thanks Jay,
I had to make a change to the first part of the query to get the results that I
wanted but your suggestion was definitely what I needed to get to the solution.
Thanks again.
For those that are interested, here's the final solution,
INSERT INTO purchase (Source, Item, Qty)
SELECT 1, totals.Item,if((totals.TotQty -r.Qty)0,r.qty, totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
UNION All
SELECT 0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
On 8/10/07 at 12:33 PM, in message [EMAIL PROTECTED], Jay Pipes [EMAIL
PROTECTED] wrote:
Ed Reed wrote:
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Try this:
INSERT INTO purchase (Source, Item, Qty)
SELECT
1, totals.Item, r.Qty
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty = totals.TotQty
UNION ALL
SELECT
0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
cheers,
Jay
Query problem
I have a table of properties that is linked to a table f images with a one
property to many images relationship. I have manged this with nested queries
but want to try and do it on one line. My current query
$query = SELECT * FROM images, properties WHERE images.property_id =
properties.property_id;
As you can see from the query this returns a row for every image so if a
property has 3 images associated with it it will be returned 3 times.
Thanks,
CREATE TABLE `images` (
`id` int(10) unsigned NOT NULL auto_increment,
`property_id` varchar(10) default NULL,
`name` varchar(30) NOT NULL default '',
`type` varchar(30) NOT NULL default '',
`size` int(11) NOT NULL default '0',
`position` int(10) unsigned NOT NULL default '0',
`title` varchar(100) NOT NULL,
`img_url` varchar(200) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=264 ;
CREATE TABLE `properties` (
`property_id` varchar(20) NOT NULL,
`postcode` varchar(20) default NULL,
`address` varchar(200) default NULL,
`short_desc` varchar(500) default NULL,
`long_desc` varchar(500) default NULL,
`latitude` double(100,20) default NULL,
`longitude` double(100,20) default NULL,
`rent` varchar(50) default NULL,
`available_from` date default NULL,
`rent_type` varchar(255) default NULL,
`double_rooms` int(2) default NULL,
`single_rooms` int(2) default NULL,
`twin_rooms` int(2) default NULL,
`additional_rooms` varchar(500) default 'on',
`features` varchar(500) default NULL,
`status` enum('off','on') default 'on',
PRIMARY KEY (`property_id`)
)
t: 0131 553 3935 | m:07816 996 930 | [EMAIL PROTECTED] |
http://www:blue-fly.co.uk
Re: Query problem
I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote: I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? I think he somehow wants to return each property once only but still have every image returned in the result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
No I want all the properties only one regardless of how many images are attached to them. Think I need a distinct in there somewhere, - Original Message - From: Jon Ribbens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 09, 2007 6:56 PM Subject: Re: Query problem On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote: I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? I think he somehow wants to return each property once only but still have every image returned in the result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
how do I return a single row per property even if it has 3 or 4 images attached to it. Please reply to the list instead of directly to me. You could do a: select p.from properties p where exists (select i.* from images i where i.property_id = p.property_id) I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow seach - Possible better query PROBLEM SOLVED
Dan, Actually you were on the right track. I changed your suggested query to the following and it seems to work and is a lot quicker. SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW (), INTERVAL 48 HOUR) AND mainthread = 'T' ORDER BY updated DESC LIMIT 50 Thanks. Al On Oct 31, 2006, at 4:01 PM, Albert Padley wrote: Dan, On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote: Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This query won't work. The table contains threaded messages. The thread_id refers back to the id of the first message in the thread. The above query would return a list of all messages in the last 48 hours when all I want is to return the original message in the thread. This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... In our test database it's probably less than 50 rows that have been updated in the last 48 hours. Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. 1 PRIMARY mrldisc index updated 4 4888Using where 2 DEPENDENT SUBQUERY mrldisc index_subquery thread_id,updated thread_id 5 func 8 Using index; Using where Thanks. Al Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On 8/3/06, André Hänsel [EMAIL PROTECTED] wrote: Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? SELECT download FROM table WHERE username='user' ORDER BY time DESC LIMIT 5; -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
For a specific username: SELECT username, time, download FROM table WHERE username = 'someusername' ORDER BY time DESC LIMIT 5 Dan On 8/3/06, André Hänsel [EMAIL PROTECTED] wrote: Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Query problem
Hi Dan, hi Obed, of course I have no specific username, I want the last 5 downloads of each distinct username in the table. :) Regards, André -Ursprüngliche Nachricht- Von: Dan Buettner [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. August 2006 20:15 An: André Hänsel Cc: mysql@lists.mysql.com Betreff: Re: Query problem For a specific username: SELECT username, time, download FROM table WHERE username = 'someusername' ORDER BY time DESC LIMIT 5 Dan On 8/3/06, André Hänsel [EMAIL PROTECTED] wrote: Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: Query problem
SELECT DISTINCT username, time, download FROM table ORDER BY time DESC GROUP BY username André Hänsel wrote: Hi Dan, hi Obed, of course I have no specific username, I want the last 5 downloads of each distinct username in the table. :) Regards, André -Ursprüngliche Nachricht- Von: Dan Buettner [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. August 2006 20:15 An: André Hänsel Cc: mysql@lists.mysql.com Betreff: Re: Query problem For a specific username: SELECT username, time, download FROM table WHERE username = 'someusername' ORDER BY time DESC LIMIT 5 Dan On 8/3/06, André Hänsel [EMAIL PROTECTED] wrote: Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
At 03:08 PM 8/3/2006, André Hänsel wrote: Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Correlated subquery, which can be the devil to debug, and don't run really quickly. I have no idea if this will work: SELECT t1.time, t1.username, t1.download FROM downloads AS t1 WHERE t1.username = ANY (SELECT t2.username FROM downloads AS t2 WHERE t2.username = t1.username) ORDER BY t1.time DESC LIMIT 5 Hmmm, that's just going to return 5 records; you need 5 or fewer for each username. That's almost like creating a view of users, then stepping through the view, selecting * limit 5 where username = view.username. See where that's headed? You may need a temporary table. Sorry I've not been more help. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Query problem
-Ursprüngliche Nachricht- Von: Miles Thompson [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. August 2006 21:56 An: mysql@lists.mysql.com Betreff: Re: Query problem At 03:08 PM 8/3/2006, André Hänsel wrote: I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. That's almost like creating a view of users, then stepping through the view, selecting * limit 5 where username = view.username. See where that's headed? You may need a temporary table. Assuming I have a (temporary) table of usernames, how can that be of any help? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: Query problem
André Hänsel wrote: -Ursprüngliche Nachricht- Von: Miles Thompson [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. August 2006 21:56 An: mysql@lists.mysql.com Betreff: Re: Query problem At 03:08 PM 8/3/2006, André Hänsel wrote: I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. That's almost like creating a view of users, then stepping through the view, selecting * limit 5 where username = view.username. See where that's headed? You may need a temporary table. Assuming I have a (temporary) table of usernames, how can that be of any help? all right, here's how this goes. Create a stored procedure WHERE YOU SELECT DISTINCT username. Then for each user, retrieve the five 5. Put them in a union, enjoy. -- John Meyer http://pueblonative.wordpress.com http://pueblonative.110mb.com/board -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On 8/3/06, André Hänsel [EMAIL PROTECTED] wrote: Hi Dan, hi Obed, of course I have no specific username, I want the last 5 downloads of each distinct username in the table. :) i was thinking a lot... and i can't find the solution but maybe yo can do somthing like this select user,download from table where user in (select distinct user from tabla) order by time desc; and in your front-end just display 5 for each user xD i you find the solution please let us know ! good luck -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query problem
Barry wrote: Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? IF!!! you need IF!! :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query problem
Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select query problem
Hi I have one problem that i can`t resolve. I have 3 tables TABLE `predmet` ( `PredmetID` int(10) unsigned NOT NULL auto_increment, `BrojPredmeta` int(10) unsigned NOT NULL default '0', `VrstaPredmetaID` int(10) unsigned NOT NULL default '0', `KorisnikID` int(10) unsigned NOT NULL default '0', `GrupaID` int(10) unsigned NOT NULL default '0', `PodgrupaID` int(10) unsigned NOT NULL default '0', `DatumZaduzenja` date NOT NULL default '-00-00', `DatumRazduzenja` date default NULL, `DatumUrgencije` date default NULL, `Komentar` text, TABLE `predmet_referent` ( `PredmetID` int(10) unsigned NOT NULL default '0', `VrstaPredmetaID` int(10) unsigned NOT NULL default '0', `KorisnikID` int(10) unsigned NOT NULL default '0', `GrupaID` int(10) unsigned NOT NULL default '0', `PodgrupaID` int(10) unsigned NOT NULL default '0', `ReferentID` int(10) unsigned NOT NULL default '0', which reference all from table predmet and CREATE TABLE `referent` ( `ReferentID` int(10) unsigned NOT NULL auto_increment, `SifraReferenta` varchar(2) NOT NULL default '', `ImeReferenta` varchar(30) NOT NULL default '', `PrezimeReferenta` varchar(45) NOT NULL default '', PRIMARY KEY (`ReferentID`) My table predmet_referent looks like this PredmetID VrstaPredmetaID KorisnikID GrupaID PodgrupaID ReferentID 1, 1, 18, 4, 4, 1 1, 1, 18, 4, 4, 2 2, 1, 21, 6, 3, 2 2, 1, 21, 6, 3, 3 3, 1, 22, 5, 1, 2 3, 1, 22, 5, 1, 1 3, 1, 22, 5, 1, 3 4, 3, 23, 6, 3, 1 4, 3, 23, 6, 3, 2 4, 3, 23, 6, 3, 3 and table referent looks like ReferentID SifraReferenta ImeReferenta PrezimeReferenta 1, '01', 'Nada', 'Nadi#263;' 2, '03', 'Goran', 'Gavran#269;i#263;' 3, '04', 'Dragan', 'PeriÅ¡iÄ#135;' I want to make select query so thatt result from that query look something like this PredmetID BrojPredmeta Referent1 Referent2 Referent3 1 121215 12 2 121355 23 3 236564 21 3 4 213545 12 3 How can i do this?This is very importat for me Any help will be great. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Setup TITLES: TITLE_ID AUTHORS: AUTHOR_ID TITLE_AUTHOR: (TITLE_ID,AUTHOR_ID) Problem: Given a title, I need to find all the authors who aren't connected with that particular book. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
- Original Message - From: John Meyer [EMAIL PROTECTED] To: List: MySQL mysql@lists.mysql.com Sent: Tuesday, May 30, 2006 5:09 PM Subject: Query problem Setup TITLES: TITLE_ID AUTHORS: AUTHOR_ID TITLE_AUTHOR: (TITLE_ID,AUTHOR_ID) Problem: Given a title, I need to find all the authors who aren't connected with that particular book. That's a pretty odd requirement, I must say. If your database has thousands or millions of books, you'd have to assume that virtually ALL of the authors in the database are NOT connected with a particular book. I'm trying to think of a situation where that list of people who were not connected with the book was actually useful Okay, maybe if you were looking for authors who could review the book, the query you want could be useful for identifying potential reviewers. Even if that was thousands of authors, it's still a smaller list than the list of all human beings on Earth :-) In any case, the query is pretty simple assuming you are using a version of MySQL which supports subqueries: select AUTHOR_ID from AUTHORS where AUTHOR_ID not in (select AUTHOR_ID from TITLE_AUTHOR where TITLE_ID = 123) You simply plug in the title_id of the book in question in place of 123 and you're all set. If I were creating the database, I would have an id _and_ an author name in the AUTHORS table and I'd have an id _and_ a title in the TITLES table. Then I'd modify the query above to do joins so that the result showed me the author names and searched so that I was looking for the book title, not the book id. But I'm guessing that you already have that in mind and just simplified the question to get the bare essence of it. Or maybe you only have a few dozen books and will quickly memorize the author names and titles that go with each author id and title id. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 29/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
Rhino wrote: - Original Message - From: John Meyer [EMAIL PROTECTED] To: List: MySQL mysql@lists.mysql.com Sent: Tuesday, May 30, 2006 5:09 PM Subject: Query problem Setup TITLES: TITLE_ID AUTHORS: AUTHOR_ID TITLE_AUTHOR: (TITLE_ID,AUTHOR_ID) Problem: Given a title, I need to find all the authors who aren't connected with that particular book. That's a pretty odd requirement, I must say. If your database has thousands or millions of books, you'd have to assume that virtually ALL of the authors in the database are NOT connected with a particular book. Actually, this is more along the lines of a PHP form where I am adding an author to a title when the title is in the database. I want to create a select list where the current authors in the database are shown, but not the ones already associated with that title. Oh yeah, and one other thing, title and book are not synonymous in this one. I'll give you the breakdown: TITLES: TITLE_ID AUTHORS: AUTHOR_ID TITLE_AUTHOR: (TITLE_ID,AUTHOR_ID) BOOKS: BOOK_ID TITLE_BOOK: (BOOK_ID,TITLE_ID) EDITORS: (BOOK_ID,AUTHOR_ID) Complex enough for you? ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query problem: UNION in subquery
Hi Luke..
Try this
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')
UNION
SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))as abc
Cheers :)
Neeraj Black Bits
-Original Message-
From: Luke [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 24, 2006 9:36 AM
To: mysql@lists.mysql.com
Subject: Query problem: UNION in subquery
Hello!
I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.
The question is about the right syntax.
1.
This works fine /UNION/
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
2.
This works fine too /subquery/:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS
SubTable1;
3.
But when I run 12 combined I get in troubles. This is a query draft,
can't come up with the right syntax:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
I tried many combinations and got various syntax errors. Any ideas?
Thanks,
Luke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem: UNION in subquery
A big Thank you goes to you! That was it!
Looks like I tried with too many parentheses i.e.
/this is wrong/
SELECT FROM ...
(
(SELECT FROM ...)
UNION
(SELECT FROM ...)
) AS abc
Regards,
Luke
- Original Message -
From: Neeraj [EMAIL PROTECTED]
To: 'Luke' [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, May 24, 2006 2:16 AM
Subject: RE: Query problem: UNION in subquery
Hi Luke..
Try this
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')
UNION
SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))as abc
Cheers :)
Neeraj Black Bits
-Original Message-
From: Luke [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 24, 2006 9:36 AM
To: mysql@lists.mysql.com
Subject: Query problem: UNION in subquery
Hello!
I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.
The question is about the right syntax.
1.
This works fine /UNION/
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
2.
This works fine too /subquery/:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS
SubTable1;
3.
But when I run 12 combined I get in troubles. This is a query draft,
can't come up with the right syntax:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
I tried many combinations and got various syntax errors. Any ideas?
Thanks,
Luke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem: UNION in subquery
Hello!
I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.
The question is about the right syntax.
1.
This works fine /UNION/
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
2.
This works fine too /subquery/:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS
SubTable1;
3.
But when I run 12 combined I get in troubles. This is a query draft,
can't come up with the right syntax:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
I tried many combinations and got various syntax errors. Any ideas?
Thanks,
Luke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
On Wed, 8 Mar 2006 10:12:22 - [EMAIL PROTECTED] wrote:
snip one column select query
but I have two other filters which may or may not be chosen. (area, and
interest).
$query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND
area='area' AND interest='interest' ORDER BY fname $type;
but what if nether is chosen, or only one? Is there an AND/OR operator or
similar in mysql?
Your app needs to build the query.
In my libsql.php file I have:
function andclause($qry, $fld, $val, $op='=') {
$fmt = %s %s $op '%s';
$qry .= sprintf($fmt,
( preg_match('!\bWHERE\b!mi', $qry) ? 'AND' : 'WHERE'), $fld, $val);
return $qry;
}
With this, you can construct your initial query:
$qry = SELECT * FROM foo WHERE blah LIKE '$baz%';
// then test, case by case, to see if you need more selection clauses:
if (! empty($area))
$qry = andclause($qry, 'area', $area);
if (! empty($interest))
$qry = andclause($qry, 'interest', $interest);
echo 'span class=ddt', $qry, '/span';
$res = SQLQuery($qry);
...
Have fun.
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query problem
I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query problem
I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross
Re: query problem
one solution (may not be the best but would work) would be to use 'like' instead of '=' and then put wildcards %%$var % around the variable so that if it is not there then it wount effect the query. Ade [EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
On 3/8/06, Adrian Bruce [EMAIL PROTECTED] wrote: one solution (may not be the best but would work) would be to use 'like' instead of '=' and then put wildcards %%$var % around the variable so that if it is not there then it wount effect the query. Yeah, I use this kind of trick for SELECTs based on user submited forms. So your query would be: SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area LIKE '%$area%' AND interest LIKE '%$interest%' ORDER BY fname $type; Ade [EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
[EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? No AND/OR. It's the job of the front-end, ie your app, to assemble to correct number of Where clauses. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
You originally mention your UNION doesn't work but you did not specify the query. This is a simple or query, or union. You can do either: select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4); or select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=2 UNION select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=4; Please let me know if it's more complex than that, but when I ran it on my test data I got +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 60 | 2 |1 | | 60 | 4 |1 | | 61 | 2 |1 | | 61 | 4 |1 | +-+---+--+ for both results. -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes. Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP TABLE IF EXISTS `Sizes`; CREATE TABLE `Sizes` ( `idsizes` int(10) unsigned NOT NULL auto_increment, `size` char(4) default NULL, `jpSize` char(4) default NULL, PRIMARY KEY (`idsizes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; On 2/9/06, sheeri kritzer [EMAIL PROTECTED] wrote: Hi Conor, The table you showed us has 2 primary keys, which is not possible. Can you do a SHOW CREATE TABLE on *each* table? -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Hello, I'm having a hell of a time figuring this query out, maybe someone can point me in the right direction. I have a table which lists the case configurations for cases of items. Each case will have a different combination of sizes e.g. Case 002 has 2 size 5, 2 size 7 and 2 size 8 items. What I'm trying to do is, given a group of sizes and quantities how can I find the corresponding ID number for a case. Here's the description of the table. +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | CaseType_idCaseType | int(10) unsigned | NO | PRI | | | | Sizes_idsizes | int(10) unsigned | NO | PRI | | | | qty | int(10) unsigned | YES | | NULL| | +-+--+--+-+-+---+ Here's what I see if I do a select on a CaseType id. select * from CaseType_has_Sizes where CaseType_idCaseType = 61; +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 61 | 2 |1 | | 61 | 4 |1 | | 61 | 6 |1 | | 61 | 8 |1 | | 61 |24 |1 | | 61 |26 |1 | +-+---+--+ I'm essentially trying to get the same resuts as this select, but in reverse I've tried using UNION but it doesnt seem to get what I'm trying for. Any help appreciated. Conor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
Sheeri, Thanks for the help. I tried your sample queries, but they dont really return what I'm looking for. I think I've found a solution though. Given the contents of a case, I'm looking for a unique case id, basicially I want to search for a case if it exists once I've decided the configuration I want. What I'm doing now is I alias the table I'm searching on for each Size, Qty combination, and create a self join on this and the Case_Type_idCaseType id. Here's an example of what I'm doing, I know CaseType_idCaseType '1' exists, and that it's make up is: +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 1 |10 |1 | | 1 |11 |1 | | 1 |12 |1 | | 1 |13 |1 | | 1 |14 |1 | | 1 |15 |1 | +-+---+--+ Taking these Sizes_idSizes and qty combinations I want to see if I can find the CaseType_idCaseType by itself. SELECT c10.CaseType_idCaseType AS case_id FROM CaseType_has_Sizes AS c10, CaseType_has_Sizes AS c11, CaseType_has_Sizes AS c12, CaseType_has_Sizes AS c13, CaseType_has_Sizes AS c14, CaseType_has_Sizes AS c15 WHERE c10.Sizes_idsizes = 10 AND c10.qty = 1 AND c11.Sizes_idsizes = 11 AND c11.qty = 1 AND c12.Sizes_idsizes = 12 AND c12.qty = 1 AND c13.Sizes_idsizes = 13 AND c13.qty = 1 AND c14.Sizes_idsizes = 14 AND c14.qty = 1 AND c15.Sizes_idsizes = 15 AND c15.qty = 1 AND c10.CaseType_idCaseType = c11.CaseType_idCaseType AND c11.CaseType_idCaseType = c12.CaseType_idCaseType AND c12.CaseType_idCaseType = c13.CaseType_idCaseType AND c13.CaseType_idCaseType = c14.CaseType_idCaseType AND c14.CaseType_idCaseType = c15.CaseType_idCaseType; It's a hell of a query, and I'm sure there's a better way to do it, but it will give me what I'm looking for. +-+ | case_id | +-+ | 1 | | 19 | | 37 | | 42 | | 44 | | 76 | | 110 | | 157 | | 552 | +-+ It's returned 9 cases on this query, but from testing it out, the first case_id returned is the one I'm looking for, all the other cases are ones that are larger than the one i searched on that include the size, qty combinations I specified in the query. If I add a 'LIMIT 1' at the end it will return only the case_id I'm looking for. As I said, I'm sure there's a better way to do this, and if anyone has any suggestions I'd only be happy to listen. Conor On 2/10/06, sheeri kritzer [EMAIL PROTECTED] wrote: You originally mention your UNION doesn't work but you did not specify the query. This is a simple or query, or union. You can do either: select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4); or select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=2 UNION select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=4; Please let me know if it's more complex than that, but when I ran it on my test data I got +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 60 | 2 |1 | | 60 | 4 |1 | | 61 | 2 |1 | | 61 | 4 |1 | +-+---+--+ for both results. -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes. Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP
query problem
Hello, I'm having a hell of a time figuring this query out, maybe someone can point me in the right direction. I have a table which lists the case configurations for cases of items. Each case will have a different combination of sizes e.g. Case 002 has 2 size 5, 2 size 7 and 2 size 8 items. What I'm trying to do is, given a group of sizes and quantities how can I find the corresponding ID number for a case. Here's the description of the table. +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | CaseType_idCaseType | int(10) unsigned | NO | PRI | | | | Sizes_idsizes | int(10) unsigned | NO | PRI | | | | qty | int(10) unsigned | YES | | NULL| | +-+--+--+-+-+---+ Here's what I see if I do a select on a CaseType id. select * from CaseType_has_Sizes where CaseType_idCaseType = 61; +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 61 | 2 |1 | | 61 | 4 |1 | | 61 | 6 |1 | | 61 | 8 |1 | | 61 |24 |1 | | 61 |26 |1 | +-+---+--+ I'm essentially trying to get the same resuts as this select, but in reverse I've tried using UNION but it doesnt seem to get what I'm trying for. Any help appreciated. Conor
Re: query problem
Hi Conor, The table you showed us has 2 primary keys, which is not possible. Can you do a SHOW CREATE TABLE on *each* table? -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Hello, I'm having a hell of a time figuring this query out, maybe someone can point me in the right direction. I have a table which lists the case configurations for cases of items. Each case will have a different combination of sizes e.g. Case 002 has 2 size 5, 2 size 7 and 2 size 8 items. What I'm trying to do is, given a group of sizes and quantities how can I find the corresponding ID number for a case. Here's the description of the table. +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | CaseType_idCaseType | int(10) unsigned | NO | PRI | | | | Sizes_idsizes | int(10) unsigned | NO | PRI | | | | qty | int(10) unsigned | YES | | NULL| | +-+--+--+-+-+---+ Here's what I see if I do a select on a CaseType id. select * from CaseType_has_Sizes where CaseType_idCaseType = 61; +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 61 | 2 |1 | | 61 | 4 |1 | | 61 | 6 |1 | | 61 | 8 |1 | | 61 |24 |1 | | 61 |26 |1 | +-+---+--+ I'm essentially trying to get the same resuts as this select, but in reverse I've tried using UNION but it doesnt seem to get what I'm trying for. Any help appreciated. Conor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes. Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP TABLE IF EXISTS `Sizes`; CREATE TABLE `Sizes` ( `idsizes` int(10) unsigned NOT NULL auto_increment, `size` char(4) default NULL, `jpSize` char(4) default NULL, PRIMARY KEY (`idsizes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; On 2/9/06, sheeri kritzer [EMAIL PROTECTED] wrote: Hi Conor, The table you showed us has 2 primary keys, which is not possible. Can you do a SHOW CREATE TABLE on *each* table? -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Hello, I'm having a hell of a time figuring this query out, maybe someone can point me in the right direction. I have a table which lists the case configurations for cases of items. Each case will have a different combination of sizes e.g. Case 002 has 2 size 5, 2 size 7 and 2 size 8 items. What I'm trying to do is, given a group of sizes and quantities how can I find the corresponding ID number for a case. Here's the description of the table. +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | CaseType_idCaseType | int(10) unsigned | NO | PRI | | | | Sizes_idsizes | int(10) unsigned | NO | PRI | | | | qty | int(10) unsigned | YES | | NULL| | +-+--+--+-+-+---+ Here's what I see if I do a select on a CaseType id. select * from CaseType_has_Sizes where CaseType_idCaseType = 61; +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 61 | 2 |1 | | 61 | 4 |1 | | 61 | 6 |1 | | 61 | 8 |1 | | 61 |24 |1 | | 61 |26 |1 | +-+---+--+ I'm essentially trying to get the same resuts as this select, but in reverse I've tried using UNION but it doesnt seem to get what I'm trying for. Any help appreciated. Conor
Re: Interesting Query Problem
Hello. Perhaps this will work (depends on the version of MySQL you're using): select question_id , count(*) from Records group by question_id having question_id not in ( select distinct question_id from Records r where member_id = @current_member_id); @current_member_id equals to current_user G G wrote: Hello, I have a simple Records table with two columns, member_id and question_id. The object of the query is to retrieve the question_id, as well as how many times it's been answered - as long as the current user hasn't answered it (member_id). So, the query shouldn't return any question_id's (and counts) if it has been answered by the current user. Right now I have this: SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY question_id; I've tried throwing in different variants of 'WHERE member_id != X', but all that seems to return is the count of questions answered, minus the amount of times the particular user has answered them. For example, if user X has answered a question that had been answered another 50 times, my query will still return that question_id, but with a count of 49. Your help is appreciated in advance. Thanks! Kind Regards, Gerald Glickman G2 Innovations.com, Inc. http://www.g2innovations.com http://www.g2innovations.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting Query Problem
Hi, An alternative for any MySQL version (from 3.23.??) would be: SELECT r1.question_id,count(r1.member_id) FROM Records r1 LEFT JOIN Records r2 ON r1.question_id=r2.question_id AND r2.member_id=member_id WHERE r2.question_id IS NULL; member_id must be the member name. mpneves On Thursday 19 January 2006 11:18, Gleb Paharenko wrote: Hello. Perhaps this will work (depends on the version of MySQL you're using): select question_id , count(*) from Records group by question_id having question_id not in ( select distinct question_id from Records r where member_id = @current_member_id); @current_member_id equals to current_user G G wrote: Hello, I have a simple Records table with two columns, member_id and question_id. The object of the query is to retrieve the question_id, as well as how many times it's been answered - as long as the current user hasn't answered it (member_id). So, the query shouldn't return any question_id's (and counts) if it has been answered by the current user. Right now I have this: SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY question_id; I've tried throwing in different variants of 'WHERE member_id != X', but all that seems to return is the count of questions answered, minus the amount of times the particular user has answered them. For example, if user X has answered a question that had been answered another 50 times, my query will still return that question_id, but with a count of 49. Your help is appreciated in advance. Thanks! Kind Regards, Gerald Glickman G2 Innovations.com, Inc. http://www.g2innovations.com http://www.g2innovations.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interesting Query Problem
Hello, I have a simple Records table with two columns, member_id and question_id. The object of the query is to retrieve the question_id, as well as how many times it's been answered - as long as the current user hasn't answered it (member_id). So, the query shouldn't return any question_id's (and counts) if it has been answered by the current user. Right now I have this: SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY question_id; I've tried throwing in different variants of 'WHERE member_id != X', but all that seems to return is the count of questions answered, minus the amount of times the particular user has answered them. For example, if user X has answered a question that had been answered another 50 times, my query will still return that question_id, but with a count of 49. Your help is appreciated in advance. Thanks! Kind Regards, Gerald Glickman G2 Innovations.com, Inc. http://www.g2innovations.com http://www.g2innovations.com/
Delete query problem
Hello, I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master record has details. MySQL versions 4.1.10 and higher. Could someone help me, please, to create such a query? Example: CREATE TABLE `master_tbl` ( `ID` int(10) unsigned NOT NULL auto_increment, `Desc` varchar(45) NOT NULL default '', `Data` varchar(45) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=InnoDB; CREATE TABLE `detail_tbl` ( `Master_ID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL default '0', `Desc` varchar(45) NOT NULL default '', PRIMARY KEY (`Master_ID`) ) ENGINE=InnoDB; This query works only for master records which have at least one detail record: DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10 Thanks in advance Dusan Pavlica
Re: Delete query problem
What about DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON master_tbl.ID=detail_tbl.ID WHERE . T.R. Dušan Pavlica napsal(a): Hello, I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master record has details. MySQL versions 4.1.10 and higher. Could someone help me, please, to create such a query? Example: CREATE TABLE `master_tbl` ( `ID` int(10) unsigned NOT NULL auto_increment, `Desc` varchar(45) NOT NULL default '', `Data` varchar(45) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=InnoDB; CREATE TABLE `detail_tbl` ( `Master_ID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL default '0', `Desc` varchar(45) NOT NULL default '', PRIMARY KEY (`Master_ID`) ) ENGINE=InnoDB; This query works only for master records which have at least one detail record: DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10 Thanks in advance Dusan Pavlica -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete query problem
On 21/12/2005, Dušan Pavlica wrote: Hello, I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master record has details. MySQL versions 4.1.10 and higher. Could someone help me, please, to create such a query? Example: CREATE TABLE `master_tbl` ( `ID` int(10) unsigned NOT NULL auto_increment, `Desc` varchar(45) NOT NULL default '', `Data` varchar(45) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=InnoDB; CREATE TABLE `detail_tbl` ( `Master_ID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL default '0', `Desc` varchar(45) NOT NULL default '', PRIMARY KEY (`Master_ID`) ) ENGINE=InnoDB; You realize you can only have one detail record per Master_ID (since you made Master_ID the PK in the detail table)? Since you are using InnoDB, you should enforce referential integrity with referential actions: CREATE TABLE `detail_tbl` ( `Master_ID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL default '0', `Desc` varchar(45) NOT NULL default '', PRIMARY KEY (`Master_ID`), FOREIGN KEY (`Master_ID`) REFERENCES `master_tbl` (`ID`) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB; That way, you only have to DELECT FROM master_tbl WHERE Master_ID = 10; to delete both master and detail records. -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete query problem
Tomas, thanks that was the solution I couldn't find out. I had to change only ON clause to master_tbl.ID=detail_tbl.Master_ID . Thanks also to Felix, I know I could use referential integrity but I knew there is a way how to do it without it and I was trying to find that way. Dusan. - Original Message - From: Tomas Rasek [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 21, 2005 11:12 AM Subject: Re: Delete query problem What about DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON master_tbl.ID=detail_tbl.ID WHERE . T.R. Dušan Pavlica napsal(a): Hello, I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master record has details. MySQL versions 4.1.10 and higher. Could someone help me, please, to create such a query? Example: CREATE TABLE `master_tbl` ( `ID` int(10) unsigned NOT NULL auto_increment, `Desc` varchar(45) NOT NULL default '', `Data` varchar(45) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=InnoDB; CREATE TABLE `detail_tbl` ( `Master_ID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL default '0', `Desc` varchar(45) NOT NULL default '', PRIMARY KEY (`Master_ID`) ) ENGINE=InnoDB; This query works only for master records which have at least one detail record: DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10 Thanks in advance Dusan Pavlica -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert query problem
You can use the back quote(`) to escape the field name. It's the ~ key on the keyboard. tickets(`from`,`department`,... If you type SHOW CREATE TABLE tickets, you'll see mysql escapes all the field names to avoid reserve word conflicts. On Nov 29, 2005, at 2:14 PM, Rhino wrote: - Original Message - From: Jeff [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 29, 2005 11:42 AM Subject: Insert query problem All, I can't get this query to run, it keeps compaining that there is a problem: The Query: insert into tickets (id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'[EMAIL PROTECTED] [EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') The error: You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,status,cre ate d,fromnam' at line 1 I suspect it doesn't like the fact that I have a field named from but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname from. One of my least favourite aspects of MySQL is the vague error messages, like the one you quote in your post. I keep hoping that they will make them much clearer but it doesn't seem to have happened yet In any case, you may well be right that MySQL doesn't like you having a column named 'from'. Most dialects of SQL tolerate that kind of thing but usually require escape characters of some kind around words like 'from', which are keywords, when they are used outside of their normal purpose. I've never had the desire to call a column 'from' so I've never learned the escape character technique for MySQL so I would suggest the following possibilities: - change your column name so that _isn't_ a keyword. That would be my first choice if it was my database. - search the MySQL manual for 'escape character' and/or 'keyword'; with a bit of luck, it is documented there somewhere - search the MySQL mailing list archives. I'm virtually positive I've seen the matter come up in the list but I just don't remember the escape syntax at the moment. If you escape the column named 'from' and the query still doesn't work, please repost so that we can investigate other possibilities. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/186 - Release Date: 29/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert query problem
All, I can't get this query to run, it keeps compaining that there is a problem: The Query: insert into tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'[EMAIL PROTECTED] [EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') The error: You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,status,create d,fromnam' at line 1 I suspect it doesn't like the fact that I have a field named from but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname from. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert query problem
Jeff [EMAIL PROTECTED] wrote on 11/29/2005 11:42:34 AM: All, I can't get this query to run, it keeps compaining that there is a problem: The Query: insert into tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'[EMAIL PROTECTED] [EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') The error: You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,status,create d,fromnam' at line 1 I suspect it doesn't like the fact that I have a field named from but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname from. Thanks, Jeff You have to use backticks: `from` http://dev.mysql.com/doc/refman/4.1/en/legal-names.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Insert query problem [solved]
Nevermind, found that usig `from` works. Jeff -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 29, 2005 11:43 To: mysql@lists.mysql.com Subject: Insert query problem All, I can't get this query to run, it keeps compaining that there is a problem: The Query: insert into tickets(id,from,department,subject,body,lastaction,lastpost,pr iority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'[EMAIL PROTECTED] [EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') The error: You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,sta tus,create d,fromnam' at line 1 I suspect it doesn't like the fact that I have a field named from but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname from. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert query problem
Jeff wrote: tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'[EMAIL PROTECTED] [EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') Use `from` (those are backticks) See: http://dev.mysql.com/doc/refman/5.0/en/legal-names.html kgt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query problem
Many Many wrong SQL syntax error check your symbol (') becareful it 'very
important .
You can do that.
Cheer!!
From: Jeff [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Insert query problem
Date: Tue, 29 Nov 2005 11:42:34 -0500
All,
I can't get this query to run, it keeps compaining that there is a
problem:
The Query:
insert into
tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta
tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
values(null,'jmckeon','1','test','test
test',unix_timestamp(now()),'[EMAIL PROTECTED]
[EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff
McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')
The error:
You have an error in your SQL syntax near
'from,department,subject,body,lastaction,lastpost,priority,status,create
d,fromnam' at line 1
I suspect it doesn't like the fact that I have a field named from but
I KNOW it's possible to write to this table and that field, I just can't
figure out the correct syntax to get it to accept the fieldname from.
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query problem
You can use backticks ( ` ) to escape the names: INSERT INTO `tickets` ( `id` , `from` , `departement` . hth, melanie From: Jeff [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Insert query problem Date: Tue, 29 Nov 2005 11:42:34 -0500 All, I can't get this query to run, it keeps compaining that there is a problem: The Query: insert into tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'[EMAIL PROTECTED] [EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') The error: You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,status,create d,fromnam' at line 1 I suspect it doesn't like the fact that I have a field named from but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname from. Thanks, Jeff _ MSN Messenger 7.5 is now out. Download it for FREE here. http://messenger.msn.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert query problem
- Original Message - From: Jeff [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 29, 2005 11:42 AM Subject: Insert query problem All, I can't get this query to run, it keeps compaining that there is a problem: The Query: insert into tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'[EMAIL PROTECTED] [EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') The error: You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,status,create d,fromnam' at line 1 I suspect it doesn't like the fact that I have a field named from but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname from. One of my least favourite aspects of MySQL is the vague error messages, like the one you quote in your post. I keep hoping that they will make them much clearer but it doesn't seem to have happened yet In any case, you may well be right that MySQL doesn't like you having a column named 'from'. Most dialects of SQL tolerate that kind of thing but usually require escape characters of some kind around words like 'from', which are keywords, when they are used outside of their normal purpose. I've never had the desire to call a column 'from' so I've never learned the escape character technique for MySQL so I would suggest the following possibilities: - change your column name so that _isn't_ a keyword. That would be my first choice if it was my database. - search the MySQL manual for 'escape character' and/or 'keyword'; with a bit of luck, it is documented there somewhere - search the MySQL mailing list archives. I'm virtually positive I've seen the matter come up in the list but I just don't remember the escape syntax at the moment. If you escape the column named 'from' and the query still doesn't work, please repost so that we can investigate other possibilities. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/186 - Release Date: 29/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query problem
Hello. You should solve the issue with server crashes. I'm not familiar with JDBC, and if changing the values of parameters with SET statement doesn't work with JDBC, probably somebody clever on the list could help you. You may forward your message to: http://lists.mysql.com/java Yes the server dies during the queries from JDBC. Regarding the client parameters, what if the client doesn't have mySQL installed? Amir Amir Shay [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query problem
Hi, Yes the server dies during the queries from JDBC. Regarding the client parameters, what if the client doesn't have mySQL installed? Amir Hello. Does SHOW STATUS executed from JDBC client return increased values of this parameters? Could your server die during queries from JDBC (check the error log)? Sometimes you should increase interactive_timeout as well. A lot of variables usually could be changed using SET statement. See: http://dev.mysql.com/doc/mysql/en/system-variables.html Sometimes when querying mySQL 4.1.11 on Linux machine I get the error Lost connection to MySQL server during query. Here are the symptoms 1.When running the query from the server it returns OK 2.When running the query from another machine using the mySQL query browser it returns OK 3.When running the query from another machine from Java using mySQL JDBC it fails with Lost connection to... 4.When running the query from another machine using software like EMS it fails with Lost connection to... 5.The error is always after 4.7 seconds and only in heavy queries (not only SELECT, even DELETES ) The problem is probably not in the communication parameters, like max_allowed_packet or connection_timeout on the server because I increased then Does anyone know how can I control those parameters from JDBC? Amir Shay [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com
Re: Lost connection to MySQL server during query problem
Hello. Does SHOW STATUS executed from JDBC client return increased values of this parameters? Could your server die during queries from JDBC (check the error log)? Sometimes you should increase interactive_timeout as well. A lot of variables usually could be changed using SET statement. See: http://dev.mysql.com/doc/mysql/en/system-variables.html Sometimes when querying mySQL 4.1.11 on Linux machine I get the error Lost connection to MySQL server during query. Here are the symptoms 1.When running the query from the server it returns OK 2.When running the query from another machine using the mySQL query browser it returns OK 3.When running the query from another machine from Java using mySQL JDBC it fails with Lost connection to... 4.When running the query from another machine using software like EMS it fails with Lost connection to... 5.The error is always after 4.7 seconds and only in heavy queries (not only SELECT, even DELETES ) The problem is probably not in the communication parameters, like max_allowed_packet or connection_timeout on the server because I increased then Does anyone know how can I control those parameters from JDBC? Amir Shay [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost connection to MySQL server during query problem
Hi, Sometimes when querying mySQL 4.1.11 on Linux machine I get the error Lost connection to MySQL server during query. Here are the symptoms 1. When running the query from the server it returns OK 2. When running the query from another machine using the mySQL query browser it returns OK 3. When running the query from another machine from Java using mySQL JDBC it fails with Lost connection to... 4. When running the query from another machine using software like EMS it fails with Lost connection to... 5. The error is always after 4.7 seconds and only in heavy queries (not only SELECT, even DELETES ) The problem is probably not in the communication parameters, like max_allowed_packet or connection_timeout on the server because I increased then Does anyone know how can I control those parameters from JDBC? Amir
Bug or query problem?
Hi, This is driving me nuts, please tell me is this a bug or a problem wiht my query: jappz_guestbook(owner_cno, sent_datetime, is_secret, accepted) jappz_member_profile(cno, pic_name) jappz_guestbook's owner_cno and jappz_member_profile's cno are the same what i need to do is select * from jappz_guestbook where owner_cno=x and only select pic_name from jappz_member_profile where cno=jappz_guestbook.cno This is my query: SELECT from_cno,from_usernam,sent_datetime,is_secret,accepted,jappz_member_profile. pic1 FROM jappz_guestbook INNER JOIN jappz_member_profile ON jappz_guestbook.owner_cno = jappz_member_profile.cno WHERE jappz_guestbook.owner_cno = jappz_member_profile.cno order by from_cno DESC limit 0,30 I am getting 4 results: from_cno from_usernam sent_datetime is_secret accepted pic1 1 r 2005-05-01 00:03:14 0 0 s.jpg 1 r 2005-05-01 00:04:09 1 0 s.jpg 3 ryana32005-05-01 01:15:57 0 0 s.jpg 3 ryana3 2005-05-01 01:16:061 0 s.jpg which is wrong because pic1 for from_cno should be r.jpg but its somehow cacheing the above. Please advise. Thanks, Ryan A -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 4/29/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Problem
Ok, Thanks for all Roger. -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: viernes, 22 de abril de 2005 4:06 Para: Dto. Sistemas de Unitel CC: mysql@lists.mysql.com Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. There should be no problem with joining two tables based on columns with different names. productos.prod_id=indexes.id should work. Both columns could be indexed, (in two separate indexes, of course, as they are in two separate tables), but only one index will be used, depending on the join order. It does not matter if you write productos.prod_id=indexes.id or indexes.id=productos.prod_id, and it does not matter if you write FROM productos,indexes or FROM indexes,productos (unless STRAIGHT_JOIN is used). In this case (se earlier posts in this thread) the table named indexes should be read first, then productos. That means an index on productos.prod_id will be used, if available. The name of the column in the productos table or the name of the related column in the indexes table does not matter. The = character in the ON clause or in the WHERE clause dictates which columns are related, not the name of the columns. I don't know why your index did not work at first. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Problem
Hi Roger, You are ok, there was an index problem in one table, they name of the rows wasn't equal and MySQL didn't recognize they as the same index. I have changed the row name and now is working fine, but I have a little question, How can I use indexes with different names in it's tables? Thanks for your help, you have been very helpful for me. Roberto -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 20 de abril de 2005 18:30 Para: mysql@lists.mysql.com CC: Dto. Sistemas de Unitel Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: Hi Roger, That was just I need. The order isnt like you say: ++-+---++---+-+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---++---+-+- ++---+-+ | 1 | SIMPLE | t1| const | PRIMARY,uniq | uniq| 250 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t2| const | PRIMARY,uniq | uniq| 250 | const | 1 | | | 1 | SIMPLE | productos | ALL| PRIMARY,dupli | [NULL] | [NULL] | [NULL] | 16153 | | | 1 | SIMPLE | i2| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | | 1 | SIMPLE | i1| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | ++-+---++---+-+- ++---+-+ Productos is executed in the middle of the other two ones, the time of the query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a little more should be perfect. It seems as there is no index on productos.prod_id? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Dto. Sistemas de Unitel wrote: Hi Roger, You are ok, there was an index problem in one table, they name of the rows wasn't equal and MySQL didn't recognize they as the same index. I have changed the row name and now is working fine, but I have a little question, How can I use indexes with different names in it's tables? I'm not sure if I understand the question, but in general the names of the columns and indexes are not case sensitive, which in your case means prod_id and PROD_ID should be treated equal. Table names and database names are different, it depends on the filesystem the server is using: URL: http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Problem
You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. Thanks -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: jueves, 21 de abril de 2005 18:17 Para: mysql@lists.mysql.com CC: Dto. Sistemas de Unitel Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: Hi Roger, You are ok, there was an index problem in one table, they name of the rows wasn't equal and MySQL didn't recognize they as the same index. I have changed the row name and now is working fine, but I have a little question, How can I use indexes with different names in it's tables? I'm not sure if I understand the question, but in general the names of the columns and indexes are not case sensitive, which in your case means prod_id and PROD_ID should be treated equal. Table names and database names are different, it depends on the filesystem the server is using: URL: http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Dto. Sistemas de Unitel wrote: You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. There should be no problem with joining two tables based on columns with different names. productos.prod_id=indexes.id should work. Both columns could be indexed, (in two separate indexes, of course, as they are in two separate tables), but only one index will be used, depending on the join order. It does not matter if you write productos.prod_id=indexes.id or indexes.id=productos.prod_id, and it does not matter if you write FROM productos,indexes or FROM indexes,productos (unless STRAIGHT_JOIN is used). In this case (se earlier posts in this thread) the table named indexes should be read first, then productos. That means an index on productos.prod_id will be used, if available. The name of the column in the productos table or the name of the related column in the indexes table does not matter. The = character in the ON clause or in the WHERE clause dictates which columns are related, not the name of the columns. I don't know why your index did not work at first. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi, Im trying to make a querythat need to search throught a table and have to search different terms, and SUM their Rank (is for a small search engine I have to design), this is the table: Table Indexes ID bigint ID_Termino -bigint Rank double Table Terminos ID_Termino bigint Primary key Termino Varchar(50) Table productos ID bigint prod_descripcion varchar(255) I have another engine that search throught text and apply a Rank to each word and store it on table indexes and if this word doesnt exist it store it in table terminus and the id of this word is related in indexes table. Indexes is related with another table called productos that is the one that is analyzed by my engine. When any client ask to me for search computer intel y search with the next query, but the results arent 100% good because I have to use a clause OR in termino, but I want to use there something like an and (something like indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=computer and termino=intel I know that is impossible, but maybe there is another way to make that). SELECT STRAIGHT_JOIN terminos.id_termino,productos.prod_descripcion,indexes.id,terminos.termino,s um(indexes.rank) as ordenate,productos.prod_unitel_id FROM terminos,indexes,productos where indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=computer or indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=intel group by (indexes.id) order by ordenate desc Thanks for all and best regards, Roberto
Re: Query problem
Dto. Sistemas de Unitel wrote: [...] something like /indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=computer and termino=intel/ I know that is impossible, but maybe there is another way to make that). Yes, there is another way. You _can_ join the same table multiple times, but you need to use table aliases. In this case you need to join two tables two times, both the terminos table and the indexes table. SELECT STRAIGHT_JOIN Why do you use the STRAIGHT_JOIN? It is often best to let MySQL optimize how to solve the query (order of joins), STRAIGHT_JOIN prevents this. terminos.id_termino,productos.prod_descripcion,indexes.id,terminos.termino, sum(indexes.rank) as ordenate,productos.prod_unitel_id FROM terminos,indexes,productos where indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=computer or indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=intel group by (indexes.id) order by ordenate desc In general, when you use both AND and OR in an expression, you should use parantheses to make it clear what happens. I have no idea how MySQL resolves the above WHERE clause. The parantheses on the GROUP BY clause are not needed, they are ignored. Try something like this: SELECT productos.prod_descripcion, sum(i1.rank+i2.rank) as ordenate, productos.prod_unitel_id FROM terminos t1,terminos t2,indexes i1,indexes i2,productos WHERE i1.id=productos.prod_id and i1.id_termino=t1.id_termino and t1.termino=computer AND i2.id=productos.prod_id and i2.id_termino=t2.id_termino and t2.termino=intel GROUP BY productos.prod_descripcion, productos.prod_unitel_id order by ordenate desc This is almost the same statment, but your OR is replaced with an AND, it should result in a faster query. In general, for a GROUP BY query, you should not select columns you are not using in the GROUP BY clause, except for when aggregate functions (like SUM(),AVG(),MIN(),MAX()...) are used on the column(s). URL: http://dev.mysql.com/doc/mysql/en/group-by-functions.html URL: http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html Use EXPLAIN to check what join order the MySQL optmizer chooses for you, post the result if it is still too slow. If everything is indexed correctly, it should read t1/t2 first, then i1/i2 and finally productos. URL: http://dev.mysql.com/doc/mysql/en/explain.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Hi Roger, That was just I need. The order isnt like you say: ++-+---++---+-+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---++---+-+- ++---+-+ | 1 | SIMPLE | t1| const | PRIMARY,uniq | uniq| 250 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t2| const | PRIMARY,uniq | uniq| 250 | const | 1 | | | 1 | SIMPLE | productos | ALL| PRIMARY,dupli | [NULL] | [NULL] | [NULL] | 16153 | | | 1 | SIMPLE | i2| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | | 1 | SIMPLE | i1| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | ++-+---++---+-+- ++---+-+ Productos is executed in the middle of the other two ones, the time of the query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a little more should be perfect. Thanks another time, your help is very good for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Dto. Sistemas de Unitel wrote: Hi Roger, That was just I need. The order isnt like you say: ++-+---++---+-+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---++---+-+- ++---+-+ | 1 | SIMPLE | t1| const | PRIMARY,uniq | uniq| 250 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t2| const | PRIMARY,uniq | uniq| 250 | const | 1 | | | 1 | SIMPLE | productos | ALL| PRIMARY,dupli | [NULL] | [NULL] | [NULL] | 16153 | | | 1 | SIMPLE | i2| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | | 1 | SIMPLE | i1| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | ++-+---++---+-+- ++---+-+ Productos is executed in the middle of the other two ones, the time of the query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a little more should be perfect. It seems as there is no index on productos.prod_id? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: sol beach [EMAIL PROTECTED] Reply-To: sol beach [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] Subject: Re: Query Problem Date: Tue, 15 Mar 2005 12:33:24 -0800 http://www.catb.org/~esr/faqs/smart-questions.html It would help a lot to get answers if you shared the description of all three tables. On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas [EMAIL PROTECTED] wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: gerald_clark [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query Problem Date: Tue, 15 Mar 2005 14:49:45 -0600 Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
You are confusing data retrieval with data presentation. Sure, SQL can do many things to format data but some data formats are better achieved through the programming language you are using to present this data for viewing. In my opinion, the type of formatting you want to do is one of those tasks. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Xristos Karvouneas [EMAIL PROTECTED] wrote on 03/16/2005 09:03:25 AM: The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: gerald_clark [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query Problem Date: Tue, 15 Mar 2005 14:49:45 -0600 Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
You may be able to get something like what you describe using
GROUP_CONCAT(), if you have mysql 4.1, though it would be easier to get a
comma separated list of authors than separate lines. See the manual for
details http://dev.mysql.com/doc/mysql/en/group-by-functions.html.
In my opinion, however, you are confusing presentation of data with how it
is accessed. Usually, presentation is the job of your code, not SQL. If I
were doing this, I would
SELECT book.title, author.name
FROM book
JOIN authorbook ON book.bookid = authorbook.bookid
JOIN author ON authorbook.authorid = author.authorid
ORDER BY book.title, author.name;
to get the data. (Notice I left out DISTINCT. There's something wrong with
the data in your tables if DISTINCT is needed here.) To get the format you
desire when printing the results, my code would only print the value of
book.title if it is different from the value of book.title I previously
printed. Something like (pseudo code):
last_title = ''
for each result_row
{
get title and author from result_row
if title != last_title
{ # first row of a new book, so print the tile
print title
last_title = title
}
else
{ # another author for the same book
print blank space
}
print author
}
That's pretty easy to translate into real code in every language I know.
Michael
Xristos Karvouneas wrote:
The code for generating the tables is shown below:
create table book
(
bookid char(12) not null,
dimensions char(15),
availability char(30),
booktype char(20),
publisher char(20),
isbn char(20),
itemsinstock int(3),
price float(5,2),
title char(50),
toc char(24),
picture char(36),
primary key (bookid));
create table author
(
authorid char(12) not null,
name char(24),
primary key (authorid));
create table authorbook
(
bookid char(12) not null references book,
authorid char(12) not null references author,
primary key(bookid,authorid));
Basically, I want to get it in the format specified in the message, i.e.
if the title is the same, I do not want it to be printed again...
Any ideas?
From: sol beach [EMAIL PROTECTED]
Reply-To: sol beach [EMAIL PROTECTED]
To: Xristos Karvouneas [EMAIL PROTECTED]
Subject: Re: Query Problem
Date: Tue, 15 Mar 2005 12:33:24 -0800
http://www.catb.org/~esr/faqs/smart-questions.html
It would help a lot to get answers if you shared the description of
all three tables.
On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas
[EMAIL PROTECTED] wrote:
Dear All,
I am faced with the following problem: I have got three tables -
book,author
and authorbook - containing information about books and authors
(some books
have multiple authors). I want to do a query that would print
information
like:
Title 1 Author 1
Author 2
Title 2Author 3
Author 4
I have written the following:
select distinct title, name
from authorbook,book,author where
authorbook.authorid=author.authorid and
book.bookid=authorbook.bookid;
hoping that it will do what I want, but I am only getting the first
author
for each book (probably because of the distinct keyword).
Is there any way I can modify the query so that it does what I want
it to
do?
I look forward to hearing from you soon.
Thanks in advance.
George
_
Express yourself instantly with MSN Messenger! Download today it's
FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table Query Problem...
Nick Zukin [EMAIL PROTECTED] wrote on 03/14/2005 05:22:38 PM: I'm trying to do a multitable query and am having problems. I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table. I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query: $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; $query .= WHERE (p.productname LIKE '%.$_GET['keyword'].%') ; $query .= OR (p.productfamily LIKE '%.$_GET['keyword'].%') ; $query .= OR (v.vcategory LIKE '%.$_GET['keyword'].%') ; $query .= GROUP BY v.vbusiness ; As an example, it might look like this: SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with. TIA, Nick You are GROUPING when you shouldn't. You only need to GROUP whenever you want to perform some kind of aggregate function (AVG, SUM, COUNT, etc). What you see is a pseudo-random result from all of the possible results that meet your criteria. Drop your GROUP BY clause and your results should come back into line. If you were trying to eliminate duplicate rows, you might want to try SELECT DISTINCT. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Query Problem
Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-Table Query Problem...
I'm trying to do a multitable query and am having problems. I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table. I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query: $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; $query .= WHERE (p.productname LIKE '%.$_GET['keyword'].%') ; $query .= OR (p.productfamily LIKE '%.$_GET['keyword'].%') ; $query .= OR (v.vcategory LIKE '%.$_GET['keyword'].%') ; $query .= GROUP BY v.vbusiness ; As an example, it might look like this: SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with. TIA, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table Query Problem...
What are the results? sk On Mon, 14 Mar 2005 14:22:38 -0800, Nick Zukin [EMAIL PROTECTED] wrote: I'm trying to do a multitable query and am having problems. I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table. I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query: $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; $query .= WHERE (p.productname LIKE '%.$_GET['keyword'].%') ; $query .= OR (p.productfamily LIKE '%.$_GET['keyword'].%') ; $query .= OR (v.vcategory LIKE '%.$_GET['keyword'].%') ; $query .= GROUP BY v.vbusiness ; As an example, it might look like this: SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with. TIA, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table Query Problem...
Because, with the '%keyword%' operator, you're going to match any of those columns that contain the keyword inside of it. This can be a little confusing as 'ef' will return true on 'abcdefghijk'? Instead, you might try 'keyword%' so that 'apple' returns true for 'apples', 'apple juice', 'apple cider', BUT returns false for 'ple'. But, without seeing the results and what you'd hoped them to be, it's tough to narrow down the problem. sk On Mon, 14 Mar 2005 15:08:28 -0800, Scott Klarenbach [EMAIL PROTECTED] wrote: What are the results? sk On Mon, 14 Mar 2005 14:22:38 -0800, Nick Zukin [EMAIL PROTECTED] wrote: I'm trying to do a multitable query and am having problems. I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table. I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query: $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; $query .= WHERE (p.productname LIKE '%.$_GET['keyword'].%') ; $query .= OR (p.productfamily LIKE '%.$_GET['keyword'].%') ; $query .= OR (v.vcategory LIKE '%.$_GET['keyword'].%') ; $query .= GROUP BY v.vbusiness ; As an example, it might look like this: SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with. TIA, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table Query Problem...
On Mon, 14 Mar 2005 15:12:18 -0800, Scott Klarenbach wrote Because, with the '%keyword%' operator, you're going to match any of those columns that contain the keyword inside of it. This can be a little confusing as 'ef' will return true on 'abcdefghijk'? Instead, you might try 'keyword%' so that 'apple' returns true for 'apples', 'apple juice', 'apple cider', BUT returns false for 'ple'. But, without seeing the results and what you'd hoped them to be, it's tough to narrow down the problem. sk Yes, I understand, but that's not what I'm getting. I'm getting rows returned where apples is not present in any of the columns in any way. I think it's a syntax mistake in my query, but I can't see it. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
