MYSQL :: Selecting Data In Rows

Selecting Rows Not In Array?
I'm trying to pull a single random field/row out of a mysql database, based on records that the user has not viewed already.

I can either store an array of IDs in a session var or a cookie, but then how do I get mysql to exclude those IDs when selecting a random number?

I'd like to 'SELECT ID FROM Table WHERE !in_array($array) ORDER BY RAND() LIMIT 1' but of course the "in_array" php code won't work in a mysql query.

Is there a way to do this in mysql only, rather than having to iterate through the array and build a query with possibly hundreds of elements in the array:

WHERE ID!=# AND ID!=# AND ID!=#,

Selecting Rows Having Repeated Value
Imagine we've got a MySQL table with the following structure:

id int
name varchar(255)

The table contains the following data (id, name):
1 name1
2 name2
3 name3
4 name1
5 name2
6 name9
7 name1

Please help me with the query to select only those rows, in which "name" value occurs in this table more than once.

So I would like to have the following result:
1 name1
2 name2
4 name1
5 name2
7 name1

Selecting Rows Not Within Another Table
I have 2 tables Stores and Store_Projects that im trying to pull all
stores not in certian projects but belong to a customer. Heres the
design of each

CREATE TABLE `Stores` (
`storeID` int(10) unsigned NOT NULL auto_increment,
`storeMetro` int(11) NOT NULL default '0',
`storeManager` int(10) unsigned NOT NULL default '0',
`storeState` int(10) unsigned NOT NULL default '0',
`storeAddress` varchar(100) default NULL,
`storeCity` varchar(50) default NULL,
`storeZipcode` varchar(20) default NULL,
`storePhone` varchar(20) default NULL,
`projectStatus` varchar(20) default NULL,
`storeSupervisorName` varchar(100) default NULL,
`storeSupervisorPhone` varchar(20) default NULL,
`storeSupervisorEmail` varchar(255) default NULL,
`storeOtherContactName` varchar(100) default NULL,
`storeOtherContactPhone` varchar(20) default NULL,
`storeOtherContactEmail` varchar(255) default NULL,
`storeNumber` varchar(50) default NULL,
PRIMARY KEY (`storeID`),
UNIQUE KEY `storeID` (`storeID`),
KEY `storeMetro` (`storeMetro`)
)

CREATE TABLE `Store_Projects` (
`projectID` int(6) unsigned zerofill NOT NULL default '000000',
`storeID` int(11) NOT NULL default '0',
`billRate` varchar(10) NOT NULL default '0.0',
`associatePayRate` varchar(10) NOT NULL default '',
`repsNeeded` smallint(6) default NULL,
`storeServiceDateDay` smallint(2) unsigned default NULL,
`storeServiceDateMonth` smallint(2) unsigned default NULL,
`storeServiceDateYear` int(4) default NULL,
`receivedPayment` enum('Y','N') NOT NULL default 'N',
`invoiceNumber` varchar(25) default NULL,
`invoiceDate` date default NULL,
PRIMARY KEY (`projectID`,`storeID`)
)

I had designed the query like this

SELECT st.*, s.stateInitials, m.metroName, sta.stateInitials AS
metroState
FROM Stores AS st
LEFT OUTER JOIN CustomerUsers AS cu
ON cu.userID = st.storeManager
INNER JOIN States AS s
ON st.storeState = s.stateID
INNER JOIN MetroAreas AS m
ON st.storeMetro = m.metroID
INNER JOIN States AS sta
ON m.stateID = sta.stateID
INNER JOIN Store_Projects AS sp
ON sp.storeID = st.storeID
WHERE st.customerID = 4
AND sp.projectID != 000002

But of course thats not going to work if the store is not in the
Store_Projects table which it wouldnt be if its not in a specific
project. So I need a way to pull all Stores with a specific customerID
but is NOT in the Store_Projects with a specific projectID.

Can anyone explain how this should be done within the query? I could
do it by creating an array and then processing out all the stores but
I would much rather let MySQL do the work for figuring this out.

Selecting Recursive Rows
computers(id_computer,description,id_location);
locations(id_location,description,father);

I have this two tables: computers and locations;
In locations I can insert a location and tell which one is his father, so I can have locations inside of locations.

