One Field With Multiple Attached Rows, Only Show The Field Once?
I have a table called "Customers", with a field called "Customer name" and a "Customer ID". I have another table called "Projects", with a field called "Project name" and a field called "Customer ID" which references to "Customers"
What i am trying to do is: Select a customer. Select the projects that are related to the customer by comparing the Customer ID field. Code:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Concatenating Multiple Rows To One Field?
I got a slight problem with a web application, its supposed to several entries for a certain ID, and display them as one string. Example: ID | Name ------------- 1 | Jack 1 | Peter 2 | John 2 | Mark 2 | Ellis Expected Result for ID 1 should be "Jack, Peter" for example, or "John, Mark, Ellis" for 2. Least problem would be clipping off a trainling or leading ",", but the main problem is a SELECT statement to get them all in one go. Else I'd have to select every Name per ID, and concatenate them in PHP or elsewhere. Given its around 100 IDs, I'd have to do like 100 querys to get the names, *in addition* to the Querys I need before and after. I want to avoid that, but I couldnt find a simple solution that works in MySQL 4.1 and above, aswell as it mustn't involve Stored Functions or something, because I got no access to the mysqld itself, to add the funcs as modules. Code:
Update One Field In All Rows
I have a mysql database which contains 24,000 rows. I added a field called "RECALL", which contain null values. What I'm trying to do is fill that field in all 24,000 rows with the value "N".
Alter One Field In A Row (300 Rows)
example changing id='1' -> id=' 5000' this must be done with over 300 ids Is there a script ot there that can do this? instead of me doing it manually.
Concat A Field In Up To 4 Rows?
A customer can have between 1 and 4 rows, is it possible to make a query that will merge field "A" of all rows and then discard all other rows leaving only 1 row left with the merged data in field "A".
Search Through Rows Until You Hit Field With Right Value
Basically, I'm trying to figure out how i can scroll through the rows in a mysql table until I hit a row which has a field containing a certain string. This is a ruff image of how my database looks (ignore the dots...it wouldn't let me leave white space): id | username | some_field | | some_other_field | 1..|...bill........|...............| |........................| 2..|...fred......|...a value..| |........a value.......| 3..|...ted.......|...............| |........................| 4..|...jeff.......|...............| |........a value......| 5..|...craig.....|...............| |........................| So, basically, i need to be able to start the query a Fred's 'some_field' then I need it to search on until it finds the next field with 'a value' in...in this case it would be Fred's 'some_other_field', but it could also be any other field in the table.
Count Same Field In Rows
i need to calculate the number of the same IP address that appear in the database, how can i do that? example of the database: IP Address Date and Time 127.0.0.1 5/23/2007 127.0.0.1 5/24/2007 192.168.1.2 5/25/2007 192.168.1.5 5/26/2007 127.0.0.1 5/27/2007
SELECTing Rows Where 1 Field Doesn't Repeat
Let's say I have a table something like this: id a b c 1 1 2 3 2 1 3 4 3 2 1 1 4 2 8 4 5 3 1 4 6 3 8 1 And then I want to select the rows, where "a" field doesn't repeat, so the 1st, 3rd and 5th. How do I do that?
Not Selecting Rows Where A Specified Field Is Empty
What I am trying to do in a query, is only select rows where a certain field is not empty. For example, I have a visitor logs table, with the following fields: id | date | pageVisited | referrer Now, I only want to select the rows where there is a referrer. Something like:
Multiple Values In One Field
Is it ok to store multiple values in one field? I have a site where users input Category Name and Category Index into a form. I want to be able to store both values in one field; for example: 0|News or 1|Sports The reason for this is long so I won't bore you, but I just want to know if I will run into problems doing this?
Multiple Field Primary Key
I've created a table using the MySQL Control Centre and set two fields to be a Joint primary key. At least that's what I was intending to do and what would have happened if it had been an Access database ( I'm well used to working in Access ) What I seem to have ended up with is a table with two sepatate field each marked as a primary key and each one preventing duplicate entries in the separate fields. I think that If I'd used the command line SQL to Create the table CREATE TABLE tblLinkPersonDept ( PersonRef MediumInt; DeptRef MediumInt PRIMARY KEY ( PersonRef, DeptRef ); Then I'd have got what I want. However, Is there a command line instruction to change the table I have to what I need ? Or does anyone know what I'm doing wrong in the Control Centre ?
Multiple Count From The Same Field
I have been having some trouble with the query below. select a.advisor_ao as 'AO', case c.existing_client when 'feps' then count(c.existing_client) else 0 end as 'total' from advisor_tb a left join company_tb b on a.advisor_id = b.advisor_id left join comp_seminar_ca_tb c on b.company_id = c.company_id group by a.advisor_ao I am not looking for an answer just wanted to know if my query is incorrect because if it is not thean it's my table structure
Multiple ID's In One Field
I have a program using PHP and MySQL, using categories. At the moment I simply have an 'cid' field with category ID in, which is obviously easy to use ("...WHERE cid = $id" etc). But I now need to use multiple categories. The way I was looking to go would be to store the ID's like so... "1|2|4|8|45" etc, in the 'cid' column. I dont know how to extract an ID using MySQL. I could do it through PHP but it would be longwinded. Is there a built in MySQL function that is able to "pick up" the ID. I don't think "LIKE" would be suitable.. no?
Multiple Entry Field
I have a table for members and a table for trades and often a member will have many trades listed on thier record. There are only 2 columns in the trade table 'table_ID' (PK) and 'tradelist' (plumber, electrician, etc). I have created an admin page in D/Weaver using PHP code, which you can add to the trade list as a new trade pops up and I have an add & edit member page with checkboxes that lists all of the trades and you can just check the ones that apply to that member. Problem 1 - It only applies the first checked box to the database.
Multiple Field Index
In phpMyAdmin I can create an index that is made of two fields. If I create a filed that starts with a user id and the second part is an auto incrementing value how do I use that? Can I use it with a: Quote: SELEST * from `table` where my_index like 'epanagio%' Is this the reason combined indexes are created.
Select Rows Where A Field Value Exists In Another Table
I have a table with a column of email addresses I have a second table with a column of email addresses I want to select all the rows in the first table whose email value exists in the second table Can't get a query to do this without an error,
How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value. I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.
Multiple Values In Single Field
If there is an existing field with values in it (1,2,5,6) comma deliminated, what is the process for querying and pulling these out? Is there something that would be combined with IN that would convert the values somehow?Thank you for any thoughts, and please rest assured that I am aware of the ill nature of multiple values in a single field.
Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I need to combine multiple records which share two like fields. (If that's not clear, the real-world explanation is below.) +----------+--------------+----+----+----+----+----+ | ID | ADDRESS | P1 | P2 | P3 | P4 | P5 | +----------+--------------+----+----+----+----+----+ | COMP1 | 123 Main St | 1 | | | | | | COMP1 | 123 Main St | | 1 | | | | | COMP1 | 123 Main St | | | 1 | | | | COMP2 | 45 Oak Pl | 1 | | | | | | COMP2 | 45 Oak Pl | | | 1 | | | | COMP2 | 45 Oak Pl | | | | 1 | | | COMP2 | 45 Oak Pl | | | | | 1 | +----------+--------------+----+----+----+----+----+ Must be distilled to : +----------+--------------+----+----+----+----+----+ | ID | ADDRESS | P1 | P2 | P3 | P4 | P5 | +----------+--------------+----+----+----+----+----+ | COMP1 | 123 Main St | 1 | 1 | 1 | | | | COMP2 | 45 Oak Pl | 1 | | 1 | 1 | 1 | +----------+--------------+----+----+----+----+----+ So essentially I want to combine all of the 'Px' fields for each ID into one record. The real model : The data represents companies (identified by ID) and the product lines they stock (P1 - P5). Right now, there's a separate rec for each product line. I need to combine those seperate recs into one. ____________________ I suppose I could import the raw data into separate tables, each with one 'P' field -- and then combine them into another, but I'm not sure about that approach either. ____________________ I already tried to make Excel combine before exporting, but had no luck.
Select Multiple Instances Of A Related Field
Is it possible to write an SQL statement to export mutliple instances of a related field in a single record? For example, ContactsTable is related to ContactHistoryTable. I want to select the contact record and export 3 columns from the ContactHistory records containing the most recent ContactHistory.Type = Phone, most recent ContactHistory.Type = Appointemnt and ContactHistory.Type = Action
Combining Multiple Records Into One Based On A Key Field
I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I need to combine multiple records which share two like fields. (If that's not clear, the real-world explanation is below.) +----------+--------------+----+----+----+----+----+ | ID | ADDRESS | P1 | P2 | P3 | P4 | P5 | +----------+--------------+----+----+----+----+----+ | COMP1 | 123 Main St | 1 | | | | | | COMP1 | 123 Main St | | 1 | | | | | COMP1 | 123 Main St | | | 1 | | | | COMP2 | 45 Oak Pl | 1 | | | | | | COMP2 | 45 Oak Pl | | | 1 | | | | COMP2 | 45 Oak Pl | | | | 1 | | | COMP2 | 45 Oak Pl | | | | | 1 | +----------+--------------+----+----+----+----+----+ Must be distilled to : +----------+--------------+----+----+----+----+----+ | ID | ADDRESS | P1 | P2 | P3 | P4 | P5 | +----------+--------------+----+----+----+----+----+ | COMP1 | 123 Main St | 1 | 1 | 1 | | | | COMP2 | 45 Oak Pl | 1 | | 1 | 1 | 1 | +----------+--------------+----+----+----+----+----+ So essentially I want to combine all of the 'Px' fields for each ID into one record. The real model : The data represents companies (identified by ID) and the product lines they stock (P1 - P5). Right now, there's a separate rec for each product line. I need to combine those seperate recs into one. ____________________ I suppose I could import the raw data into separate tables, each with one 'P' field -- and then combine them into another, but I'm not sure about that approach either. ____________________ I already tried to make Excel combine before exporting, but had no luck.
Creating A Single Field From Multiple Row Results
I know how to combine multiple columns to get a single result field but I'm not sure how to combine rows from the same column into a single result field. Basically what I want to do is the following. If my table looks like: risk | reference -----|------ a | 1 a | 2 a | 3 I want a query that will give me: risk | references -----|------------ a | 1, 2, 3
Updating Multiple Username In One User Field
how to update one field with 5 name of users and looping it till the end of records.if i do this UPDATE CALL_BARR_LIST_READY SET [USER]='' WHERE [USER] ='KUREIL' it will filled all the records with KUREIL as a USER. but now i have 5 users and > 1000 records to update where each user get the same total no of records.
Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id. How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously? My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense). Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.
Limit Number Of Rows Or Characters Returned In A Text Field
I understand you can limit the rows of a recordset, but can you somehow tell a query to limit the rows (or characters) returned within just a text field? I have a page that returns abstracts of many articles and I want to truncate the description for each to just 200 characters. Is there a way to accomplish this with MySQL?
How To Convert A Varchar Field Into Proper Mysql Date Field?
I have a database in which the date is stored in varchar field in a following format: d-m-Y (06-08-2007), now the problem is that I want to change that field into mySQL date field as well as convert my older dates into MySQL date format i-e Y-m-d (2007-08-06).. There are about 300 old entries..is there a way I can do that automatically without manually re-entering the dates again?
Extracting Field Data And Putting Each Field Into A Shell Variable?
In a MySQL query on the database (one table with 15 variable length fields, I want to put each field into a Bash variable so that I can handle each field as an entity. The query I have is something like this: mysql -e "use $database; select field1, field2, field3..., from Table1 where fieldN like '%something%';" can I / How do I do something like: mysql -e "use $database; select field1,..., var1=field1, var2=field2,....;" do stuff with var1, var2..., done
Inserting Into Multiple Tables Using Auto Incremented Field
I'm inserting a record into MySQL 4.0 using Visual Basic ADO. When using the AddNew and Update method I am unable to retrieve the value of a Auto incrment field (Yes I know I can MoveLast but this icreases the update time by a factor of 10 and when your talking about 200,000 inserts its way to long). Is there a way to insert the record into multiple tables in one statement where you can use the value of the auto incremented field as the key in the second table? So the statement inserts the record into a table retrieves the new auto_incremented key and then insterts information into the second table using the incremented key as the primary key?
Create A Field By Deleting A Part Of The String From Another Field!
I have a table for "Addresses", which has a column titled "Address" and the records in this column are a combination of house#, street name and street suffix, which typically looks like "100 Main Street". I am looking for a query result wherein a new column would be created which would have only the street name and the suffix and would eliminate the house #s, hence the result would look like "Main Street".
Enforcing Unique Field Values Accross Multiple Tables
I have 2 tables (t1,t2) with unique keys defined in each. In addition to enforcing unique key values in each table, I would also like to enforce unique key values accross both tables (ex. If key value 'XXX' appears in t1 I don't want to allow entry of key value 'XXX' in t2 and vice versa).
Copy Field Over, Then Update The Original Field
I want to change a price of a product, but back up the old price. I was trying to do this through two queries, an INSERT and an UPDATE, but it's not working. INSERT the row that will store the backup: INSERT INTO s01_MUS_SalesXProducts ( s01_MUS_SalesXProducts.sale_id , s01_MUS_SalesXProducts.prod_id ) VALUES ( ".(int)$sale_id." , ".(int)$prod_id." ) Then I would copy the prices over and update the original with one UPDATE query: UPDATE s01_Products LEFT JOIN s01_MUS_SalesXProducts ON s01_Products.id = s01_MUS_SalesXProducts.prod_id SET s01_MUS_SalesXProducts.old_price = s01_Products.price , s01_Products.price = ".(float)$sale_price." WHERE s01_Products.id = ".(int)$prod_id." However it is copying the new price to the backup field. Is there a way to do this in two queries? I know if I select the product price before inserting I can do it in 3. Truely I would like to do this in 1, but I don't believe this is possible due to the nature of the queries.
Creating Field In Table To Sum Field Values
i was wondering if there was a way in mysql to have one record that will sum up all each field value(one record with each field having sum totals)? i have a table with over 60 statistical fields that i want to get the sum for.
Updating Field With Contents Of Temporary Field
I have a table, that when new information is entered for it the new info is held in a temporary table that has exactly the same fields (the only difference is the auto increment pid in the original table is not auto increment in the temporary one - it holds the pid from the original). I need to be able to update the original with the contents of the temporary field, all fields are updated. Is there an easy way to do this, or will I need to select from the temporary and then run the update setting all the fields explicitly. I have tried something like [code] Update villas SELECT * FROM updatevilla WHERE pid='$villapid' [/code ] in php but it doesn't work. I think I see what some of the problem is but am not sure how to fully write the query.
Orderby A Text Field As It Were An Integer Field
Is it possible to construct a query so that the orderby 'field' returns the rows as though the 'field' were an integer field, even though it is a text field? I do not want e.g. 10 to be before 2. The field in question is called 'lagerantal' and is of type 'text'
Import NULL Or Empty Field Field Value
If you are importing data from an external data source and you have the choice of populating empty fields with NULL, what are the pros and cons of doing so?
Copy Partial Field Value To New Field
I have a table with state and county in one field. The first 2 characters in all the records indicates the state. Is there a way to copy just these first two characters into a new field in mysql?
Given Field Name, Find Table That Has Tha Field Name?
Is there a quick way to identify tables that have a specific column in them? For example, if I have a column name 'RuleName', how can I need to find all the tables in a database that have a column with that name?
Date Field, Time Field
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row. I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) and to be able to sort the query results on the resulting field.
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.
How To Remove Multiple Spaces From A "data" Field?
I need to remove multiple spaces from random pieces of text in a data field. Can someone tell me how to do this? I've tried using this but it doesn't do anything: update posts set body = replace(body,' href',' href'); *Note the double spaces before the first href and the single space before the second href.
Using A Field Alias In Another Field
I'm migrating a DB from Access to MySQL and I having problems with queries similar to this: SELECT 1 AS x, x+1 as y; I get a "ERROR 1054 (42S22): Unknown column 'x' in 'field list'" error. Is there a way to do this without calculating 'x' twice?
I Want Field Data Without Field Name, How?
I am retrieving data from mysql under linux. something like mysql -e "select id from table1", I get output id 1 2 but I dont want the field name "id" but only the data. How can I do it?
Field Contains Form Field
I have a form putting variable search_var Select * from training.itdtms_content where content_Body like #search_var# the # on each side are for the Coldfusion variable. I want to be able to find records that have a search term inside them, can anyone help?
Part Of A Field Into Another Field
I have a table with an email field that contains values of the form uid@domain. I need to populate a new field called uid with just the uid part of the email address. Is there any way of using select to just retrieve the part before the @ and insert it into the uid field?
Show Rows With No Matching Fields
here is my query which probably needs some more advanced join statements: select * from orders o, customers c, orders_styles os, styles s where o.customers_id = c.customers_id and os.orders_id = o.orders_id and os.styles_id = s.styles_id group by o.orders_id the problem is that sometimes, because of user input, there will be no matching fields in the tables orders_styles or styles (no rows with matching orders_id exists in table orders_styles to table orders). I still want to show these rows however. Right now, as you can see it only displays rows that have matching rows in orders_styles and styles.
Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57 # User@Host: counter[counter] @ localhost [] # Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647 SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7; mysql> select count(*) from _1_log; +----------+ | count(*) | +----------+ | 111824 | +----------+ 1 row in set (0.00 sec)
Show Rows With Result 0 For Aggregate Functions
have a query like this: SELECT C.idc, C.name, count(CM.idm) from C, CM where C.IDC=CM.IDC and CM.idci is null and C.type='class' GROUP BY C.IDC The result table only contains the elements of table C where the count() is >0. How can I obtain a result table that contains all elements of table C with their count (either 0 or >0) ?
|
TRACKER
