How To Make Sql Query For Comparison Condition
how to make sql query for comparison condition.
in case,i want to generate report between range two date for example 1/2/2007 until 20/2/2007?
View Complete Forum Thread with Replies
Related Forum Messages:
Comparison Query?
I have two tables in my database that collect membership/user info. How do I write a query to compare the two and have the query spit out the users that are on one but not the other?
View Replies !
Comparison Query
I have two tables, I have a company table and a client table. The fields in question are as follows Company.companyid Company.maincontactid Contact.contactid Due to bad design and bad data entry we have a bunch of companies with a contact number but no entry in the contact table. I want to run a query to dump a list of Company.companyid and Company.maincontactid where Company.maincontactid has no corrisponding Contact.contacid
View Replies !
Query By Column Comparison
I'm trying to pull a row by comparing that the numerical value of one column is greater than the numerical value of other columns. The manual is quite confusing as to how to phrase such a thing, and I haven't done much more than simple queries up until now. MySQL version: 4.0.22 What I started with that doesn't work: select * from someTable where col1 > col2 and col1 > col3 and col1 > col4 order by someThing limit 1 I'm basically trying to grab one row where the value of one column is greater than the values of the remaining similar columns.
View Replies !
More Than 1 Condition In Same Query
I want to select the table and then order it by the start-date which is one of the column and check if the the start date is gone as in start-date > current_time(the time when the query is performed) and then report the relevant top 3 results!
View Replies !
Query Condition Didn't Get Logged
In slow query log query condition didn't logged. Only query with table name omitting condition get logged. What is the reason. How to log the entire query. The following query takes 2s to execute and is logged in slow query log. The original query is : Select count(*) from markingqc where persondate between '2007-07-09 00:00:00.000' and '2007-07-09 23:59:59.999' and PersonName='admin' Logged query is: SELECT * FROM markingqc; How to get the condition in the log?
View Replies !
Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' with a field representing the topic author's username. I then have another table called 'ignorelist' which has two fields; one is an account number and the other a reference to the aforementioned author. There can be many ignored authors for any one account. For any given account number, I'd like all the rows from the forumtopics table where there is no match for (account, author) held in the ignorelist table. Obviously it's easy to check for existence but can the opposite be done? I run MySQL 4.0.17.
View Replies !
Query To Ignore Results If Condition True
I am having trouble defining a query that ignores rows if a query if true IE. Column 1 = 1,2,3,4,5 Column 2 = a,b,a,a,c For example: I search for results from column 1 and if '3=a', ignore all 'a' rows thus returning the result b=>2, c=>5.
View Replies !
Make A COUNT Query
I want to run a query on quite a big table, about 3,000,000 records. The query is quite simple SELECT COUNT(*) FROM TblName WHERE Something = 1 I am only interested to know whether there are at least 10,000 records that meet this condition, so counting the whole table and then comparing the result to 10,000 is overhead. Is there a way to write a query that would simply tell me if there are at least 10,000 (or any other number) records, and would stop executing/counting once it gets to 10,000?
View Replies !
What Do I Need To Do To Make This Query Work?
I must've been working too long hours this week, as my flow of ideas has come to a complete stop. Its really quite simple: 2 tables - bookings , invoices These two counts gives me precisely what I want PHP $unpaid_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings WHERE invoice_id = 0 AND client_id = $id AND status != Ɔ' AND finish <= '$time'"),0);$total_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings WHERE client_id = $id AND status != Ɔ' AND finish <= '$time'"),0); so I present this in a ratio, $unpaid_bookings / $total_bookings... awesome now, when I try and flip the coin and show unpaid invoices over total invoices, it all falls apart once I have more than 1 booking per invoice. First i'll explain the table structure. Each booking has a column `invoice_id`, which is assigned to the primary `id` of the invoice table. So one invoice might be associated with x bookings. PHP $unpaid_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b WHERE i.id = b.invoice_id AND b.client_id = $id AND i.is_paid = Ɔ'"),0);$total_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b WHERE i.id = b.invoice_id AND b.client_id = $id"),0); Again so it works fine when 1 invoice is associated to 1 booking, but when 1 invoice is associated with say 2 bookings, the invoice count will return two instead of one. The worst part is, I know exactly why (because there are two bookings for the invoice), I just can't get around it! I've tried all sorts of joins, group by's, and i'm not having any luck. I'm trying not to be weak and add the client_id into the invoice table,
View Replies !
Query To Make One Column Into Multiple Columns
The table is as such CREATE TABLE `hardware_table` ( `timestamp` int(11) NOT NULL default '0', `node_ref` int(11) NOT NULL default '0', `var_ref` int(11) NOT NULL default '0', `value` varchar(255) default NULL, `t_date` date NOT NULL, `t_time` time NOT NULL, KEY `timestamp` (`timestamp`), KEY `node_ref` (`node_ref`), KEY `var_ref` (`var_ref`), KEY `t_date` (`t_date`), KEY `t_time` (`t_time`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; node_ref is a link to the hostname var_ref is a link to the variable etc. I get a column for each varname instead of all of them under the varname column. Getting close with the following.....
View Replies !
How Do I Make Rows From Another Table Appear As Columns In Query?
I have a join query (which r937 kindly helped me out with) which joins a table of tracks with a table of albums in a many-to-many relationship. I also have a table called links with a column of URLs called links. There's also a column of link categories called linkTypes which is of type enum with three possibilities - artist, album and track. What I'd really like to do is to add three columns to the result set (if that's the correct term) of the join from above i.e. I'd like to add trackLinks, artistLinks, albumLinks. So I guess my question is: How can I make columns on the fly by using field values from another table? In answer to the first thquestion - why don't you just add the columns to those two tables? The answer is because in the future I will need an undetermined number of each of these columns, e.g. one artistLink column per affiliate - artistLinkItunesUS, artistLinkItunesUK etc. So is this something I can feasibly do without restructuring the database or should I use a couple of queries and try combine the data through PHP? Or perhaps I'm looking at the relationships incorrectly? Having to add columns like that does seem like a one-to-many relationship which I haven't taken care of.
View Replies !
Howto Make A Query Based On Another Result?
I need to make query from another query result. tbl_A fields: ID and Date tbl_B fields: ID and FileName Code: Select ID FROM tbl_A where Date>=CURDATE() this 1st_query_result will be any ID with current date from tbl_A. Then I need this 1st_query_result to query the filename which store at tbl_B. something like this: Code: select FileName from tbl_B where ID = 1st_query_result
View Replies !
Incorporate An Additional WHERE Condition (was "Help With My Query Please?")
Iām trying to incorporate an additional statement in the below query, which works: $sql = "SELECT b.bookID, b.roomID, b.arrive, b.depart, b.status, b.property_id FROM room_booking_two b WHERE ((b.arrive BETWEEN '$d1' AND '$d2') OR (b.depart BETWEEN '$d1' AND '$d2')) ORDER BY b.roomID, b.arrive"; Now I want to add the line: WHERE b.property_id=".$_GET['property_id']) So I thought it would work like this: $sql = "SELECT b.bookID, b.roomID, b.arrive, b.depart, b.status, b.property_id FROM room_booking_two b WHERE b.property_id=".$_GET['property_id']) AND ((b.arrive BETWEEN '$d1' AND '$d2') OR (b.depart BETWEEN '$d1' AND '$d2')) ORDER BY b.roomID, b.arrive"; But that does not seem to work. Can anyone please advise?
View Replies !
Unique Combinations Of Model And Make (was "Query Question")
I'm trying to write a query and was wondering if someone could help me formulate it. Here is an example table I'm working with: Table: vehicles Model Make Ford Taurus Ford Taurus Ford F150 Chevy Tahoe Chevy Blazer Chevy Tahoe I want a query that returns all unique combinations of model and make. So the result set should be: Model Make Ford Taurus Ford F150 Chevy Blazer Chevy Tahoe I know how to return distinct values for one variable, but not for two like that. Any help is greatly appreciated.
View Replies !
Query Won't Use Index On "OR" Condition
I have a table full of teams: Code: team_id, name And a table full of fixtures: Code: fixture_id, home_id, away_id, date The fixtures table is now quite large and this week I added indexes on the "home_id" and "away_id" columns (in addition to the existing primary key on fixture_id). I run a query which joins on the following: Code: JOIN fixtures ON (fixtures.home_id = teams.team_id OR fixtures.away_id = teams.team_id) But it does not use my indexes for the join.... it therefore scans around 40,000 rows. If I remove the "OR fixtures.away_id = teams.team_id" and just join the home ones, the number of rows scanned goes down to 11. Is there a reason that the "OR" operator stops the index being used? Do I need to re-write my sql so that it searches 11 rows twice (one home, one away) instead of doing the "OR" clause?
View Replies !
Md5 Comparison
UPDATE admin_users SET password = md5(?') WHERE id = 1; SELECT password = md5(?') FROM admin_users WHERE id = 1; The result of the last query is "0", which means the values don't match. How can that be? What am I missing? The hashes look just the same when I echo them. Is it a data type problem? EDIT: I should mention the m5 password hash is stored in a binary(64) column.
View Replies !
Comparison In Db.
Don't know if it's even do-able in MySQL but I kinda think it is. lets make an example table over a log. day - action 1 ---- foo 1 ---- bar 2 ---- foo 2 ---- foo 2 ---- bar 2 ---- bar 2 ---- foo Now I want, with one query, most of all, select the increase in percent and the name of the action that has increased the most from day one to day two. If this isn't possible I would like to select the name, the amount for the action on day one and the increase of the top increaser.
View Replies !
Comparison
I want to compare a text box value with the one stored in the mysql table. Here is the input of text box. HTML Code: 352969678 and the mysql table record is like HTML Code: 352 969678 a space is there after the first 3 digits. So i want to compare it via SQL. Suppose that the data from the text box is in $data and the column in table is "phone" so i want to do this (in PHP): HTML Code: "select * from some_table where phone = ".$data But it will not work as the data in the $data has not space after the first 3 digits while the record in table in database is having a space after the first 3 digits...
View Replies !
Database Comparison
I am looking for some papers/information that compare relational databases such as oracle, mysql, sql server etc. I am particularly interested in their features such as locking mechanisms, integrity constraints, views... Anyone know where I can find the information?
View Replies !
Text Comparison
I'm having a problem with one query. My database has the following fields: id(int), time(int), and groupfield(enum - 5 possibilities). I want to select all the rows where the time is between let's say 12 and 24 and the group is specified by the user using a html drop list. The variable is named $group. Code:
View Replies !
Comparison Optimization
On to my problem. I'm running a query on a table with over 100,000 rows in it. Basically, my query is performing a "similar" check on books that people have logged in their "book list". It looks through the table and checks to see who has the most number of books similar to you. Here's the query: select count(a.mem_id) as numMatches,m.username,m.mem_id as memberID from book_list a LEFT JOIN book_list_members m ON a.mem_id=m.mem_id where a.book_id in(224,164,30,43,47,1,6,52,90,45,120,270,...,2442) GROUP BY a.mem_id ORDER BY numMatches DESC LIMIT 50 In the in() comparison, this can be anywhere from 1 integer to 1000 integers, it all depends on how many books a user has in their list. I've indexed "book_id", but unfortunately if there are more than say, 100 elements in the IN() comparison, the query takes anywhere from 1-2 seconds to execute. That's a long time when I have hundreds of users hitting a website and running that query. Is there a better way to do this? Is there a way I can "store" this query result somewhere, and only have it updated every couple hours (cached in other words, for immediate access).
View Replies !
Float Comparison
I'm writing a simple database with one table and a few fields that lists some books. One of the fields is an Unsigned Float field that lists the book's reading level. Then I have a PHP/web page front end that users can search for books listed in the database. Basically this is the probelm I'm having: If a user asks for all the books between the reading levels 3.0 and 3.3 -- MySQL returns every record that has a reading level of 3 up to and including all the books with a reading level of 3.3. However, if a user types in the form they want all the books between 3.0 and 3.2 -- MySQL returns all the 3.0 and 3.1 books, but returns none of the 3.2 level books. The code is a simple select query that ends up being: SELECT * FROM MyTable WHERE ReadingLevel >= 3 AND ReadingLevel <= 3.x
View Replies !
Previous Row Comparison
I have a table in this format: Column A | Column B 1 | 2 1 | 3 1 | 4 2 | 5 ..... ..... What I want to do is something like this: If in Column A there is a change in the number i.e. from the above table there were many 1s which transitioned into a 2. I want to record this change. I want to extract these two rows into another table. How can I make this possible?
View Replies !
Date Comparison 2
I want to compare dates in sequential records (in mysql format YYYY-MM-DD) in a db. I have a loop to iterate through a set of records. In this loop, how can I refer to the previous record in the loop? As in: for ($i=0; $i <$num_results; i++) { $row = mysql_fetch_assoc($result); if ($row('thisDate') != the previous record's date) { do something } }
View Replies !
Sql Comparison Tools
We've got a bunch of fairly long and complex sql statements that we need to compare between our dev and prd environments. The dev environment has been changed by a bunch of patches, while prd has been changed by an incompetent BA. Does anyone know of any tools that can help do this? Standard text/file comparison tools haven't been any help, so we're down to eyeballing them, which is both no fun and prone to error...
View Replies !
Dates Comparison??
I'm trying to find people from my db based on a given min_date and max_date using: SELECT . . . WHERE birthdate BETWEEN '".$dd_min_age."' AND '".$dd_max_age."' but if a range is specified there are no results returned. If I choose though the same date for min and max then I get the corresponding results.
View Replies !
Multiple LIKE Comparison Using IN Operator
Is there anyway to compare a field with multiple string values using LIKE? Here's the statement I have now: SELECT * FROM list WHERE email LIKE CONVERT( _utf8 'hotmail@hotmail.com' USING latin1 ) OR email LIKE CONVERT( _utf8 'john@hotmail.com' USING latin1 )"; I know how to do it using the IN operator, SELECT * FROM list WHERE email IN ('hotmail@hotmail.com', 'john@hotmail.com') but I need to be able to use the LIKE operator with the CONVERT function. The reason I am asking is because I am trying to match 50+ different email addresses.
View Replies !
String Comparison Implementation
how string comparison is implemented in mysql. (Both in Physical and Application layer) How queries on string (for example '>' or like or strcmp) are implemented. Any idea where can I find the open source for string comparison/ ordering/ implementation? Does it use the sme structre as strcmp in c?
View Replies !
Database Comparison Tools
I am looking for an easy way to be able to compare two mysql databases for their difference in tables and fields. It would also be nice for an easy way to make changes to the databases and port over information easily.I did search on this and found a couple of products that I found difficult to work with. The two main ones i tried, one froze when it tried to "sync" the databases and the supposed log of the "sync" wasn't very forthcoming and the other product just plain didn't work.I was wondering if anyone out there had this similar need and what the products/scripts were that they used? I would really thin kthere has to a least be a script that compares databases, even if you can't make changes right then and there.
View Replies !
Date Comparison Trouble
Spent the last 30 mins looking for a solution and haven't found one so: The table looks like: ID aDate Days - days in advance 1 | 2008-03-10 12:00:00 | 7 Im trying to do the following: SELECT table. * FROM table WHERE aDate >= '2008-03-(03+Days) 11:30:00' Basically so i can feed a date and it uses that date plus whatever is in the days fields, simple i know!
View Replies !
String Comparison (substr)
I am trying to update some data between two tables but the only consistent data in both tables is the model number However the problem is that the supplier has altered some of the model number's last 6 digits which is in date format Example - in my database I have model numbers: ABC34030309 AGHDNNEJ040309 In the new table with the data I have ABC34040109 AGHDNNEJ040109 If I could somehow strip the last 6 digits off each I would have a perfect match. I have looked at substring and this appears to only allow me to start at the beginning of any string unless I know the length. as each product model varies in length I cannot use this. I looked at trim but this looks like you have to specify what to trim rather than a number of characters.
View Replies !
Comparison Of Data Types
I have a large map that is represented in a sql database with an incremental index and a value for each entry representing the type of area on the map.I'm looking to optimize the code that stores and retrieves this information.In an attempt to minimize the number of characters that are sent out from the db, I am storing the types as ascii characters that represent the number type and converting them to numbers once retrieved. In this way, I hope to speed it up by not having to send two-digit numbers.So, my question is whether or not this is a good approach. Would sending just simple numbers be just as fast or faster?Or, more basically, I need a comparison of mysql performance when handling the various types of data. it seems like such a comparison has to exist somewhere.
View Replies !
Equality Comparison Of Char
Is there any difference in speed between an equality comparison of char (or varchar) column values with a constant, and comparison of int column values with a constant? i.e. would this: select * from table where col1 = 'blah'; be much slower or faster than select * from table where col1 = 6; this? Obviously the difference is probably miniscule, but this is a query I am planning on running in a lot of loops so it could be important
View Replies !
String Comparison To A Given Prefix
I would like to search a column of strings for matches to a given prefix. Is there a way to use existing string comparison functions to do this? Specifically I'm looking for the number of leading characters identical to my (variable) search string: Example: looking for matches to prefix "Robb" SELECT names, some_functions(names, "Robb") AS m FROM etc, etc, etc... | names | m | | Roger | 2 | | Robert | 3 | | Roseanne | 2 | | Cary | 0 | | Randy | 1 | | Robby | 4 | | Allison | 0 | Return the highest value of m for which LEFT(names,m)=LEFT("Robb",m). I've been reading through the section on string comparison function, but can't find anything suitable. But perhaps there is some combination of funtions you could use?
View Replies !
Mysql String Comparison
Using pure mysql syntax is there a way to compare a varchar and check if it starts with a certain character, ie SELECT * FROM databases WHERE name starts_with '+'
View Replies !
Database Comparison Tool
I have 2 different servers on different machines. I would like to compare database from this server to each other. Example: I would like to compare database1 (on server 1) to database 1 (on server 2)
View Replies !
Comparison With Other Rows While Searching
I've a table which looks like below: pk_id,group_id, mysqllate_id, category 1, 1, 1, 'a' 2, 1, 3, 'b' 3, 1, 5, 'c' 4, 2, 1, 'a' 5, 2, 2, 'c' 6, 2, 4, 'd' 7, 3, 1, 'a' 8, 3, 2, 'b' 9, 3, 3, 'd' I want to perform a query like this on table: Get me all 'mysqllate_id' for a 'category'='a', which are common for all 'group_id' found in array (let's say this array has 3 elements 1,2,3). So basically I'm looking for 'Common' mysqllate_ids across group_ids where category is 'a'. So this query should return '1' as mysqllate_id, which is common among all 3 group_id (mentioned in array) and have category 'a'. What would be the best way to do this? It seems like some sort of recursion is required.
View Replies !
Time Comparison Issue
I have a field storing date/time information in the TIMESTAMP form (mm/dd/yyyy hh:mm:ss AM). Is there a way to compare the current date with this stored date? I am trying to select all of the rows where the saved date is before the current date. I used php to generate the current date and tried using < or <=, but they do not seem to be working.
View Replies !
Datetime Comparison Working
I'm having a simple sql to extract data from time to time (service application in Delphi) and am storing the last record's datetime (MySQL datatype) as an example: 20/08/2005 6:04:21 PM I want to get the newer data and so, next time i run, i'm comparing the last retrieved record's datetime with the SQL data and want to get only the records having a >= value of DTColumn. *** Laststoredvalue := '20/08/2005 6:04:21 PM'; (am storing as a string) My sql string is built at runtime where i use the following: SQL.Add('WHERE t1.DTColumn >= CAST(' + QuotedStr(Laststoredvalue) + ' AS DATETIME)'); *** When i execute the query and read the first record, It's still getting ALL the records right from the 1st one available that has DTColumn value of '10/12/1981'.
View Replies !
Comparison Of Database Performance
I'm trying to justify using MS Access for a simple database where some other folks in our company are pumped on SQL Server. They only know that someone else told them SQL Server is the answer to everything, when in fact it's extreme overkill here IMO. This database will have a few tables and is mainly used for storing information regarding CAD drawings. We're not talking about a ton of transactions here! Maybe 100 per day PEAK new records or edits. That's a big maybe, on average probably 10 per day or less. I have a similar system I started 3 years ago which has about 1500 records today. I know Access has a 2gig limit on data and to me this is the only limiting factor to be concerned about. When comparing Access to SQL Server (not even a comparison) it's really about performance right? There will never be tons of transactions and many concurrent users on this database.
View Replies !
Case Sensitive Comparison
How can I make a case sensitive comparision using mysql_query? For example: "SELECT * FROM table WHERE User_id = '$user' AND Password = '$password'"; This returns true even if I match "ABCD" with "abcd".
View Replies !
Date Comparison Problem
I have a website with a diary of events. I have just come across this problem: The diary is listed on the site month by month - you can click a month and events for that month will show up. Events have a from date and to date and can span across multiple months. This has all been working fine until I added an event running from december 06 to january 07 - it no longer showed up. Here is how my query looked: (using december 2006 as the selected page as an example) SELECT * FROM diary WHERE (MONTH(from_date) <= MONTH(��-12-01') AND MONTH(to_date) >= MONTH(��-12-01') AND YEAR(from_date) = YEAR(��-12-01')) OR (MONTH(from_date) <= MONTH(��-12-01') AND MONTH(to_date) >= MONTH(��-12-01') AND YEAR(to_date) = YEAR(��-12-01')) ORDER BY from_date, to_date Now looking back it is obvious that this will not work as it is looking for a match in the years. After some research and trial and error I have come up with the following query: SELECT * FROM diary WHERE (UNIX_TIMESTAMP(��-12-01') BETWEEN UNIX_TIMESTAMP(from_date) AND UNIX_TIMESTAMP(to_date)) ORDER BY from_date, to_date However this is still not working. This is really bugging me and any help will be greatly appreciated. Thanks!
View Replies !
How To Write Like Comparison In Procedures?
this sample procedure takes in an username, searches for number of entries matching the pattern of the username. delimiter $$ create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int ) deterministic begin if length(m_username) = 0 then set m_username = 'jason' #set temporary name if empty end if set m_username_matched = 0; #finds number of matches for the given username select count(*) into m_username_matched #store into the variable to be returned from sampleDB.sampleTable where userName like '%m_username%' #this is the statement that does not work# end$$
View Replies !
Text With Tab To MySQL - Db Comparison
sorry... i did post first in databases forum... do not know how to delete my thread... so here my questions: i have a text file with TAB separation. in this file, i have some products with lets say: RefNr. / Name / Option / Amount when i open it i see: SOURCE: RefNr.NameOptionAmount 001Product 1Black2 001Product 1Black1 001Product 1Black0 001Product 1Green1 001Product 1Green4 001Product 1Green1 002Product 2Black3 002Product 2Black1 002Product 2Black1 002Product 2Green2 002Product 2Green2 002Product 2Green0 etc etc do you nkow a way to get this instead, after running a script for instance (my question is āhow does the script should look likeā!): RESULT: RefNr.NameOptionAmount 001Product 1Black3 001Product 1Green6 002Product 2Black5 002Product 2Green4 (i could save this text file first to EXCEL if it helps.) once iāve done it, iāll have in a second step to ācompareā this file to a mySQL Database. but in the selected DB table, i wonāt have exactly the same structure. i mean in my text file iāll have for instance not only RefNr. / Name / Option / Amount but RefNr. / Name / Option / Amount / Description / DeliverDate / ImgName and in my table in the DB iāll have: RefNr. / Name / Option / Amount / DeliverDate / Info i will need to compare the amounts between āRESULTā and my mySQL table, to get the amount which changed updated in my DB table. any idea welcome... even if the process is not automated and if i need to do everything manually in phpMyAdmin for instance (i do not even know how to do it in phpMyAdmin!).
View Replies !
TIMESTAMP Operations & Comparison
I'm trying to write a query that will remove all entries older than 30 days, using MySQL 5. The date is stored as TIMESTAMP. I'm stumped on just how to express "30 days earlier than now". I'm familar with the NOW() function but don't know how to add/subtract dates. The query is otherwise very simple, something like DELETE FROM posts WHERE datecreated < [????]
View Replies !
Fake Date Comparison
Code: select * from tareas where STR_TO_DATE( fecha, '%d/%m/%Y' ) = STR_TO_DATE( 19/11/2007, '%d/%m/%Y' )' order by STR_TO_DATE( fecha, '%d/%m/%Y' ) DESC Yes, an operator wrong, but why ?
View Replies !
|
TRACKER
