Re: A SQL Query Question
userId long picture MeduimBlob datePosted DateTime A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this? Latest pic for user N: SELECT userID,MAX(dateposted) FROM tbl WHERE userID=N; Latest pics per user: SELECT t1.userID,t1.dateposted FROM tbl t1 LEFT JOIN tbl t2 ON t1.userID=t2.userID AND t1.datepostedt2.dateposted WHERE t2.userID IS NULL; PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A SQL Query Question
See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of a User Hello everyone, I have a table A: userId long picture MeduimBlob datePosted DateTime A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this? Thanks Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A SQL Query Question
See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of a User select userId, picture, MAX(datePosted) from A order by datePosted; In Response To: Hello everyone, I have a table A: userId long picture MeduimBlob datePosted DateTime A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this? Thanks Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
I just thought of something else... could the same be accomplished
using stored routines? I could find no way in MySQL to create stored
routines which could be used with the 'group by' queries though.
If this were possible, it should then be also possible to define a
'LAST' stored routine, or something which would output a given field
value based on whether some other field (say, numeric ID, or timestamp)
was the highest in its group.
This looks to be possible with external functions ('CREATE AGGREGATE
FUNCTION'), but this would require writing an external library to handle
the call, too. It would be strange it if were impossible to create an
aggregate stored procedure.
Does anyone know if it's possible to define stored procedures this way?
Rob Wultsch wrote:
On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko
[EMAIL PROTECTED] wrote:
Oooh, this looks evil. It seems like such a simple thing. I guess
creating max(log_date) as a field, and then joining on it, is a solution --
but my actual query (not the abridged version) is already half a page long.
I think at this point, unless someone else suggests a better
solution, this would be easier to do programatically -- skip the group
altogether, and instead simply order the rows, and grab the last one for
each username in code.
I guess another alternative would be to use a View for the UNIONized
query, but doesn't MySQL 'fake' views in 5.0 somehow?
I have used views to good results, however I have read not good things
about them. I would not be surprised if they worked well for this use.
I would also not be surprised if the merge storage engine was a better
option for you.
Possibly interesting:
http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/
--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
On Fri, Apr 11, 2008 at 4:01 PM, Victor Danilchenko [EMAIL PROTECTED] wrote: Oooh, this looks evil. It seems like such a simple thing. I guess creating max(log_date) as a field, and then joining on it, is a solution -- but my actual query (not the abridged version) is already half a page long. A derived table or a LEFT JOIN are your best bets, as shown here: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html In most cases, the derived table is best. It creates a temp table automatically, so it's similar to using a view. My experiments with actual views gave dismal performance, and the user variable trick described on Baron's blog is pretty hard to get right. I think at this point, unless someone else suggests a better solution, this would be easier to do programatically -- skip the group altogether, and instead simply order the rows, and grab the last one for each username in code. If you don't have that much data to worry about then this could be good, but it's often tricky to code correctly because of the state you have to keep track of. Also, use UNION ALL if you don't need MySQL to remove duplicate rows. It makes a big difference in performance. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko [EMAIL PROTECTED] wrote: GROUP BY seems like an obvious choice; 'GROUP BY username', to be exact. However, this seems to produce not the last row's values, but ones from a random row in the group. Under most databases your query is erroneous. Take a look at http://lists.mysql.com/mysql/212084 . I don't think the fact that I am doing this on a subquery is relevant, but just in case, I am including this info. Here is what the query looks like, abridged: SELECT id,username,log_date,event_type FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username Read http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Your are probably going to end up with a fairly ugly query (mostly because of the union) with what you have a derived table which will join against a and b again. SELECT if(a2.id is NULL, b2.id, a2.id) ,a1.username ... FROM (SELECT username, MAX(log_date) as mlog_date FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username) AS a1 LEFT JOIN a AS a2 ON a1.mlog_date = a2.log_date AND username... LEFT JOIN b AS b2 ... Any one have a suggestion for how to do with in a way that is not ugly as heck? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
Oooh, this looks evil. It seems like such a simple thing. I guess creating max(log_date) as a field, and then joining on it, is a solution -- but my actual query (not the abridged version) is already half a page long. I think at this point, unless someone else suggests a better solution, this would be easier to do programatically -- skip the group altogether, and instead simply order the rows, and grab the last one for each username in code. I guess another alternative would be to use a View for the UNIONized query, but doesn't MySQL 'fake' views in 5.0 somehow? Rob Wultsch wrote: On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko [EMAIL PROTECTED] wrote: GROUP BY seems like an obvious choice; 'GROUP BY username', to be exact. However, this seems to produce not the last row's values, but ones from a random row in the group. Under most databases your query is erroneous. Take a look at http://lists.mysql.com/mysql/212084 . I don't think the fact that I am doing this on a subquery is relevant, but just in case, I am including this info. Here is what the query looks like, abridged: SELECT id,username,log_date,event_type FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username Read http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Your are probably going to end up with a fairly ugly query (mostly because of the union) with what you have a derived table which will join against a and b again. SELECT if(a2.id is NULL, b2.id, a2.id) ,a1.username ... FROM (SELECT username, MAX(log_date) as mlog_date FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username) AS a1 LEFT JOIN a AS a2 ON a1.mlog_date = a2.log_date AND username... LEFT JOIN b AS b2 ... Any one have a suggestion for how to do with in a way that is not ugly as heck? -- Victor Danilchenko Senior Software Engineer, AskOnline.net [EMAIL PROTECTED] - 617-273-0119 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko [EMAIL PROTECTED] wrote: Oooh, this looks evil. It seems like such a simple thing. I guess creating max(log_date) as a field, and then joining on it, is a solution -- but my actual query (not the abridged version) is already half a page long. I think at this point, unless someone else suggests a better solution, this would be easier to do programatically -- skip the group altogether, and instead simply order the rows, and grab the last one for each username in code. I guess another alternative would be to use a View for the UNIONized query, but doesn't MySQL 'fake' views in 5.0 somehow? I have used views to good results, however I have read not good things about them. I would not be surprised if they worked well for this use. I would also not be surprised if the merge storage engine was a better option for you. Possibly interesting: http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/ -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
In the last episode (Jan 22), Adam Bishop said: If I have a dataset as below: Name, Age, Word Bob, 13, bill Joe, 13, oxo Alex, 14, thing Jim, 14, blob Phil, 14, whatsit Ben, 15, doodah Rodney, 15, thingy I want to select the first block where the age is equal, i.e. return in the case of the above set, Bob and Joe, and with the same query if Bob and Joe didn't exist, return Alex, Jim, and Phil. In broken SQL, I want to SELECT * FROM `table` WHERE 'Age'='Age 1st Row';. How about: SELECT * FROM mytable WHERE Age=MIN(age); The smallest and largest values for a column are alawys available via MIN() and MAX(). If you had wanted the 2nd smallest, or the top 3, then you would have needed a subquery. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
Ah, that would work. Looks like I was making the problem too complex in my mind, thanks for your help. Adam Bishop -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: 22 January 2007 07:07 To: Adam Bishop Cc: mysql@lists.mysql.com Subject: Re: SQL Query Question In the last episode (Jan 22), Adam Bishop said: If I have a dataset as below: Name, Age, Word Bob, 13, bill Joe, 13, oxo Alex, 14, thing Jim, 14, blob Phil, 14, whatsit Ben, 15, doodah Rodney, 15, thingy I want to select the first block where the age is equal, i.e. return in the case of the above set, Bob and Joe, and with the same query if Bob and Joe didn't exist, return Alex, Jim, and Phil. In broken SQL, I want to SELECT * FROM `table` WHERE 'Age'='Age 1st Row';. How about: SELECT * FROM mytable WHERE Age=MIN(age); The smallest and largest values for a column are alawys available via MIN() and MAX(). If you had wanted the 2nd smallest, or the top 3, then you would have needed a subquery. -- Dan Nelson [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: Calculating User Ranks (SQL Query Question)
On 22-Dec-2004 Michael J. Pawlowsky wrote:
Im trying to come up with a more efficient method to do this.
I have a table where people enter some info into the table.
snip
I would like to allow the users to be able to see where they stand
rank
wise with everyone else.
Right now I basically do a SELECT count(1) as entries, user_id GROUP
BY
user_id ORDER BY entries DESC.
snip
I was wondering if anyone could think of a better way to do this.
Add a rank column that gets recalculated after each entry is added?
php code:
function calc_user_ranks($limit=100) {
global $dbconn;
$dbconn-Execute('SET @x:=0');
$qry = SELECT (@x:[EMAIL PROTECTED]) AS rank, user_id, count(1) AS
entries
FROM user
GROUP BY user_id ORDER BY entries DESC LIMIT $limit;
$dbconn-Execute($qry);
while($row = $r-FetchRow())
$rank[$row['user_id']] = $row['rank'];
$dbconn-Execute('UPDATE user SET rank=0');
foreach($rank as $id = $r) {
$qry = UPDATE user SET rank=$r WHERE user_id=$id;
$dbconn-Execute($qry);
}
}
Regards,
--
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]
Calculating User Ranks (SQL Query Question)
Im trying to come up with a more efficient method to do this. I have a table where people enter some info into the table. The more entries they add the more points they get. (1 point per entry). I would like to allow the users to be able to see where they stand rank wise with everyone else. Right now I basically do a SELECT count(1) as entries, user_id GROUP BY user_id ORDER BY entries DESC. Then loop through the results until I match their user_id and count how many times I go through the loop and that is how I can give them their ranking. It just seems like a big waste to loop through the results until I find their user_id. I was wondering if anyone could think of a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculating User Ranks (SQL Query Question)
Try this: CREATE TEMPORARY TABLE tmpRankings ( Rank int auto_increment, entries int, user_id int ) INSERT tmpRankings (points, user_id) SELECT count(1), user_id FROM sometablenamehere GROUP BY user_id ORDER BY entries DESC; This way the tmpRankings table contains an ordered, numbered list of all of your user_id's (in proper ranking order). If you change tmpRankings from a Temporary table to a permanent table you could rebuild it each time someone makes a new entry. That would save you the overhead of recomputing the entire table each time someone just wants to know their ranking but the data hasn't changed because nobody has made any entries since the last check. There are other tweaks you can do to save recomputes, too, but this is the general idea. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 12/22/2004 04:00:45 PM: Im trying to come up with a more efficient method to do this. I have a table where people enter some info into the table. The more entries they add the more points they get. (1 point per entry). I would like to allow the users to be able to see where they stand rank wise with everyone else. Right now I basically do a SELECT count(1) as entries, user_id GROUP BY user_id ORDER BY entries DESC. Then loop through the results until I match their user_id and count how many times I go through the loop and that is how I can give them their ranking. It just seems like a big waste to loop through the results until I find their user_id. I was wondering if anyone could think of a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael Michael J. Pawlowsky wrote: Im not sure if this is possible or not. I have a Sales leads table. Part of the table has 2 employee_ids. 1. The Sales person the lead is assigned to. 2. The Marketing person that generated the lead. Then there is a employee table that has ids and names. When generating a report for leads I would like to lookup the name of the employee. I know I can do it with a seperate query, but I'm wondering if I can also do it in one query. Something like: SELECT employee.name as sales_name, employee.name as marketing_name, leads.id FROM leads, employee WHERE employee.id = leads.salesid AND employee.id = leads.marketingid Is there someway this can be done? Thanks for any assistance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Right. If the employee ID in either the rep_no or entered_by columns does not have a corresponding row in the global_employee table, then the regular join won't match that row. In that case, as you found, you need a LEFT JOIN, which guarantees you get the rows from the table on the left, and auto-creates NULL fields for the table on the right when it has no matching row. For reference, this is mentioned in the manual http://dev.mysql.com/doc/mysql/en/JOIN.html. Michael Michael J. Pawlowsky wrote: Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
The you will need to use the second format. DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 4:09 PM Subject: Re: SQL Query Question - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- 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: SQL Query Question
If you do any math on your column, no index on the column can be used. If possible, you should always try to write your condition so that the calculations are done on the value(s) to compare to, not on the column. So, assuming you have no rows with future timestamps, something like this should work: SELECT * FROM yourtable WHERE queue_time BETWEEN CURDATE() AND NOW(); If you might have timestamps later than NOW for some reason, you could change this to something like: SELECT * FROM yourtable WHERE queue_time BETWEEN CURDATE() AND (CURDATE + INTERVAL 1 DAY - INTERVAL 1 SECOND); To select yesterday's rows, you could do something like: SELECT * FROM yourtable WHERE queue_time BETWEEN (CURDATE() - INTERVAL 1 DAY) AND (CURDATE() - INTERVAL 1 SECOND); To help you visualize what's happening here, try SELECT CURDATE(), NOW(), CURDATE() + INTERVAL 1 DAY - INTERVAL 1 SECOND, CURDATE() - INTERVAL 1 DAY, CURDATE() - INTERVAL 1 SECOND; You say you've been wrestling with the docs, so you probably already seen these, but just in case: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html Michael Victor Pendleton wrote: The you will need to use the second format. DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() -Original Message- From: Dirk Bremer (NISC) - Original Message - From: Victor Pendleton [EMAIL PROTECTED] If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
This works for Oracle, give it a try, use any format you want for the MM/DD/YY area. select to_char(queue_time, 'MM/DD/YY'); Scott Purcell -Original Message- From: Dirk Bremer (NISC) [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 2:55 PM To: [EMAIL PROTECTED] Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- 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: SQL Query Question
WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- 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: SQL Query Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 3:25 PM Subject: Re: SQL Query Question - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- 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: SQL Query Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Fri, 2004-04-16 at 14:09, Dirk Bremer (NISC) wrote: - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. You could keep any index you have and do it this way: SELECT a,b FROM x WHERE queue_time BETWEEN date_format(curdate(), %Y%m%e00) AND date_format(curdate(), %Y%m%e235959); -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: SQL Query Question
Hello, For my final solution I decided to use the inner join method. The query is created dynamically based upon a user interface component that allows people to build queries using parenthesis, ands and or's. Plus there is another field that I didn't include in the original question so as to keep the problem focused. So here is the basic structure of what I did, each query starts with the standard select table1.*,table2.* from table1,tabl2, then I append the inner joins for each search able field. The user can choose to search for one value or many values. Realistically I don't expect this to go above 3 to 5 fields. searchtable s1,searchtable s2 Then I include the queries where (table2.id=s1.rdid and then the dynamic part ((s2.vid=10109 and s2.value=5) and (s1.vid=10089 and s1.value4000))) now I make sure all the searchtables are matched and (s1.rdid=s2.rdid) then the rest of my query which binds table1 to table2 but that irrelevant to this discussion and blah blah blah Here is an example select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value=5) and (s1. vid =10089 and s1.value4000))) and (s1.rdid=s2.rdid) and blah blah blah This works really fast for a table with about 20,000 records. I hope it works against a large table. But unfortunately when I add an 'or' it really slows down by about 5 fold. select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and s3.value=5) and (s1. vid =10089 and s1.value4000)) or (v2. vid =10096 and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) and blah blah blah Thanks for everyone's help! I can't tell you how much I appreciate it. Joe On Monday, January 19, 2004, at 07:30 PM, Jochem van Dieten wrote: Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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: SQL Query Question
I think I figured out the time problem. If I make s2 in the or s1 and remove any instances of s2 it works very fast with the 'or'. Joe On Tuesday, January 20, 2004, at 09:50 AM, sulewski wrote: Hello, For my final solution I decided to use the inner join method. The query is created dynamically based upon a user interface component that allows people to build queries using parenthesis, ands and or's. Plus there is another field that I didn't include in the original question so as to keep the problem focused. So here is the basic structure of what I did, each query starts with the standard select table1.*,table2.* from table1,tabl2, then I append the inner joins for each search able field. The user can choose to search for one value or many values. Realistically I don't expect this to go above 3 to 5 fields. searchtable s1,searchtable s2 Then I include the queries where (table2.id=s1.rdid and then the dynamic part ((s2.vid=10109 and s2.value=5) and (s1.vid=10089 and s1.value4000))) now I make sure all the searchtables are matched and (s1.rdid=s2.rdid) then the rest of my query which binds table1 to table2 but that irrelevant to this discussion and blah blah blah Here is an example select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value=5) and (s1. vid =10089 and s1.value4000))) and (s1.rdid=s2.rdid) and blah blah blah This works really fast for a table with about 20,000 records. I hope it works against a large table. But unfortunately when I add an 'or' it really slows down by about 5 fold. select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and s3.value=5) and (s1. vid =10089 and s1.value4000)) or (v2. vid =10096 and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) and blah blah blah Thanks for everyone's help! I can't tell you how much I appreciate it. Joe On Monday, January 19, 2004, at 07:30 PM, Jochem van Dieten wrote: Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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: SQL Query Question
Let me post the question this way, MyTable --- pointerid valueid 811 54 811 63 812 100 813 200 814 300 815 400 I want all the records in MyTable where (valueid=54 and valueid=63) or valueid=400 group by pointerid Which means I would get the records whose pointer id is 811 and 815 Thanks, Joe On Monday, January 19, 2004, at 03:03 PM, Jamie Murray wrote: Joe didn't you already post this question last week and have it correctly answered by Roger ? I only ask because at that time I saw the query and thought to myself that the left outer join solution posted by Roger would not give you the results you had expected. - Original Message - From: sulewski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 3:47 PM Subject: SQL Query Question Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- 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]
Fwd: SQL Query Question
The ands are killing you in regards to what no data returned is that what you mean. Yes, I'm not getting any data on a return. Because the vid can only be one value not both. because (vid=54 and vid=65) which you already know means both have to succeed and if there is no data to match that criteria then you get nothing and the query will move to or vid=100 . You got it. Question just to make sure but you only want records from tab1 that match tab2 and have (vid=54 and vid=65) . In the case of (vid=54 or vid=65) it short curcuits and finds one or the other so yes it will succeed most likely evertime in your case. tab1tab2 id = 1 id = 1 vid = 54 id = 1 vid = 64 this above is what you mean by many to one correct. Yes this is correct. And yes, to make the match it's a simple join. Shouldn't this be a simple join? just for starters here is simple example select tab1.vals from tab1,tab2 where (tab2.vid = 54 and tab2.vid = 65) and tab1.id = tab2.id; This doesn't work because vid can only be one integer per record. So your example doesn't work because no vid field can be both 54 and 65. It's one or the other. I want to find all records in table 1 that will link to a single record in tab2 with a value of 54 and another record with the value 64. so this says return all records from tab2 where vid = 54 and 65 which return specific ID'S from tab2 which go with tab2 vid . so this is a subset of data from table2 which will act like a filter and return the correct matches from table1 when we join ID columns of both tables in an equijoin. Is this along the line of what you are already doing , please comment and maybe I can help somemore. I think you get what I want to do. So how do I do it? :) - Original Message - From: sulewski [EMAIL PROTECTED] To: Jamie Murray [EMAIL PROTECTED] Sent: Monday, January 19, 2004 4:41 PM Subject: Re: SQL Query Question I asked a similar question but this time it's different. Last time I was looking for places where the record in table 1 didn't have a link to table 2. Now I wish to find all the records in table 1 that contain multiple links to table 2. The trouble is that I wish to and and or these links together. So I want to say, find all the records in table 1 where table 2 has the following values (vid=54 and vid=65) or vid=100 etc. Before it was finding one missing link this time it's finding many links. It works fine with or'ed values. find al the records in table 1 where table 2 has the follwing values (vid=54 or vid=65). It's the ands that are killing me because the vid is an integer field and i'm not really trying to find two values in the same record but two values in different records. Does this make better sense? Joe On Monday, January 19, 2004, at 03:03 PM, Jamie Murray wrote: Joe didn't you already post this question last week and have it correctly answered by Roger ? I only ask because at that time I saw the query and thought to myself that the left outer join solution posted by Roger would not give you the results you had expected. - Original Message - From: sulewski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 3:47 PM Subject: SQL Query Question Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- 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: SQL Query Question
On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) I *THINK* you're asking for the case where it has one of these values. In that case try Select Table1.* from Table1 t1 JOIN Table2 t2 on t1.ID = t2.rdid Where t2.vid in (46, 554) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
Or, if I'm not mistaken, you could do something like: SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.id = t2.rdid AND t2.vid IN (46, 554) ; That should work, but the joining thing should too. I can never get the joining straight, so I always enjoy a shorter route. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 4:39 PM To: [EMAIL PROTECTED] Subject: Re: SQL Query Question Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- 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: SQL Query Question
Jochem, I believe this works. This is also easy to build dynamically. The query is going to be generated based upon some user input. Thank you very much, Joe On Monday, January 19, 2004, at 04:38 PM, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- 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: SQL Query Question
Lincoln Milner said: Or, if I'm not mistaken, you could do something like: SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.id = t2.rdid AND t2.vid IN (46, 554) ; That should work No. You are back to square one where there should only be one record in t2 with a vid of either 46 or 554. What is requested is a solution where there are 2 records in t2, one with a vid of 46 and one with a vid of 554. I can never get the joining straight, so I always enjoy a shorter route. Just make sure the shorter route gives the same results. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). If you absolutely need to do it with a join you might try Select t1.* from Table1 t1 JOIN Table2 t2 on (t1.id = t2.rdid and (t2.vid = 46 or t2.vid = 554)) I don't think this is as clear as a simple join with a Where, but there are a lot of ways to get the same result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? If you absolutely need to do it with a join you might try Select t1.* from Table1 t1 JOIN Table2 t2 on (t1.id = t2.rdid and (t2.vid = 46 or t2.vid = 554)) I don't think this is as clear as a simple join with a Where, but there are a lot of ways to get the same result. Build the tables, run the queries, compare the results. There are even more ways to get a different result, and yours is one of them. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of, and it's irrelevent. Table1 (t1) isn't used at all in the join parameters. As records from table1 are required in the result, this won't work as desired. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query question
Pael, Try this: SELECT firmal.beskrivelse as Businessline, lokasjon.navn as Location, count(person.[uniqueid]) FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid GROUP BY firmal.beskrivelse, lokasjon.navn Replace [uniqueid] with the primary key of the person table. Andy -Original Message- From: Paal Eriksen [mailto:[EMAIL PROTECTED] Sent: 11 November 2003 12:11 To: [EMAIL PROTECTED] Subject: SQL query question Hi, i have the following query: SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as Location FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid which will give me a list of Name, Businessline, Location. What i'm trying to do is to get a list which is grouped on Businessline and Location. Then i want to list a count of name at each location and businessline. How can i achieve this, if it's possible? So it should be like this: Businessline, Location, Sum people A AA 10 A AB 30 B AA 5 B AB 27 B AC 90 Paal Ny versjon av Yahoo! Messenger Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query question
So close, Thanks you very much Andy. I tried one similar to your suggestion, but didn't get quite the result i expected. Cheers Paal Ny versjon av Yahoo! Messenger Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom
Re: SQL query question
try group by SELECT firmal.beskrivelse as Businessline, lokasjon.navn as Location, count(person.name) as Sum People FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid group by firmal.beskrivelse, lokasjon.navn -leo- From: Paal Eriksen To: [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:11 PM Subject: SQL query question SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as Location FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid Businessline, Location, Sum people A AA 10 A AB 30 B AA 5 B AB 27 B AC 90
RE: SQL query question
Rolf, You need to separate your functions. You are adding complexity to your world by storing irrelvant infromation in your database. Critical Data Handling (in a proper world) is ALWAYS handled separately from display. So in your example, You are storing all the html display formatting in your database. Bad idea. Not only does it complicate your SQL like you have found out... but selecting something that your looking for becomes unreadable. You should do the following. 1) Add a simple index to your table... i.e. an ever increasing number... that will always be unique to the line. Either that, or a short name that describes the line that you're looking for 2) Alter your database table to only store the critical information. Such as... ftp://cc278355-a.groni1.gr.home.nl/c64/games/--- The URL that alters.. --- The Filename --- The URLDesc --- The desc Then In the ASP, you say Select * from table1 WHERE (Either unique ID = X) or ( ShortName = 'GameName1' ); Then when you get the recordset out you handle it in asp with... % // Start ASPN insert data inline...% HTML FORMATTING HERE %=RS.(URL)% MORE HTML STUFF HERE LIKE P and FONT and ALIGN %=URLDESC% Etc Etc That would be proper programming form... Did that make sense? -Original Message- From: Rolf C [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 4:57 PM To: [EMAIL PROTECTED] Subject: SQL query question Hello all, I am a totally newby to MYSQL but here i go. I want to create an ASP page that shows an image (screendump of game) a game name a game description and an url. I created a database with the following table: filename, urldesc, desc Now i have to create an SQL query that will put this information in a webpage: this is what i got. SELECT ' img border=0 src=' filename '.gif /p td a href=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.zip ' urldesc '/a td p ' desc ' /p td ' FROM table1; The zip file for the download and the gif file for the image have the same filename exept the extention. It nearly works but it won't show all the text in the description. Any ideas? _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- 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: SQL query question
Well, from what limited info I have, it looks like your image tag is not closed properly. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Rolf C [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 2:57 PM To: [EMAIL PROTECTED] Subject: SQL query question Hello all, I am a totally newby to MYSQL but here i go. I want to create an ASP page that shows an image (screendump of game) a game name a game description and an url. I created a database with the following table: filename, urldesc, desc Now i have to create an SQL query that will put this information in a webpage: this is what i got. SELECT ' img border=0 src=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.gif /p td a href=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.zip ' urldesc '/a td p ' desc ' /p td ' FROM table1; The zip file for the download and the gif file for the image have the same filename exept the extention. It nearly works but it won't show all the text in the description. Any ideas? _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- 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: SQL query question - using LIKE
Hi $query = 'SELECT * FROM Table WHERE FirstName LIKE %'.$firstname.'%' ; if($lastname)$query.= ', AND LastName LIKE %'.$lastname.'%'; if($region)$query.= ', AND Region LIKE %'.$region.'%'; if($loan_officer)$query.= ', AND Loan_officer LIKE %'.$loan_officer.'%'; etc. $mysql_result = mysql_query($query, $mysql_link); HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: 28 March 2002 17:21 To: MySQL Subject: SQL query question - using LIKE I have a simple search form that allows the user to search a contact db based on criteria that they choose. Search by - first name - text box last name - text box region - drop down loan officer - drop down I want the user to be able to receive results if they choose all possible criteria or just one criteria. My question is about query structure. How do I query the database when I have multiple criteria selected? Any help is appreciated. Mark - 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: SQL query question - using LIKE
Mark, Peter, The query below assumes that the user will search of FirstName and none or more of the other fields - which was not how the question reads (to me). To answer the question it would be useful to know how you are accessing MySQL - are you using PHP (as per example code below) for example? When the form data is received it is (very) necessary to 'clean' and validate the data. Part of this process involves the question was this field filled out?. Thus front end-processing is the time for the query's WHERE clause to be built up. The last question is going to be has at least one field been filled out?! The methodology of Peter's reply still applies. There are a number of tutorials available on various sites (but can't point you at one because don't know which tool you're using!!!). Would certainly recommend researching a few... Regards, =dn $query = 'SELECT * FROM Table WHERE FirstName LIKE %'.$firstname.'%' ; if($lastname)$query.= ', AND LastName LIKE %'.$lastname.'%'; if($region)$query.= ', AND Region LIKE %'.$region.'%'; if($loan_officer)$query.= ', AND Loan_officer LIKE %'.$loan_officer.'%'; etc. $mysql_result = mysql_query($query, $mysql_link); HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: 28 March 2002 17:21 To: MySQL Subject: SQL query question - using LIKE I have a simple search form that allows the user to search a contact db based on criteria that they choose. Search by - first name - text box last name - text box region - drop down loan officer - drop down I want the user to be able to receive results if they choose all possible criteria or just one criteria. My question is about query structure. How do I query the database when I have multiple criteria selected? Any help is appreciated. Mark - 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 - 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: SQL query question - using LIKE
DL -
Points well taken -
I am using php and doing simple validation
EX - if ($fname !=) {
add fname string to search variable;
}
And if none of the fields have been filled out I'll return an error msg.
As you mentioned, Peter's logic still applies here and it has given me
enough to work with.
But I am certainly open for more suggestion/ feedback.
Thanks
Mark
-Original Message-
From: DL Neil [EMAIL PROTECTED]
To: Peter Lovatt [EMAIL PROTECTED]; Mark Stringham
[EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Date: Thursday, March 28, 2002 11:52 AM
Subject: Re: SQL query question - using LIKE
Mark, Peter,
The query below assumes that the user will search of FirstName and none
or more of the other fields - which was not how the question reads (to
me).
To answer the question it would be useful to know how you are accessing
MySQL - are you using PHP (as per example code below) for example?
When the form data is received it is (very) necessary to 'clean' and
validate the data. Part of this process involves the question was this
field filled out?. Thus front end-processing is the time for the
query's WHERE clause to be built up. The last question is going to be
has at least one field been filled out?!
The methodology of Peter's reply still applies. There are a number of
tutorials available on various sites (but can't point you at one because
don't know which tool you're using!!!). Would certainly recommend
researching a few...
Regards,
=dn
$query = 'SELECT *
FROM Table
WHERE
FirstName LIKE %'.$firstname.'%' ;
if($lastname)$query.= ', AND LastName LIKE
%'.$lastname.'%';
if($region)$query.= ', AND Region LIKE %'.$region.'%';
if($loan_officer)$query.= ', AND Loan_officer LIKE
%'.$loan_officer.'%';
etc.
$mysql_result = mysql_query($query, $mysql_link);
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
---
-Original Message-
From: Mark Stringham [mailto:[EMAIL PROTECTED]]
Sent: 28 March 2002 17:21
To: MySQL
Subject: SQL query question - using LIKE
I have a simple search form that allows the user to search a
contact db
based on criteria that they choose.
Search by -
first name - text box
last name - text box
region - drop down
loan officer - drop down
I want the user to be able to receive results if they choose all
possible
criteria or just one criteria.
My question is about query structure. How do I query the database
when I
have multiple criteria selected?
Any help is appreciated.
Mark
-
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
-
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: SQL query question - using LIKE
Mark,
Tutorials: Start at the PHP home page and look for the links page.
DevShed would be a good start.
Regards,
=dn
- Original Message -
From: Mark Stringham [EMAIL PROTECTED]
To: DL Neil [EMAIL PROTECTED]; Peter Lovatt
[EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Sent: 28 March 2002 19:16
Subject: Re: SQL query question - using LIKE
DL -
Points well taken -
I am using php and doing simple validation
EX - if ($fname !=) {
add fname string to search variable;
}
And if none of the fields have been filled out I'll return an error
msg.
As you mentioned, Peter's logic still applies here and it has given me
enough to work with.
But I am certainly open for more suggestion/ feedback.
Thanks
Mark
-Original Message-
From: DL Neil [EMAIL PROTECTED]
To: Peter Lovatt [EMAIL PROTECTED]; Mark Stringham
[EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Date: Thursday, March 28, 2002 11:52 AM
Subject: Re: SQL query question - using LIKE
Mark, Peter,
The query below assumes that the user will search of FirstName and
none
or more of the other fields - which was not how the question reads
(to
me).
To answer the question it would be useful to know how you are
accessing
MySQL - are you using PHP (as per example code below) for example?
When the form data is received it is (very) necessary to 'clean' and
validate the data. Part of this process involves the question was
this
field filled out?. Thus front end-processing is the time for the
query's WHERE clause to be built up. The last question is going to be
has at least one field been filled out?!
The methodology of Peter's reply still applies. There are a number of
tutorials available on various sites (but can't point you at one
because
don't know which tool you're using!!!). Would certainly recommend
researching a few...
Regards,
=dn
$query = 'SELECT *
FROM Table
WHERE
FirstName LIKE %'.$firstname.'%' ;
if($lastname)$query.= ', AND LastName LIKE
%'.$lastname.'%';
if($region)$query.= ', AND Region LIKE %'.$region.'%';
if($loan_officer)$query.= ', AND Loan_officer LIKE
%'.$loan_officer.'%';
etc.
$mysql_result = mysql_query($query, $mysql_link);
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
---
-Original Message-
From: Mark Stringham [mailto:[EMAIL PROTECTED]]
Sent: 28 March 2002 17:21
To: MySQL
Subject: SQL query question - using LIKE
I have a simple search form that allows the user to search a
contact db
based on criteria that they choose.
Search by -
first name - text box
last name - text box
region - drop down
loan officer - drop down
I want the user to be able to receive results if they choose all
possible
criteria or just one criteria.
My question is about query structure. How do I query the
database
when I
have multiple criteria selected?
Any help is appreciated.
Mark
-
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
-
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: SQL query question?
It's not possible to do in one step in MySQL. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.com/ "roger westin" [EMAIL PROTECTED] wrote: So a question I have two tables. And i want to... (i just show you) Table 1. OwnerChar(30)Uniqe FileChar(80) OIDint(not in use yet) Table 2. IDintuniqe and so on OwnerChar NameChar adress etc So I want to give the OID in table 1 the corresponding ID from Table 2 so that I may remove The Owner Col. I can do it with using tmp tables and so on, ( my knowlage in SQL querys is wery limited), But I want to do it with Just ONE singel SQL line Any ideas? /roger - 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