------------------------------------------------
id_location, description, father
------------------------------------------------
1, Main Building, 0
2, HRs, 1
3, Director, 2
4, Warehouse, 0
5, Office, 4
6, Director, 5
-------------------------------------------

When I insert a new computer, I tell in which location is that computer

-------------------------------------------------------
id_computer, description, id_location
-------------------------------------------------------
1, Fujitsu-Siemens 4200, 2
2, Fujitsu-Siemens 5300, 3
3, Fujitsu-Siemens 4200, 5
4, Fujitsu-Siemens 5300, 6
------------------------------------------------

Now I would like to select all computers from main building for example.

I can not select * from computers where id_location=1 because that would not get the computers from HRs and Director. I would like to have all computers listed.

Only Selecting Specific Rows
Fields: studentID, reviewID, date

I want to get all rows in a certain date range where there are at least 3 rows for a given studentID. In other words...

Case 1: student has written 2 or fewer reviews during the date range. Don't retrieve these rows.

Case 2: student has written 3 or more reviews during the date range. Retrieve ALL of their reviews during that time. So if they wrote 5 reviews, the query will have 5 rows for their studentID.

Any ideas for this query? So far I got the date range to work using this. I'm struggling with the 3+ reviews part.
SELECT `studentID`, `reviewID`, `date` FROM `review` WHERE `date` BETWEEN '2007-10-21' AND '2007-11-09' ORDER by studentID, `date`

Any ideas?

Selecting From Multiple Rows??
i have made a voting script... every time someone votes it goes into a table called 'voting'... since people can vote on multiple things in the site there are multiple instances of each user in the 'voting' database...

if each entry has a 'userID' a 'ratingGiven' and a 'objectBeingVotedOn' field
how would i go about grabbing say the "ratingGiven" field from all of one particular user's entries .

Selecting Rows In MySQL
I have a big database with about 500.000 rows. Each row consists of five columns with various information, e.g.

a,1,1,1,1
a,2,2,2,2
a,3,3,3,3
b,1,1,1,1
c,1,1,1,1

I want to select all identical values from column 1 (e.g. the a in the above example).
Then I want to copy the data in a new table, which is going to have as below:
a, (1,1,1,1),(2,2,2,2), (3,3,3,3)...

Selecting All Rows From One Table That Is Not In The Other
What I want to do is this:

select table1.id from table1,table2 where table1.id != table2.id   ....  I wish it was possible to write like that... but it doesn't I guess I can figure out the reason but anyway how should I do it? PLEAse help... example:

table1    table2
id 1      id2
id 2
id 3

select table1.id from table1,table2 where <what to write here?>

result:
id 1
id 3

Selecting Unique Rows
I have a query that checks a list of users against a list of items and returns for example the following.

SELECT users.username, ratings.recording_uid
FROM users
INNER JOIN
ratings ON (users.uid = ratings.user_uid)
WHERE ((users.username = 'a') OR (users.username = 'b'))

which returns...

username recording_uid
a 1
b 1
a 2
b 2
b 3
a 4
b 4
b 5

How can I add to that query so that I know what user a has that user b doesn't and vice versa? eg. so that it returns

username recording_uid
a 3
b 5

Selecting A Certain Rows From A Database
if i wanted to select a certain range of rows from a database,is it possible?For example,i have a column in my database which is also a primary key and i wanted to select rows from where the primary key column is 21 - 40,would this sql statement be valid?

SELECT * FROM tbl1 WHERE id=21 TO id=40;

Selecting Distint Rows
I have a load of images in a table, and these images are split into 2 different sections within my site. Some images may appear in both sections, but most only in one or the other.
I want to be able to search the images table, and have the search results page show a list of thumbnails (this i have done already).
If an image appears in both sections of the site, I do not want to show 2 thumbnails of the same image (this is what currently happens), instead I want to show a single thumbnail, and beneath it show that it is present in both sections.
The common factor between these images that appear in both sections, is a field called image_code, which is unique to each actual image. If an image appears in both sections, it will have the same image_code.
If I use DISTINCT I will only get a single image, but I am not able to show that it appears in the other section.

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?

Selecting Rows From The Past 24 Hours
I have some data rows, and they contain date entries in a datetime formatted field - e.g.:

2006-07-05 01:30:03
2006-07-05 01:46:36
2006-07-05 01:20:41

I would like to select rows from the table for the past 24 hours

It would be something like:

