Rows Based On First Letter
I'm trying to figure out the fastest/best way to return rows based on the first letter of the title column in my table. For example, I want to get all the article titles that start with the letter "a".
Is there any difference between the two select statements? Any reason to use one over the other? Memory? Speed?
SELECT title FROM table WHERE LEFT(title, 1) ='a';
or
SELECT title FROM table WHERE title like 'a%';
View Complete Forum Thread with Replies
Related Forum Messages:
Items Based On The First Letter
we have products database with CD's, and would like to create an alphabetical list of artists, then display the CD's that the artist has underneath them. The database has the products in them, with the artists name being called 'prod_artist_name'. the query i tried, though I knew it wouldn't work is: SELECT * FROM products WHERE prod_artist_name LIKE '%n%' Now, this displays all the artists with the letter 'n' in their name, obviously. just wondering if there was a way to display the artists with 'n' as the first letter of their name.
View Replies !
Select The First Letter Of Each Rows In A Db
I want to be able to select only the first letter of each row in my table. I want to select only DISTINCT row so i would have as a result, an array of letters. For example, if i have 25 rows starting with the letter 'A', well 'A' would come out only once.
View Replies !
Filtering The Rows Based On Its Timestamp
I'm trying to write a backup script that takes the last modified rows of a table in x days. I can do it if I have a timestamp column for every table in the database, but my aim is to write a script for general use as anyone can use it with his/her database without the need to add that column. This will be possible if there a property gives the last modified timestamp of a row. does anybody has any way to reach that target?
View Replies !
Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The thing is, in my system, I have a set of users who have a 'quota' limiting their usage. They can create n number of tables and put m number of rows in it, but at the end, their 'usage' can not exceed, lets say, x MB. My problem is, how to I evaluate the space being used, given the fact that I can pull all data for a user by including a user ID in the where clause. In other words, I need to find size of the result set containing all user's rows. I am using mysql jdbc driver and not sure if there is any API that deals with the physical size of the resultset. resultset.getFetchSize() talks in terms of rows v/s the actual space they take.
View Replies !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag: resource - 1:M - tagged_resource - M:1 - tag So a resource can have many tags and a tag can belong to many resources. The data would look like this: RESOURCE TABLE id: 1 name: resource1 TAG TABLE id: 1 tag: new id: 2 tag: tested TAGGED_RESOURCE resourceID: 1 tagID: 1 resourceID: 1 tagID: 2 How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE? I have tried using 'IN' but it acts like an OR operator rather than AND.
View Replies !
Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem: Simplified tables: ARCADES ======= ID, name GAMES ====== ID, name ARCADES_GAMES ============= ID, arcade_iD Games_ID Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those. I can do soemthing like... SELECT a.name FROM arcades a WHERE EXISTS(SELECT 1 FROM arcades_games WHERE arcade_id=a.id AND game_id IN (11,14)) But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14. It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?
View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
View Replies !
Selec Using First Letter
i have this table filled with names. Does anybody know how I can write a select statement which selects all names starting with a certain letter in the alphabet or a strane sign like [0...9 )(*&^%$#@!_+{}][|?/<>,.] ??? Code:
View Replies !
First Letter Of Uppercase
I have a table with a column that contains names of countries. All the letters of the countries are uppercase (ex. UNITED STATES). I need the letters to be all lowercase except for the first letter of every word (ex. United States). I came across the "LCASE" function. But, I assume that will make all the letters lowercase (ex. united states). Is there a way to make the first letter of every word of the countries uppercase and the rest of the letters lowercase? The countries are 1, 2, and 3 words long. I would do it by hand, but there are over 50,000 rows. I'm not sure if what I'm asking is possible, but I'm sure someone here does.
View Replies !
Search First Letter
I want to be able to extract all words in my table that start with 'A', 'B', and so. But I'm not getting any reuslts when I run the below SQL: SELECT words FROM `my_words` WHERE words = '%{H}%';
View Replies !
Searching By First Letter
I need a query to search for entries in my table where the first letter of last_name and first_name is the selected value. So, when I click on the letter 'a', it would return all entries where the last_name and first_name begin with the letter a.
View Replies !
First Letter Of An Entry
Is there a query that allows me to select an item that starts with a specific letter, or a range of letters? for example, i have: apple banana orange in my table. Can i issue a query that selects anything that has the first letter in the range a-d?
View Replies !
Sort By First Letter
I would like to be able to sort by the first letter of a field. I have a problem where I have business's in a list and would like it to be alphabetically sorted.However, the listings that are priority (paid listings are '1', normal are '0') listings, I would like these at the top of each character section eg, A, B, C etc..
View Replies !
Capitalize First Letter
I am running a query which places the results in a drop down box. it wirks but i can't get the results to capitalize the first letter when producing the page. i've used css to achive this before but can't get it to work with results in the drop down box.
View Replies !
First Letter Of A Field
How can I select every row from a table where the first letter of the value of column 'w_name' is equal to A? mysql SELECT * FROM weapons WHERE the_first_letter_of_field(w_name) = 'A' How would I do this with actual, legal MySQL syntax? Thanks!
View Replies !
WHERE :: Specific Letter Of The Alphabet
How would I formulate a SQL statement where I want the first letter of a field to be equal to a specific letter of the alphabet? IE: I have the field lastName and I want to select all the records that begin with the letter 'a'.
View Replies !
How To Filter The First Letter Of A Field?
I want to filter a field according to its first character in a query, such as: select field1, field2 from table1 where "condition"; the condition is like: the first letter of field1 is not a number. How to write the condition?
View Replies !
Capitalizing The First Letter Of Every Word
i have something like this in my data: "BACHELOR OF SCIENCE IN INFORMATION TECHNOLOGY" "CERTIFICATE IN PROGRAMMING" now i already managed to make the first letter capital of the FIRST WORD only and make the result to lowercase by using this MySQL Code: UCASE(substr(DESCRIPTION_CODE, 1,1)), LCASE(substr(DESCRIPTION_CODE, 2))) and the result is like this "Bachelor of sciend in information technology"
View Replies !
Search For Entries Starting With A Non-letter?
We're trying to make a search form, and are wanting to create links for all the letters a person can click on and will bring up all entries that start with that letter. That part is easy: .... WHERE field LIKE '$letter%' But we'd like to also have one a person can click on for numbers and punctuation. Is there a built-in mySQL querey component where you can search for entries in a column that start with a non-letter?
View Replies !
1st Letter As UPPER Rest As LOWER
How can I update the first letter in each word in a column as an UPPERCASE and the rest of the letters as LOWERCASE? ie: john doe = John Doe UPDATE contacts SET name =?? FYI my Table is 'contacts' the column is 'name'
View Replies !
Search String Starting With A Certain Letter
I want to provide my visitors the simple task of clicking a letter and finding ALL corresponding names beginning with that letter. For example, I want to put a table with the letter A-Z (A-B-C-D-E, etc.), so, when someone clicks on the letter A, the php script will search the database and pull out every name that begins with the letter A. Click on the letter B, show every word that begins with the letter B, and so forth.
View Replies !
Query For String Begninng With The Letter 'A'
I am wanting to create a query which finds all records which have a field (in this case the title field of the field) that begin with the letter 'A'. I have been using a fulltext search, but that finds all fields with 'a' in the title, not just the ones beginning with 'A'
View Replies !
Need To Create Username From First Letter And Surname.
I have a signup form connected to a mysql database. The database collects the info from the form with a time and date field. i want to create a username out of the first letter of the persons name and thier surname. eg. Mr John Smith = jsmith So how can i make that automatically go into the mysql without the person actually typing this in themselves. Code:
View Replies !
Limit Mysql Output By Letter
Hi I am building a drinks database/search site. At present I have 26 tables 1 table for each letter like drinksa, drinkb, drinkc..ect which represent a, b, c...ect I have realised this methed it not good practice as I can't search all 26 tables in one go. What I would like to do now is put all the drink a to z in one table called drinks. I would then be able to search that 1 table, this is fine I can do that without a problem. But I am haing some difficulty getting my head around the front end what I mean is on the front end of the site on the drinks page there are links to each section like a, b ,c, d, e,......x, y, z. But if all the data is in one table then all the drinks a to z would be output I know I can limit how many to output but I was wondering is it possible to limit by starting letter For example when a user clicks "A" then all the drink starting with "A" are output but not any other drinks starting with a diffrent letter. This would be done by usin an sql statment I belive by using limit but am not sure if its possble to limit by letter. I have been looking at a lot of foums and so far I have not come across anything that may be of help so I though I'd ask If anyone would point me in the right direction or could expmain if this is possible or a better way of doing this I would be very greatful
View Replies !
Full Text Search :: 3 Letter Words
I have articles on my site that I am using fulltext search to search for, it is working fine, but there are a lot of 3 letter words I want to be able to search for (like acronyms), but the config file must have the min length at 5. I do not have access to the config file, so I can't change it. Any suggestions on how I could still search for 3 letter words? I was thinking about padding all 3 letter words with like characters, like xz or something, then when the user enters a 3 letter word i'd append the characters to that too, but then remove the xz from search results. Is there a better way to do this?
View Replies !
Appending A Letter To A Word (making It Plural)
I am creating a simple scheduling app, and in one part of the code, I would like to add an "s" to a word if an event is longer than "1 day", or "1 hour", etc. This would be in preparation to output the results in a drop-down list. In my db table I have different fields for those two variables (length of event, and timeframe of event (day, week, etc). What I would like to do is be able to check to see of the value of the event length is = 1 or not. If not, then I want to add an "s" to the timeframe (for example turn 3 "day" into 3 "days" (basically making it plural).
View Replies !
SQL Statement - Changing Case Of A Letter Automatically
I am running MySQL on Windows and I have a simple database setup with a list of items (contained in the same column), to keep it simple - let's say it is a list of colors. red white blue I want to be able to run a sql statement to change only the first letter in each word to uppercase so it will read: Red White Blue I think there is a command (but I am not familiar with it) that is suppose to change the entire case of the database table columns for example with : UPDATE Products SET ProductName = UPPER(ProductName) * A WHERE clause could also be used if only certain rows needed to be changed. But I only want to change the first letter as mentioned - does anyone know how to go about this ?
View Replies !
Return List Of Artist Starting With Apropriate Letter
I have table with artist. Structure is quite simple. It has two columns, ArtistID and Name. Now, I have a page where users can click on a letter like: A B C D E F... etc and once clicked, it will return all artist who's name start with clicked letter. I use LIKE to query db. SELECT a.ArtistID, a.Name FROM mns_artist as a WHERE a.Name LIKE 'C%' Now, the problem begins on letter T. Bunch of artist names start with The and I don't want to include them on page under T, but under apropriate letter. For example, I don't want The Rolling Stones to appear under T, but under R.
View Replies !
Next And Previous Buttons MySQL Rows Displaying 3 Rows Per Page
I have got a table "members" and the DB name is "jutland". I have got columns - id, name, surname & email. I want to display 100 records with 5 rows per page using PHP going back and forth with previous(previous 5 rows) and next (next 5 rows) links. I have the knowledge of the LIMIT x, y condition but still stuck on passing required parameters.
View Replies !
Any Way To Optimize Join To Find Rows Without Conditional Foreign Rows?
I've got a table of keywords that I regularly refresh against a remote search API, and I have another table that gets a row each each time I refresh one of the keywords. I use this table to block multiple processes from stepping on each other and refreshing the same keyword, as well as stat collection. So when I spin up my program, it queries for all the keywords that don't have a request currently in process, and don't have a successful one within the last 15 mins, or whatever the interval is. All was working fine for awhile, but now the keywords_requests table has almost 2 million rows in it and things are bogging down badly. I've got indexes on almost every column in the keywords_requests table, but to no avail. I'm logging slow queries and this one is taking forever, as you can see. What can I do? # Query_time: 20 Lock_time: 0 Rows_sent: 568 Rows_examined: 1826718 .....
View Replies !
Select Based On FK
I have two questions regarding the MySQL v4.1.16 scenario below: 1. What should the FK on update and on delete parameters be (i.e., default, cascade, restrict, no action, etc.)? 2. How can I insert the correct id’s as implied below? I have tried just about everything (including “Insert …select”, “LAST_UPDATE_ID();”, etc.) and whenever I think I am starting to get close I either get more errors or ‘0 records added’ with no error message; I would greatlyappreciate someone who could instruct me on how to accomplish this task.
View Replies !
Using A Web Based Database
My web site host allows me to create MySQL databases on its database server. I've created a database as a trial. Now I would want users of my website to supply data to the database and for me to be able to extract it for analysis.
View Replies !
Web-based Chat
I'm debating between 2 different table setups for a web-based chat system (gchat clone). The first is just one table labeled 'chat' which has the columns 'user_1' 'user_2' 'time' 'message'. The second would be dynamically creating/deleting tables with the user names as table names like 'user_1/user_2'. I don't know a whole lot about DB table organization but I was thinking the actual 'select' statement would be faster with seperate tables. Any ideas?
View Replies !
UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.
View Replies !
Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....
View Replies !
Syntax Based On MS Access SQL
Our shop is using an old version of MySQL (3.23).In testing our database concept we put together a test database in Access and the sql statement in MS Access looks like SELECT DISTINCT FROM table1.col1, table2,col1 FROM table1, table2 WHERE (((table1,col1) like "*" & table2!col2 & "*")); this gives us output from both tables(tables1 and tables2) while looping over the values of table2.col2.How does MySQL do this type of function?
View Replies !
SELECT Based On 2 Tables
I'm writing some custom stuff to pull from a WordPress install I have one one of my servers. For one function I want to SELECT all the rows from one table based on criteria from that table and another table (they are related by the row ID of the first table). Here is the SQL I have now.... sql Code: Original - sql Code SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC It seems to be returning too many results. It's just returning all the rows from wp_posts that match the post_type and post_status criteria and ignoring the term_taxonomy_id (from wp_term_relationships) requirement.
View Replies !
Update One Col From Another Col Based On Primary Key
I have a table which stores referral URLs in column 'ref' and primary key of the table is 'clid' I have added another column which will store the name of the search engine referral by the name 'SearchEngine'. Now what I want to do is to avoid PHP processing and write a mysql query as follow, Code: UPDATE clicks SET SearchEngine=( SELECT ( CASE WHEN (LOCATE('.google.',ref)!=0) THEN 'Google' WHEN (LOCATE('.yahoo.',ref)!=0) THEN 'Yahoo' WHEN (LOCATE('.msn.',ref)!=0) THEN 'Microsoft' WHEN (LOCATE('.microsoft.',ref)!=0) THEN 'Microsoft') WHEN (LOCATE('.live.',ref)!=0) THEN 'Microsoft') ELSE 'Others' END ), @tmpo:=clid FROM clicks WHERE ref!='' ) WHERE (clid=@tmpo); but it gives me following error, Code: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHEN (LOCATE('.live.',ref)!=0) THEN 'Microsoft') ELSE 'Others' END),clid:=@tm' at line 1 Basically i want to parse referral url, decide the search engine name and then store its name in the new column based on primary key so that correct values are stored in correct record.
View Replies !
Date Based Calculations
I'm looking for a way to create a query that generates a record for every day of the last year and using that date information for each day generates a unique database query (sub query?)for each day of the year. I could probably do this in php but I'd like to do it for performance reasons from mysql (or at least most of it). Does any one know of any good online tutorial/s that may help me build this sort of project? Or have some basic scripts that may be a good starting point?
View Replies !
Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement: UPDATE ApplicationTbl INNER JOIN AcademicTbl ON ApplicationTbl.CampusID = AcademicTbl.CampusID AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period SET ApplicationTbl.App_Status = 'Qualified' WHERE AcademicTbl.Sem_OnCampus >= '1' AND AcademicTbl.GPA >= '2.4' AND AcademicTbl.Judicial_Sanction IS NULL It keeps saying its wrong. Even though I know its not
View Replies !
Update One Column Based On Another
I have two tables. One set up like this: id item_id group_id userid outcome settled The second is userid yes no I would like to look at the first table and find all rows that have a specific group_id, then update the userid yes/no based on the outcome on that row. Outcome Y then add 1 to the yes column of that userid, outcome N then add 1 to the no column of the userid. Is this possible in one SQL statement?
View Replies !
|
TRACKER
