Re: Need help with a query
On Monday 23 January 2006 03:33 pm, Michael Stassen wrote: > Mark Phillips wrote: > > I am running mysql 4.0.24 on Debian sarge. > > > > I have a table with two columns, "team" and "division", both varchar(255). > > There are some errors in the table where division has a value but team is > > blank. Given that I am getting new data, and the data entry folks may create > > a record with a blank division and a team, I thought I would avoid any issues > > with team or division being blank as follows: > > You should change your app to enforce your rules. That is, your app should > prevent your data entry folks from entering incomplete records. Otherwise, it's > garbage in, garbage out. You are absolutely correct. However, it is not my app nor do I control how the data is input into it. I just get a dump of the data to work with. :-( > > > SELECT DISTINCT division, team FROM registered WHERE team!='' OR DIVISION!='' > > > > That returns what I expected - all division-team fields have data > > I doubt it. You've joined your two conditions with "OR", so your WHERE > condition will be true for any row with at least one of the two conditions met. > Only a row with *both* fields blank would be excluded. Remember, > >NOT(A OR B) = NOT(A) AND NOT(B) > > so you should have used "AND". You see? A row you don't want has > >team = '' OR DIVISION = '' > > so a row you do want has > >NOT(team = '' OR DIVISION = '') > > which is equivalent to > >team != '' AND DIVISION != '' > I feel so stupid. Not sure why I missed that - it is so basic! Well, that is my first Home Simpson of the week Doh! Thanks ! > > Then I decided to order the output, so I added an ORDER BY clause and some > > parentheses to make the sql more readable: > > > > SELECT DISTINCT division, team FROM registered WHERE (team!='' OR > > DIVISION!='') ORDER BY division, team > > The parentheses are irrelevant. The ORDER BY cannot have changed which rows > were returned. Perhaps the ordering facilitated noticing the unwanted results. > > > But, I still get records with a blank team field (even if I remove the > > parenthesis). I finally found a solution, but I do not understand it: > > > > SELECT DISTINCT division, team FROM registered WHERE (team!='' AND > > DIVISION!='') ORDER BY division, team > > As I explained above. > > > Why does the ORDER BY clause require an AND in the WHERE clause to work > > correctly?? I do not understand the logic. > > ORDER BY has nothing to do with it. > > > Thanks for any insight you can share with me. > > Michael > > -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query
Mark Phillips wrote: I am running mysql 4.0.24 on Debian sarge. I have a table with two columns, "team" and "division", both varchar(255). There are some errors in the table where division has a value but team is blank. Given that I am getting new data, and the data entry folks may create a record with a blank division and a team, I thought I would avoid any issues with team or division being blank as follows: You should change your app to enforce your rules. That is, your app should prevent your data entry folks from entering incomplete records. Otherwise, it's garbage in, garbage out. SELECT DISTINCT division, team FROM registered WHERE team!='' OR DIVISION!='' That returns what I expected - all division-team fields have data I doubt it. You've joined your two conditions with "OR", so your WHERE condition will be true for any row with at least one of the two conditions met. Only a row with *both* fields blank would be excluded. Remember, NOT(A OR B) = NOT(A) AND NOT(B) so you should have used "AND". You see? A row you don't want has team = '' OR DIVISION = '' so a row you do want has NOT(team = '' OR DIVISION = '') which is equivalent to team != '' AND DIVISION != '' Then I decided to order the output, so I added an ORDER BY clause and some parentheses to make the sql more readable: SELECT DISTINCT division, team FROM registered WHERE (team!='' OR DIVISION!='') ORDER BY division, team The parentheses are irrelevant. The ORDER BY cannot have changed which rows were returned. Perhaps the ordering facilitated noticing the unwanted results. But, I still get records with a blank team field (even if I remove the parenthesis). I finally found a solution, but I do not understand it: SELECT DISTINCT division, team FROM registered WHERE (team!='' AND DIVISION!='') ORDER BY division, team As I explained above. Why does the ORDER BY clause require an AND in the WHERE clause to work correctly?? I do not understand the logic. ORDER BY has nothing to do with it. Thanks for any insight you can share with me. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help with a query
Hello. You may use these queries: select flight_id ,baseline*tan(radians(angle)) as attitude from flights where (baseline*tan(radians(angle))) = ( select max(baseline*tan(radians(angle))) from flights f2); +---+-+ | flight_id | attitude| +---+-+ | 2 | 119.17535925942 | +---+-+ mysql> select flight_id, baseline*tan(radians(angle)) as attitude from flights where (baseline*tan(radians(angle)))= (select min(baseline*tan(radians(angle))) from flights f2); +---+-+ | flight_id | attitude| +---+-+ | 8 | 72.426658110531 | +---+-+ However, you should be aware about rounding errors and possibly different results in 5.0 and older versions. See: http://dev.mysql.com/doc/refman/5.0/en/precision-math.html Mark Phillips <[EMAIL PROTECTED]> wrote: > I have a table with several columns. The ones of interest are flight_id, > angle, and baseline. I want to find the flight_ids for the flights with the > maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)). > > For example, > Flights > +++---+ > | flight_id | angle| baseline | > +++---+ > | 1 | 37.0 | 100.0 | > | 2 | 50.0 | 100.0 | > | 3 | 48.0 | 100.0 | > | 4 | 40.0 | 100.0 | > | 5 | 44.0 | 100.0 | > | 6 | 40.0 | 100.0 | > | 7 | 45.0 | 100.0 | > | 8 | 44.0 | 75.0 | > | 9 | 57.8 | 75.0 | > +++---+ > > The result I am looking for are: > > Maximum altitude: > +++ > | flight_id | altitude | > +++ > | 2 | 119.17536 | > | 9 | 119.17536| > +++ > > Minimum altitude: > +---+---+ > | flight_id | altitudeM | > +---+---+ > | 8 | 72.42666 | > +---+---+ > > Thanks for any help you can provide! > -- 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]
Fwd: Re: Need Help with a query
I forgot to copy the list as well Mark -- Forwarded Message -- Subject: Re: Need Help with a query Date: Sunday 11 December 2005 06:47 pm From: Mark Phillips <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Rhino, My apologies for leaving out the version of mysql. I agree 1000% with your rant - it was an oversight on my part. I have mysql 4.0.24 on my development machine and 4.1.11 on my production machine. Thanks for the query - it works very well in 4.1.11. I think in 4.0.24 I need to use a temporary table. Thanks again! Mark On Sunday 11 December 2005 04:19 pm, you wrote: > - Original Message - > From: "Mark Phillips" <[EMAIL PROTECTED]> > To: "MYSQL List" > Sent: Sunday, December 11, 2005 12:38 PM > Subject: Need Help with a query > > >I have a table with several columns. The ones of interest are flight_id, > > angle, and baseline. I want to find the flight_ids for the flights with > > the > > maximum and minimum altitudes, where > > altitude=baseline*tan(radians(angle)). > > > > For example, > > Flights > > +++---+ > > > > | flight_id | angle| baseline | > > > > +++---+ > > > > | 1 | 37.0 | 100.0 | > > | 2 | 50.0 | 100.0 | > > | 3 | 48.0 | 100.0 | > > | 4 | 40.0 | 100.0 | > > | 5 | 44.0 | 100.0 | > > | 6 | 40.0 | 100.0 | > > | 7 | 45.0 | 100.0 | > > | 8 | 44.0 | 75.0 | > > | 9 | 57.8 | 75.0 | > > > > +++---+ > > > > The result I am looking for are: > > > > Maximum altitude: > > +++ > > > > | flight_id | altitude | > > > > +++ > > > > | 2 | 119.17536 | > > | 9 | 119.17536| > > > > +++ > > > > Minimum altitude: > > +---+---+ > > > > | flight_id | altitudeM | > > > > +---+---+ > > > > | 8 | 72.42666 | > > > > +---+---+ > > > > Thanks for any help you can provide! > > I do wish posters to this list would get in the habit of volunteering which > version of MySQL they are using, particularly for SQL questions! > > The answer to almost every SQL question is "it depends on which version of > MySQL you are using". It's very tedious to give the answer for every > version MySQL, as in: If you're using Version 3.x, the answer is A. If > you're using Version 4.0.x the answer is B. If you're using Version 4.1.x, > the answer is C. etc. > > [By the way, I don't mean to single you out with this mini-rant; it's just > a general observation.] > > Therefore, I'm going to assume you are using Version 4.1 or higher; in > other words, you use a version which supports subqueries. If you are on an > earlier version, please reply to the list and explain which version you are > on. Perhaps someone will be willing to show you alternatives that will work > for you. > > I should also explain that I am _not_ on a version of MySQL which supports > subqueries. However, my main database is DB2 which does support subqueries > and the SQL used by DB2 and MySQL is very very similar so this _untested_ > answer should be pretty close to what you need. > > I think the best answer to your question is to use subqueries. I'm going to > express the answer in pseudocode first to give you a general sense of the > answer, then give you something that should be pretty close to a final > answer that will work on your system. > > Pseudocode (for maximum altitude): > > select flight_id, baseline*tan(radians(angle)) as max_altitude > from Flights > where baseline*tan(radians(angle)) in (subquery that gets largest altitude > from table) > > In real SQL, that should end up looking like this: > > select flight_id, baseline*tan(radians(angle)) as max_altitude > from Flights > where baseline*tan(radians(angle)) in (select > max(baseline*tan(radians(angle))) from Flights) > > To get the query for the minimum altitude, use the exact same query except > replace the max function with the min function in the subquery and change > the 'as' for the outer query from 'max_altitude' to 'min_altitude'. > > For what it's worth, I got slightly different numbers in DB2 so I did
Re: Need help with a query..
At 03:09 AM 6/16/05, Cory Robin wrote:
I need to speed up a search, big time.
I have an application that searches for records on a date field. If it
doesn't find an exact date match, it keeps searching adjacent days until it
finds a certain amount of records.
The problem now is, I'm using my application to loop through and run
multiple queries and it's dog ass slow..I'm hoping that one of you SQL
gurus can point me in the right direction to create a query that will work
it out for me. Here's the logic the best I can explain..
I want to return a minimum of 15 records.. I'm searching for records on or
around 2005-10-01
Select * from table_x where row_date = '2005-10-01'
/* at this point if matched records are >= 15 then simply return the records
on that date.. If not..*/
Select * from table_x where row_date = '2005-09-31'
Select * from table_x where row_date = '2005-10-02'
And so on until it finds >= 15 records or it searches through 5 days (+- 3
on search date)
I hope this makes sense.. I'm new to all this stuff.
Eventually I'm going to do the same thing for times as well..
Thanks in advance for any help!
Plus/minus 3 days makes a total of 7 days, not 5 days. I would try
something like:
Select * from table_x where row_date between '2005-09-28' and '2004-10-04'
order by abs(datediff('2005-10-01',rowdate) LIMIT 15;
It is not clear if you are looking for
A) one day with 15 records
B) the 15 records closest to your target date.
C) the 15 records closest to target date, plus all records for dates in
that result of 15 records.
My example query does (B). If you want (A) or (C), lease out the LIMIT
clause and do that code in your application - might also be possible with
subselects, but let's make sure that;s what you want before going
there. Knowing what version you have is crucial, because availability of
date/time functions varies widely in currently available releases.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
[EMAIL PROTECTED] wrote on 16/06/2005 16:29:46: > > > [EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM: > > > Cory Robin <[EMAIL PROTECTED]> wrote on 16/06/2005 08:09:22: > > > > I need to speed up a search, big time. > > > > > > I have an application that searches for records on a date field. If it > > > doesn't find an exact date match, it keeps searching adjacent days until > > it > > > finds a certain amount of records. > > > > > > The problem now is, I'm using my application to loop through and run > > > multiple queries and it's dog ass slow..I'm hoping that one of you > > SQL > > > gurus can point me in the right direction to create a query that will > > work > > > it out for me. Here's the logic the best I can explain.. > > > > > > I want to return a minimum of 15 records.. I'm searching for records on > > or > > > around 2005-10-01 > > > > > > Select * from table_x where row_date = '2005-10-01' > > > /* at this point if matched records are >= 15 then simply return the > > records > > > on that date.. If not..*/ > > > Select * from table_x where row_date = '2005-09-31' > > > > > > Select * from table_x where row_date = '2005-10-02' > > > > > > And so on until it finds >= 15 records or it searches through 5 days (+- > > 3 > > > on search date) > > > > > > I hope this makes sense.. I'm new to all this stuff. > > > > > > Eventually I'm going to do the same thing for times as well.. > > > Heres a suggestion: > > > select * from table_x > > where row_date between date_sub(now(), interval 3 day) AND > > date_add(now(), interval 3 day) > > order by abs(time_to_sec(datediff(created, now( > > limit 15 ; > > > This does times relative to now(), but I am sure you can generalise it. > > The first line specifies the desired fields > > The second selects (in principle) all the records within your largest > > target window > > The third orders them by closeness to your target time > > and the last says you only want 15 of them. > > > This version is based on exact seconds from the target time (now() in my > > case): the version which works in whole days would only be slightly > > different. > > > Alec > > Only one problem with your solution, LIMIT tells how many records AT > MOST to return, he wants to get 15 AT LEAST and stop appending > records once he gets over 15 total results. > > I can't seem to make a query (in reply to his problem) to return AT > LEAST 15 rows without some sort of iteration or flow control > involved in the process. Neither on of which is available in MySQL > SQL until 5.0+. Since he didn't say which version he is using I am > assuming a target version of 4.1 or less for the solution. Does > anyone else have a non-scripted solution? That wasn't the way I read it "And so on until it finds >= 15 records or it searches through 5 days" - within I interpret as wanting all the records within 5 days up to a limit of 15. I presume that if >15 records are found, those closes to the target time are preferred. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
[EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM: > Cory Robin <[EMAIL PROTECTED]> wrote on 16/06/2005 08:09:22: > > I need to speed up a search, big time. > > > > I have an application that searches for records on a date field. If it > > doesn't find an exact date match, it keeps searching adjacent days until > it > > finds a certain amount of records. > > > > The problem now is, I'm using my application to loop through and run > > multiple queries and it's dog ass slow..I'm hoping that one of you > SQL > > gurus can point me in the right direction to create a query that will > work > > it out for me. Here's the logic the best I can explain.. > > > > I want to return a minimum of 15 records.. I'm searching for records on > or > > around 2005-10-01 > > > > Select * from table_x where row_date = '2005-10-01' > > /* at this point if matched records are >= 15 then simply return the > records > > on that date.. If not..*/ > > Select * from table_x where row_date = '2005-09-31' > > > > Select * from table_x where row_date = '2005-10-02' > > > > And so on until it finds >= 15 records or it searches through 5 days (+- > 3 > > on search date) > > > > I hope this makes sense.. I'm new to all this stuff. > > > > Eventually I'm going to do the same thing for times as well.. > Heres a suggestion: > select * from table_x > where row_date between date_sub(now(), interval 3 day) AND > date_add(now(), interval 3 day) > order by abs(time_to_sec(datediff(created, now( > limit 15 ; > This does times relative to now(), but I am sure you can generalise it. > The first line specifies the desired fields > The second selects (in principle) all the records within your largest > target window > The third orders them by closeness to your target time > and the last says you only want 15 of them. > This version is based on exact seconds from the target time (now() in my > case): the version which works in whole days would only be slightly > different. > Alec Only one problem with your solution, LIMIT tells how many records AT MOST to return, he wants to get 15 AT LEAST and stop appending records once he gets over 15 total results. I can't seem to make a query (in reply to his problem) to return AT LEAST 15 rows without some sort of iteration or flow control involved in the process. Neither on of which is available in MySQL SQL until 5.0+. Since he didn't say which version he is using I am assuming a target version of 4.1 or less for the solution. Does anyone else have a non-scripted solution? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Need help with a query..
Cory Robin <[EMAIL PROTECTED]> wrote on 16/06/2005 08:09:22: > I need to speed up a search, big time. > > I have an application that searches for records on a date field. If it > doesn't find an exact date match, it keeps searching adjacent days until it > finds a certain amount of records. > > The problem now is, I'm using my application to loop through and run > multiple queries and it's dog ass slow..I'm hoping that one of you SQL > gurus can point me in the right direction to create a query that will work > it out for me. Here's the logic the best I can explain.. > > I want to return a minimum of 15 records.. I'm searching for records on or > around 2005-10-01 > > Select * from table_x where row_date = '2005-10-01' > /* at this point if matched records are >= 15 then simply return the records > on that date.. If not..*/ > Select * from table_x where row_date = '2005-09-31' > > Select * from table_x where row_date = '2005-10-02' > > And so on until it finds >= 15 records or it searches through 5 days (+- 3 > on search date) > > I hope this makes sense.. I'm new to all this stuff. > > Eventually I'm going to do the same thing for times as well.. Heres a suggestion: select * from table_x where row_date between date_sub(now(), interval 3 day) AND date_add(now(), interval 3 day) order by abs(time_to_sec(datediff(created, now( limit 15 ; This does times relative to now(), but I am sure you can generalise it. The first line specifies the desired fields The second selects (in principle) all the records within your largest target window The third orders them by closeness to your target time and the last says you only want 15 of them. This version is based on exact seconds from the target time (now() in my case): the version which works in whole days would only be slightly different. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with a query
awesome. thank you! [Wed, Aug 25, 2004 at 06:27:38PM -0700] This one time, at band camp, David Perron said: > > Are you assuming that all months have 30 days? You can use the same syntax > with INTERVAL 1 MONTH > I would also format the date comparison to use the same precision that the > DATE_ADD function outputs. > > So, > > DATE_ADD(table.date, INTERVAL 1 MONTH) <= DATE_FORMAT(CURDATE(), '%Y-%m-%d') > > I might be off on the 1 MONTH part, but I think it's the right track. > > -Original Message- > From: Redmond Militante [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 25, 2004 6:03 PM > To: [EMAIL PROTECTED] > Subject: need help with a query > > hi > > i need advice on a query i'm trying to do. i'm trying to find entries with > that are about to expire. entries expire if their date of submission is > older than 60 days. i want to find all entries with a date of submission > greater than 30 days, or those that are going to expire within a month > > i'm using this as part of my query to find entries that are about to expire: > ' ... DATE_ADD($dbtable3.savedemployers2, INTERVAL 30 DAY) <=NOW()' > > am i doing this the right way? > > thanks > redmond > > -- > Redmond Militante > Software Engineer / Medill School of Journalism FreeBSD 5.2.1-RELEASE-p9 #0: > Thu Jul 1 14:36:26 CDT 2004 i386 8:00PM up 28 days, 3:36, 4 users, load > averages: 0.00, 0.29, 0.51 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- Redmond Militante Software Engineer / Medill School of Journalism FreeBSD 5.2.1-RELEASE-p9 #0: Thu Jul 1 14:36:26 CDT 2004 i386 11:00PM up 28 days, 6:36, 3 users, load averages: 0.22, 0.18, 0.30 pgpC8omL1KwJU.pgp Description: PGP signature
Re: need help with a query
David Perron wrote: Are you assuming that all months have 30 days? You can use the same syntax with INTERVAL 1 MONTH True. I would also format the date comparison to use the same precision that the DATE_ADD function outputs. Why would you do that? The date column contains a DATE. CURDATE() returns a DATE. DATE_ADD() returns a DATE. Comparing DATEs is straightforward. DATE_FORMAT returns a string, however, so your query compares a DATE to a string. At best, the difference is optimized away. At worst, an extra conversion takes place. Formatting is for presentation, not comparison. So, DATE_ADD(table.date, INTERVAL 1 MONTH) <= DATE_FORMAT(CURDATE(), '%Y-%m-%d') I might be off on the 1 MONTH part, but I think it's the right track. Again, compare columns to (functions of) constants. Do not run the column through a function, if at all possible. Thus, this should be table.date <= CURDATE() - INTERVAL 1 MONTH Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with a query
Redmond Militante wrote: hi i need advice on a query i'm trying to do. i'm trying to find entries with that are about to expire. entries expire if their date of submission is older than 60 days. i want to find all entries with a date of submission greater than 30 days, or those that are going to expire within a month i'm using this as part of my query to find entries that are about to expire: ' ... DATE_ADD($dbtable3.savedemployers2, INTERVAL 30 DAY) <=NOW()' am i doing this the right way? thanks redmond Almost. First, you say that your column is a DATE, but you are comparing to NOW(), which returns a DATETIME. That's OK (mysql will convert), but it would be slightly better to compare to CURDATE(), as it returns a DATE. More importantly, you do not want to compare a function of a column to a constant, because then an index on the column is of no use, forcing a full table scan. If at all possible, move the function to the constant side of the comparison, as a function of a constant is a constant. In other words, datecol + 30 days <= today is equivalent to datecol <= today - 30 days but the former forces a table scan while the latter could use an index on datecol. So, in your case, you should use WHERE ... $dbtable3.savedemployers2 <= CURDATE() - INTERVAL 30 DAY Finally, note that this would also return entries which have already expired, if they are still there. To leave those out, change the condition to $dbtable3.savedemployers2 BETWEEN CURDATE() - INTERVAL 60 DAY AND CURDATE() - INTERVAL 30 DAY Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help with a query
Are you assuming that all months have 30 days? You can use the same syntax with INTERVAL 1 MONTH I would also format the date comparison to use the same precision that the DATE_ADD function outputs. So, DATE_ADD(table.date, INTERVAL 1 MONTH) <= DATE_FORMAT(CURDATE(), '%Y-%m-%d') I might be off on the 1 MONTH part, but I think it's the right track. -Original Message- From: Redmond Militante [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 6:03 PM To: [EMAIL PROTECTED] Subject: need help with a query hi i need advice on a query i'm trying to do. i'm trying to find entries with that are about to expire. entries expire if their date of submission is older than 60 days. i want to find all entries with a date of submission greater than 30 days, or those that are going to expire within a month i'm using this as part of my query to find entries that are about to expire: ' ... DATE_ADD($dbtable3.savedemployers2, INTERVAL 30 DAY) <=NOW()' am i doing this the right way? thanks redmond -- Redmond Militante Software Engineer / Medill School of Journalism FreeBSD 5.2.1-RELEASE-p9 #0: Thu Jul 1 14:36:26 CDT 2004 i386 8:00PM up 28 days, 3:36, 4 users, load averages: 0.00, 0.29, 0.51 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with a query
sounds like you need a join. Select * from Poll, poll_votes where (Poll.poll_id = poll_votes.poll_id) and (poll_votes.user_ID = WHATEVER); But I'm still a beginner so Respectfully, Ligaya Turmelle ""Anders Gjermshus"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi. > > I'm having trouble with a query. I'm wondering if it is possible at all. > > I'm making a poll for my webpage. I have two tables: > > > > Poll and poll_votes > > > > Table poll contains data about the poll > > Poll_Votes contains all the votes. What I want is to pull out data about the > poll and if the user has voted or not. > > > > I have tried many different queries, but I haven't managed to get it to > work. Can someone help me. > > > > My poll_votes table has this structure: > > Poll_id > > User_id > > Time > > > > I hope someone can help me. > > > > - Anders Gjermshus > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query
* Soheil Shaghaghi > Hello everyone, > I need help with MySQL coding in php please if anyone can help. I can try. :) > I have 3 tables: > -users, where the user info is stored. > -awards: contains the list of all the awards for each user > -award_types: contains different types of award > The tables are at the bottom of the page. > > What I need to do is look at these tables when a user id is being > viewed and display the awards image that the user has won. > A user can have multiple awards. Ok... and what is the problem? The SQL could be something like this: SELECT award_type, award_image FROM award_types,awards WHERE award_types.id = awards.award_id AND awards.chosen = 'enabled' AND awards.user_id = $userid (Not sure about the chosen = 'enabled', just looked like that was what you wanted from your example data.) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
* Aleksandar Bradaric > >> select key, desc, value > >> from your_table t1 > >> where value = (select max(value) from your_table where desc = t1.desc) > > > Anyway, when i execute this query, i get an error near 'select > > max(value)'... :( > > It's because the subselects are supported from version 4.1. Yes. > If you use older MySQL version then it's not possible to do > it with a single query :( Yes, it is. :) http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > See the "MAX-CONCAT trick". -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Hi, > I have already tried the 'rtfm', but it just didn't help. But it's right there :) 3.5.2 The Row Holding the Maximum of a Certain Column > ..and I want to get this with a single query: > +-++--+ > | key | desc| value | > +-++--+ > | 2 | book| 7 | > | 6 | pen | 7 | > +-++--+ select key, desc, value from your_table t1 where value = (select max(value) from your_table where desc = t1.desc) Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Tibby wrote: ..and I want to get this with a single query: +-++--+ | key | desc| value | +-++--+ | 2 | book| 7 | | 6 | pen | 7 | +-++--+ I need to get only one row from col. DESC, the one with the highest VALUE. With one query... select `desc`, max(value) from mytable group by `desc` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query ...
Hi John Kelly, This is not a perfect solution but may be useful to you. But still with two queries! From Mysql prompt issue these two queries. SELECT @maxcat := max(Category) FROM table WHERE category = 'Sports:Football:Players' OR category = 'Sports:Football' OR category = 'Sports'; Select * from table where category = @maxcat; Note that "abc" is greater than "ab" This will not be affected by the depth of category. Anvar. At 06:15 PM 02/11/2001 -0800, you wrote: >Hi, I have a MySQL table with a column that contains some of a web site >directory's category names in the format: > >Sports:Football:Players > >I am trying to build a query that that locates all records that match the >above category name OR if none exist its parent "Sports:Football" OR if none >exist its parent "Sports". The top level category, in this case "Sports", >will always have at least one matching record. > >I know I can do this with multiple queries by checking the previous query's >result, but I am trying to build a query that does it all in one lookup to >avoid lots of lookups in deep categories. Something along the logical lines >of ... > >SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE >category = 'Sports:Football' OR IF NONE category = 'Sports' > >... of course the above query does not work but if anyone knows of how to >accomplish something similar in one query I would much appreciate it. > >Thanks! > > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help with a query ...
On Fri, Nov 02, 2001 at 06:15:08PM -0800, John Kelly wrote: > Hi, I have a MySQL table with a column that contains some of a web site > directory's category names in the format: > > Sports:Football:Players > > I am trying to build a query that that locates all records that match the > above category name OR if none exist its parent "Sports:Football" OR if none > exist its parent "Sports". The top level category, in this case "Sports", > will always have at least one matching record. > > I know I can do this with multiple queries by checking the previous query's > result, but I am trying to build a query that does it all in one lookup to > avoid lots of lookups in deep categories. Something along the logical lines > of ... > > SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE > category = 'Sports:Football' OR IF NONE category = 'Sports' > > ... of course the above query does not work but if anyone knows of how to > accomplish something similar in one query I would much appreciate it. Sir, the problem is that relational databases use set logic, and you are trying to find a solution in sequential logic. You need three tables. I'll call them Cat1, Cat2, and Cat3, but you should rename them to whatever makes sense to you. They will hold, respectively, 'Sports', 'Football', and 'Players'. (Or 'Business', 'Industry', and 'Firms'. Or whatever.) The table structure would be something like: Cat1(Cat1ID, Category) Cat2(Cat1ID, Cat2ID, Category) Cat3(Cat2ID, Cat3ID, Category) A sample row in Cat1 would be (1, 'Sports') A sample row in Cat2 would be (1, 1, 'Football') A sample row in Cat3 would be (1, 1, 'Players') (If you're not concerned about economizing on storage, you could eliminate the IDs and have each child record refer to the parent category instead of the parent ID. In that case, you don't need the Cat1 table, which serves only to match a category with its ID.) The query would be SELECT Cat1.Category, Cat2.Category, Cat3.Category FROM (Cat1 LEFT JOIN Cat2 ON Cat1.Cat1ID = Cat2.Cat1ID) LEFT JOIN Cat3 ON Cat2.Cat2ID = Cat3.Cat2ID WHERE Cat1.Category = 'Sports' AND (Cat2.Category = 'Football' OR Cat2.Category IS NULL) AND (Cat3.Category = 'Players' OR Cat3.Category IS NULL); If you want the output in the 'Sports:Football:Players' form, you can rewrite the SELECT clause as SELECT Concat(Cat1.Category, If(IsNull(Cat2.Category), '', ':'), Cat2.Category, If(IsNull(Cat3.Category), '', ':'), Cat3.Category) Disclaimer: I haven't run this, so I might have gotten some of the details wrong. Bob Hall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help with a query ...
Hi, thanks for your response. I don't see how the LIKE command would help me pull records that matched the category name or its parent category if none exist, or its parent category if none exists. Perhaps you could provide a example. I want something that follows this logic ... SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE category = 'Sports:Football' OR IF NONE category = 'Sports' Regarding the purpose of this query, it will be performed in every page/category in a web site directory looking to see if an advertisement is available for the current category, if not, it needs to look for one assigned to the parent category, so on and so on until it reaches the top level category for which there will always be an ad. Any suggestions/examples appreciated! John - Original Message - From: "Michael" <[EMAIL PROTECTED]> To: "John Kelly" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, November 02, 2001 7:09 PM Subject: Re: Need help with a query ... > You need to use the LIKE command I think. The MySQL manual should give you > all the details. If you need further help I can just write the whole query > out for you. Or was the problem that you only want to worry about the > if..then stuff if needed? If so could you tell what your pulling back from > that table? > > "The principal mark of genius is not perfection but originality, the > opening of new frontiers." >-- Arthur Koestler > > *^*^*^* > Michael McGlothlin <[EMAIL PROTECTED]> > http://mlug.missouri.edu/~mogmios/projects/ > > On Fri, 2 Nov 2001, John Kelly wrote: > > > Hi, I have a MySQL table with a column that contains some of a web site > > directory's category names in the format: > > > > Sports:Football:Players > > > > I am trying to build a query that that locates all records that match the > > above category name OR if none exist its parent "Sports:Football" OR if none > > exist its parent "Sports". The top level category, in this case "Sports", > > will always have at least one matching record. > > > > I know I can do this with multiple queries by checking the previous query's > > result, but I am trying to build a query that does it all in one lookup to > > avoid lots of lookups in deep categories. Something along the logical lines > > of ... > > > > SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE > > category = 'Sports:Football' OR IF NONE category = 'Sports' > > > > ... of course the above query does not work but if anyone knows of how to > > accomplish something similar in one query I would much appreciate it. > > > > Thanks! > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help with a query ...
You need to use the LIKE command I think. The MySQL manual should give you all the details. If you need further help I can just write the whole query out for you. Or was the problem that you only want to worry about the if..then stuff if needed? If so could you tell what your pulling back from that table? "The principal mark of genius is not perfection but originality, the opening of new frontiers." -- Arthur Koestler *^*^*^* Michael McGlothlin <[EMAIL PROTECTED]> http://mlug.missouri.edu/~mogmios/projects/ On Fri, 2 Nov 2001, John Kelly wrote: > Hi, I have a MySQL table with a column that contains some of a web site > directory's category names in the format: > > Sports:Football:Players > > I am trying to build a query that that locates all records that match the > above category name OR if none exist its parent "Sports:Football" OR if none > exist its parent "Sports". The top level category, in this case "Sports", > will always have at least one matching record. > > I know I can do this with multiple queries by checking the previous query's > result, but I am trying to build a query that does it all in one lookup to > avoid lots of lookups in deep categories. Something along the logical lines > of ... > > SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE > category = 'Sports:Football' OR IF NONE category = 'Sports' > > ... of course the above query does not work but if anyone knows of how to > accomplish something similar in one query I would much appreciate it. > > Thanks! > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