SELECT fldID, fldName, fldDate
FROM
my_table
WHERE
.... fldDate is within the last 24 hours
But I'm not sure how to do it.

I've used the date_format function before, but that might not help.

Then I found the date_add function - so if I did this:

SELECT DATE_ADD(&#55614;&#57150;-01-02 23:59:59', INTERVAL -24 HOUR);
It would return:

1998-01-01 23:59:59

That looks good - but I'm not sure how I can build it into my statement to only return rows where the data_format field contains data less entered in the last 24 hours.

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:

SQL Selecting Rows, Group By Increment
Lets assume the follow database:

| ProductID | Cost |
+-----------+------+
| 1 | 100 |
| 2 | 10 |
| 3 | 250 |
| 4 | 900 |
| 5 | 50 |
| 6 | 60 |
| 7 | 340 |
| 8 | 100 |
+-----------+------+

Is there an SQL function to group those values by a certain increment? For example a predefined increment of 100 would return:

| COUNT | Cost |
+-----------+---------+
| 5 | 0-100 |
| 0 | 101-200 |
| 1 | 201-300 |
| 1 | 301-400 |
| 0 | 401-500 |
| 0 | 501-600 |
| 0 | 601-700 |
| 0 | 701-800 |
| 1 | 801-900 |
+-----------+---------+

Of course, I could loop through each increment with PHP and query each increment but that could get rather slow after a while.

Selecting Rows Where Foreign Key Is Null
I have this following query:

SELECT l.NAAM, l.VOORNAAM, l.ADRES, l.LAND, l.POSTCODE, l.GEMEENTE, r.titel
FROM westhoek.wtc_aansluitingen a, westhoek.wtc_leden l, westhoek.wtc_relatietitels r
WHERE l.ID = a.lid AND l.RELATIETITEL = r.id AND a.jaar=2003 AND l.RELATIE Is Null
ORDER BY l.NAAM, l.VOORNAAM

but I also want to get the rows where l.relatietitel is null,
so in Oracle SQL it would be ... and l.relatietitel = r.id (+)....

but the (+) doesn't seem to work in mysql...

Selecting Rows From One Table With A Key That Doesn't Exist In Another.
I'm trying to select rows from one table that does not have a key in another table.

For instance:

I have a hospital table with two columns, id and name.

I have a patients table with three columns, id, name, and locationid.

Now I want to find those hospitals that have no patients. So I want to get those hospitals whos id does not equal any of the locationid's in the patients table.

Selecting Rows From Multiple Tables
If i have 2 tables with a column 'users'
is there a way to select table1.users from table1 where table1.users is not in table2.users?

Selecting Random Rows In A VERY Large Table?
I would like to select random rows in a very large mysql table but I don't want to use something like that:

"SELECT * FROM tablename
ORDER BY RAND() LIMIT 1000";
Because it's horribly slow when you have a large table like mine (6 million rows+).

Selecting Rows Whose Values Begin With A Certain Character
Let's say my table just contains just one column of words:

apple
bear
banana
water
salt
sugar
cat
zebra

How would I write a select statement that selects all rows where the word begins with the letter b, s or z?

SELECT word FROM mytable WHERE word (code goes here)

Would I need a case statement for this?

Selecting Previous And Next Rows Based On One Input
let me start by what i am doing:

SELECT msgid FROM messages WHERE uid=1;
output:
+----------------------------------+
| msgid |
+----------------------------------+
| 1588ad275a80f22e6206364abbba530a |
| 37ce77bea4481ab7ed2625055512467e |
| 4bf7e4cb13590da24625821c795eb8d7 |
| 57628792ba507161e3f73daff3c972b7 |
| 91e95b3fbe9f04467023cfe84e6ed6e2 |
| e710bb38be4f3cbd05b6db594f7e8805 |
+----------------------------------+

how would i get it so if i enter a value it will return the previous and next rows.

SOME_SQL WHERE msgid='57628792ba507161e3f73daff3c972b7' AND uid=1;
output:
+----------------------------------+
| msgid |
+----------------------------------+
| 4bf7e4cb13590da24625821c795eb8d7 |
| 57628792ba507161e3f73daff3c972b7 |
| 91e95b3fbe9f04467023cfe84e6ed6e2 |
+----------------------------------+

Selecting A MINIMUM And MAXIMUM Number Of Rows.
I have a problem where I want to get no more than 10 (LIMIT) of the newest records from a table that are within a month, but I want to get at MINIMUM 3 records, all using SQL.

I can do :

-------------------
SELECT *
FROM updates
WHERE date_of_update
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL 1
MONTH ) AND CURDATE( )
ORDER BY date_of_update DESC
LIMIT 0 , 10
-------------------

To limit it to the 10 updates no older than a month, but if there are no updates within a month I get nothing.

So I would like to do something like :

-------------------
SELECT *
FROM updates
WHERE ROWNUM() < 3 OR ((date_of_update
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL 1
MONTH ) AND CURDATE( )) AND update_id IN (
SELECT *
FROM updates
ORDER BY date_of_update DESC
LIMIT 0, 10
))
-------------------

This code will take the 10 most recent updates and then starts pulling them out as long as the number of rows is less less than 3 or the update is within the date range and one of the 10 recent updates. The problem is that mySQL appears not to have any kind of rownum function.

Limit is a nice substitute except it only works for setting an upper bound, not a lower bound number of records.

Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value.
example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates).

how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Selecting Data
I am trying to make the below command work, but I seem to have missed the point completely from the manual, i am complete confused....
This is what I understand the manual to mean.

T
++++++++++++
select a.* from AS_11419 a

where not exists (select * from p_l p where p.RECORD_ID = a.IDno ) ;
++++++++++++++

the mysql reports "
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 'exists (select * from p_l.p where p.RECORD_ID != a.IDno )' at line
"

Selecting Data
I need to extract data that is between the current date and 13 weeks back. I have tried the query below but it does not seem to be working. Can anyone tell me what i am doing wrong

SELECT week_ending
FROM leadership_input_sheet
WHERE week_ending
BETWEEN 2007-01-01 AND (CURDATE() - INTERVAL 13 WEEK)

Using Sum And Selecting Data Fom 2 Tables

Here is my prob..

ADMIN TABLE
---------------------------------------
adm_id | adm_login
---------------------------------------

FILE TABLE
----------------------------------------------------
id | file | size | adm_id
----------------------------------------------------

Now the result i want is to list all admins with there sum of total file size everyone have uploaded

eg..

Selecting 2 Sets Of Data
I need to select two groups of data based on the time stamp.

Table 1

Card | Date | Time | #1 | #2 | #3
-----------------------------------------------------
1 | 2004-09-09 | 19:39:21 | 841630 | 251534 | 110
2 | 2004-09-09 | 19:39:21 | 895424 | 256112 | 86
3 | 2004-09-09 | 19:39:21 | 895527 | 256881 | 109
1 | 2004-09-09 | 19:49:51 | 841750 | 251545 | 112
2 | 2004-09-09 | 19:49:51 | 895634 | 256173 | 88
3 | 2004-09-09 | 19:49:51 | 895937 | 256885 | 113
1 | 2004-09-09 | 19:59:42 | 841934 | 251554 | 112
2 | 2004-09-09 | 19:59:42 | 895886 | 256182 | 89
3 | 2004-09-09 | 19:59:42 | 896222 | 256892 | 113

I need the latest set of data (time 19:59:42) for all three cards and
then I will need the next previous set of data (time 19:49:51) for all
three cards.

After I have the data, I'll do calculations to arrive at %, etc.

To select the max time, I've tried the following:

select table1.* from table1 left join table1 as t2 on table1.card =
t2.card and table1.time < t2.time where t2.card IS NULL order by
date,time,card;

I don't know how to determine the previous set of data.

Selecting Specific Data
I have query which goes like this

select b.name,a.id,i.bonus,r.rating,m.merit
from sps_ee a,sps_dept b,
(select id,ee_att_value bonus from ws_sps_ee_att d where  d.ee_class_id=11 and d.ee_att_id=1) i,
(select id,ee_att_value rating from ws_sps_ee_att d where  d.ee_class_id=7 and d.ee_att_id=20) r,
(select id,ee_att_value merit from ws_sps_ee_att b where  b.ee_class_id=1 and b.ee_att_id=1) m

where a.id=i.id and a.id=r.id and a.id=m.id

now i need to find data from tables r,i,m where the value (bonus,rating,merit)is 0 and replace with X

Selecting Data From 2 Tables
I have two tables in a database (MS SQL, but close enough). One table, called Table1 has a field BillingAddress. The other, Table2, has a field ShippingAddress.

Is there any way to search both tables for either a BillingAddress or ShippingAddress which starts with something (say, WHERE field LIKE '1 Street%'), then weed out the duplicates and return them all as one field?

Selecting Certain Data From Keywords
Is it possible to create a table where it selects certain rows from a field with the same value (like a keyword) and not showing other rows because the value is not the same. And to list all the rows in a table using php? for example:

If I had 20 rows and say 3 of them had the value 'cat', and I wanted to display only all the values with 'cat', and at the same time it includes the rest of the fields to do with that certain value. But making sure not to show the other 17 rows in a table using php.

Selecting Data Between Two Dates
I'm trying to return data for each day in a date range. If the day does not have a total I would like to use the last previous rows total for a new row. I have no idea how to do this. This sql returns.

select
ag.agcy_name, date(a.acct_signup_date), count(m.mem_member_id)
from transactions t
left join txn_type txt on t.txn_type_id = txt.txn_type_id
left join travel_type trt on trt.travel_type_id = t.travel_type_id
left join segment s on t.segment_id = s.segment_id
left join member m on m.mem_member_id = t.member_id
left join account a on a.acct_account_id = m.mem_account_id
left join agency ag on ag.agcy_agency_id = a.acct_agency_id
left join group_member_relation g on g.group_mem_rel_member_id = t.member_id
where a.acct_status_id ='1'
and a.acct_signup_date between ? and ?
group by ag.agcy_name, date(a.acct_signup_date) .

Selecting Data From 7 Tables
I have 7 tables in database. All of them shares a common coloumn called PMID.. i want to select data from all these 7 tables on the bases of PMID and am using the following query.Code:

$query = "Select * from pmstep1,pmstep2,pmstep3,pmstep4,pmstep5,pmusa,pmca
where pmstep1.pmid = pmstep2.pmid = pmstep3.pmid = pmstep4.pmid = pmstep5.pmid = pmca.pmid = pmusa.pmid ='shasha'";

but when i execute it on database using phpmyadmin. or command line to check my system hangs and it start to behave like 386 computers.. Please guide me that how can i select data based on this common coloumn from 7 tables. i never used JOINS and am not sure whether it has to do with joins or not.

Selecting Data From More Than One Table (not Join)
I have a database with a bunch of tables, including two tables that store messages. These two tables have different names, but are identical in structure.

In my app, a user can run a report that selects messages from both of these tables. This is currently achieved by running two selects and printing out the details.

However I would now like to sort and limit (i.e. show messages 1-25 and have a link to show the next 25 etc).
I can do this in the code by dumping the combined results in an array and then sorting/limiting the output, but this will cause problems if the number of messages gets too big and the array size becomes too large for the memory in the server.
Also it doesn't help any with optimization and won't speed up the query time as I won't be able to use the LIMIT clause in the SQL!

So what I want to know is whether it is possible in SQL to select data from more than one table, given that the two (or more) tables to be joined will be identical in structure? If so, can I perform the normal ORDER BY and LIMIT functions?

Selecting Data From Multiple Tables
I have three related tables. tv_observersation, which contains stationID with the start time and end time for a tv station program viewed (the stationID isn't a key field as the same station can be viewed different times). A stations table, which contains the stationID and the station_Name, and an advertisement table, which contains a list of advertiserID and which stationID they are subscribed too (a one to many table).

I am trying to generate a query, that would take out info i.e, the info from only stations that a specific advertiser has subscribed to in the advertisment table, but I am getting duplicate info in the query results from the observations table for a specific table. I think it may be a problem with how I am using the select statement. Do I want to select from two tables observations, stations like I am below, or should I use the other two (stations, and advertisments) with the inner join statement.

The objective is to retrieve only the stations that a advertiser is subscribed to from the observations table. Code:

Selecting Portion Of A Data From A Field? Help...
what would be the function to select lets say only about 40 characters within a varchar data field?

example that data field may contain about 500 characters, but i only want to select to first 20 characters to output.

Selecting Data From Single Table... Subqueries
I have a table with a list of books, their genres (type), and price. I have attached it as an image.

I would like to select all books whose price is higher than all books of type HOR. Basically, I think that this means: find the book of type HOR that is the most expensive. Then, select all other books whose price is greater than that.

In this example, the most expensive HOR book is 19.60. Therefore, I need to select all books with price > 19.60 and not of type HOR.

I tried using a subquery like this:

SELECT * FROM BOOK WHERE PRICE > (SELECT MAX(PRICE) FROM BOOK WHERE TYPE='HOR' GROUP BY TYPE)
However, I get an error that says:

Quote:

#1064 - 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 'SELECT MAX( PRICE ) WHERE TYPE = 'HOR' GROUP BY TYPE )
LIMIT

I looked up that error in the mysql doc but it didn't have much info in there:

Quote:

Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR)
Message: %s near '%s' at line %d

Selecting Multiple Columns Including Encrypted Data
Is there a way of selecting all columns from a table, including (aes)encrypted columns without having to individually name each column name->value. e.g.

SELECT * FROM table;

instead of

SELECT column1, column2, column3, AES_DECRYPT(column3, 'salt') FROM table;

I only ask because I have tables with dozens of columns and specifying every individual column will be very time consuming.

Many-to-many Using Link Table And Selecting Only Rows That Have More Than One Link
Okay, I am not 100% sure if I am explaining this correctly, which is why I am having a hard time finding answers via a search.

Here is my db structure:

tbl_object
----------
object_pk
name
etc

tbl_category
------------
category_pk
name

tbl_object_category_link
------------------------
object_fk
category_fk

I have successfully ran queries where I am looking for objects in just one category using:

SELECT tbl_object.name FROM tbl_object, tbl_category WHERE object_pk=object_fk AND category_pk="2"

However, if I want to only get objects that are in multiple categories, I am stuck. I can either pull ones that are in either/or like this:

SELECT tbl_object.name FROM tbl_object, tbl_category WHERE object_pk=object_fk AND (category_pk="2" OR category_pk="1")

The next "logical" step for me was to to this:

SELECT tbl_object.name FROM tbl_object, tbl_category WHERE object_pk=object_fk AND category_pk="2" AND category_pk="1"

Adding Up Data From Multiple Rows?
If I have numerical information stored in different rows what is the best method to find the sum of all the numerical information in the rows?

Import A Csv To Update Only Certain Rows Of Data?
I have an existing mysql table and a csv. I want to import the csv into the table, but it is at a different sort order. There are too many records to sort my csv by hand to match. There is a common 'cd_code' field that both have.

Is it possible to import a csv to the table so that each row of the csv is inserted in the correct row of the table, based on the common 'cd_code' field?

For example, if I sorted by cd_code asc, it would match the csv, but how to I import the csv into a particular sort?

Separating Parts Of Data Into Different Rows
I wasn't quite sure where to post this so I'm sorry if it's in the wrong category. I have a great deal of data I need to enter into my database which starts in the form xxxxxx:xxxxxx where the x's always vary in length and there is always a ":" dividing the two parts of data. I need to seperate these two parts of data and have the first part before the ":" go into one row and the second half after the ":" go into another row (both rows in same table)...the ":" can be discarded. I'll be adding this data manually by uploading a txt file to the database in the form of:

xxxx:xxxx
xxxxxxxx:xx
xxxxxxx:xxxxxxx
xxx:xxxxxxx

I have no idea how I would go about doing this. It would really save me time if it was possible to code a small script that automatically does this everytime I upload a txt file.

How To Import Data Into Existing Rows Of A Table?
I have moved 665 records off of Filemaker into mysql-4.0.18. I had a
great deal of trouble exporting a text field, possibly because it had
control characters that interferred with my field delimiter (also it
was from a Mac, and I'm new at this.)

So I have a table called invoices and another table called tasks, that
has only an invoice number and the work billed on that invoice number.
To see the work billed along with the other invoice data I have to do
a join, and this works but is clumsy for me. Since tasks.work is a
text field of 'infinite' variety I don't see the sense of keeping it
in a separate table like I would with clients' info; it belongs *in*
the invoice table, not just with it.

Is it possible to import this text data into a field in the invoice
table? I've messed around with this but it always appends the data as
new records. Perhaps I was doing an insert back then. Is the answer
something like 'load data infile 'workdone.csv' into invoices
(workdone);'

The invoices table was filled in a certain order (by invoice number)
and the workdone.csv was exported in the same order, so each invoice
row should get its correct text.

Is this a good thing, or am I thinking too 'flat-file-ish'?