MYSQL :: Combine Fields From Many Rows

Combine Two Fields Into One?
How do I go about combining the values of two fields into a single field? I just learned about the field type "blob" (that is too funny by the way) and want to combine the two fields that I already have set up as varchar into the new field before drop those columns.

Combine 2 Fields
I've got the firstname and lastname in my member-table and I want to compare the whole name with the entered search string, like this:

select id from members where (fname + " " + sname) = 'Matt Dillon'

How do I do that? When I try the above I got the whole member list in result.

Combine Fields
i need to actually move one field from one table to another.... not combine it later on in php or anything like that, but actually move the field over...
the database is called dg_reviews, and the structure is..

games
> system
> reviewer

games2
> year
> genre

i want to put the tables from games into games 2

Combine Data From Different Fields
I have the following problem.

Let's say I have a table that looks like this:

Num1   Num2
----      ----
001       002

Both fields are text type. What am I supposed to do to get the result in one column such as this:
EDE001_002

which EDE is just some added string (could be numbers too as text), and Num1 ist connected to Num2 with an underscore (_).

Or can I do this easier with PHP? If yes, how?

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Swap Two Fields Bertween Two Rows
I have a table in which a field (ordine) is used for the orderer output of data.

This is not an index, since data are subdivided in different categories and therefore the value is not unique for the whole table.

Sometimes I need to change the display order and I was used to use a query like this one:

UPDATE globalmoneta as mon1,globalmoneta as mon2 SET mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";

probably not very efficient, but acceptable since this operation is not often performed.

Unfortunately, after a server upgrade this query is no more working. I suspect this is due to a different mysql version but I can not check it.

Any idea on how to obtain the same task with a different query?

Select Only Rows Of 2 Fields That Does Not Repeat
I would like select only rows where registerNo AND entryid that does not repeat.
Example, from the table only one row of 'registerNo=1 and entryid=sqbiiphiu1' and the last 5 rows will be selected.

I'm using php to build this. i'm not sure if distinct can help. this is wat i thought of
$query = "SELECT DISTINCT * FROM fn_vote WHERE registerNo = '".$registerNo."' AND entryid='".$entryid."'";

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.

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?

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)?

Combine Two Tables
I've tried to combine two tables, putting the content of one column in the second table.

INSERT/REPLACE INTO table1(column) SELECT column FROM table2

My problem is, that all the content is only added as new rows. But I need to insert the data from table1(column) in the existing rows of table2.

The sequence of rows should be oriented on another column in both tables with an key (like 55302024d5ce751f1746b3bcc8299558) for each row.

Combine Two Queries Cant Seem To Get It!
This is the problem, below is the table I need to query I need to select the id with only the mac address and also get the data of the keyword role. something like this:

SELECT id from table where mac address = (macaddress) and select data from table where keyword = role;

+-----+-------------+---------------+-------+
| id | keyword | data | flags |
+-----+-------------+---------------+-------+
| 263 | record_in | On-Demand | 0 |
| 263 | role | default | 0 |
| 263 | callerid | device <263> | 0 |
| 263 | allow | | 0 |
| 263 | qualify | no | 0 |
| 263 | callgroup | | 0 |
| 263 | pickupgroup | | 0 |
| 263 | nat | yes | 0 |
| 263 | mailbox | 263@default | 0 |
| 263 | username | 263 | 0 |
| 263 | mac | 00041324668B | 0 |
| 263 | record_out | On-Demand | 0 |
| 263 | context | from-internal | 0 |
| 263 | dtmfmode | rfc2833 | 0 |

Trying To Combine 2 Queries Into 1
i'm trying to combine 2 queries into 1 efficient query and i've tried many variations but i'm just not getting it right. i realise that i'm going wrong somewhere with the join expression....i think??

here's the first query that works....jobname

SELECT p1_job_title_code.job_title_01 AS jobname
FROM p1_job_title_code
WHERE p1_job_title_code.job_title_id = 'DESIG'

Combine Two Queries
I have these two queries that I would like to combine, but I am to the point, where I'm not sure its possible.

The two queries are:

//delete from correlation table
DELETE
FROM site_keyword
WHERE keyword_id = 50

//clean up keyword table
DELETE
FROM keyword k LEFT JOIN site_keyword sk
ON k.keyword_id = sk.keyword_id
WHERE sk.keyword_id IS NULL

Need To Combine 2 Tables Into 1
Is this how it's done?

INSERT INTO student (
(SELECT DISTINCT a.*
FROM student_spring a, student_summer b
WHERE a.unique_key != b.unique_key)
UNION
(SELECT DISTINCT b.*
FROM student_summer b, student_spring a
WHERE b.unique_key != a.unique_key)
)

I'm trying this but I keep losing data whenever I run this!

Also, how do I populate a database table from an XML file?

Two Table Combine
i have two(maybe more ) tables. i a now trying to create a virtual table basing on the existing tables.

the two tables have no relationship and both have the same number of records(e.g both 5) in the virtual table, i want i have all the fileds of both table and list all the records, the number of reords in the virtual table should be 5 not 10.
i tried times and times, but alway get 10.

Combine Two Tables
I already know how to LEFT JOIN table results together horizontally. But, how about vertically? In other words, I have two tables that are similar. Is it possible to design a query so that it returns matching results from both tables?
More detail:

Table A categories: 'id', 'cost', 'label', 'parameterA'

Table B categories: 'id', 'cost', 'label', 'parameterB', 'parameterC'

Would it be possible to run a select that would return results like this (with matching entries from both tables):

type (which table the item came from), id, cost

Combine Two Databases
in MyphpAdmin i have two separate databases, one for a script called gcards, one for phpbb.
what i'd like to do is cross reference it so that members could send cards without necessarily knowing another member's email addy... but to do this, i'd need the gcards to draw upon the user info in the phpbb.

is this even possible? i found this other thread from last year, but if this is talking about the same thing essentially, i'm too sql-illiterate to understand how i'd do it....

Combine 2 Statement
I am sure this one has been covered, but I can not find the awnser...
For pagnation purposes I need

select count(*) from items where type='xxx'

and to show the page I need

select * from items where type='xxx' LIMIT 25

Since my where clause is very large as is my table this seems like a waste to run these two statements.

Combine These Two Selects
i'm working on a project which includes a special kind of search. Till now i programmed it using two selects (connecting them together with help of php):

CODESELECT pro.*, mem.id, mem.name, mem.login, mem.email_work, mem2.id, mem2.name, mem2.login, mem2.email_work, tas.project, tas.owner, tas.assigned_to, tas.id, tas.worked_hours, tas.task_rate, tas.worked_hours*tas.task_rate AS earned, tas.start_date, tas.due_date, tas.complete_date
FROM projects pro
LEFT OUTER JOIN tasks tas ON tas.project = pro.id
LEFT OUTER JOIN members mem ON mem.id=tas.owner
LEFT OUTER JOIN members mem2 ON mem2.id=tas.assigned_to WHERE ( tas.assigned_to IN(10)) GROUP BY mem2.id,pro.id ORDER BY pro.id ASC

Combine 2 Strings
I am making a query from a few tables. In one table I've stored the name info in 3 columns, FirstName, LastName, Initial.
I would now like to join the name info together, but I'm not really clear on how to do that.

Code:

Names.FirstName & ' ' & Names.LastName

Doesn't seem to work for me. Is that the correct format? I'd like to have a space between the names.

Combine Contents
I would like to combine the contents of two tables if possible, the result will be used for my application.
To better understand my problem I export a DBF database to a MySQL one which will be used for my application. Over time the MySQL may be edited so obviously I want to keep this changes but at the same time I would like to get any new records from my DBF into this MySQL table.I thought about creating a temporary MySQL table exported from the latest copy of my DBF and somehow trying to merge the old MySQL table (that may contain changes) with the new one.
I have a look through the MySQL commands but can't seem to find anything that may help me with it.

How To Combine Results From 2 Tables ?
This is for a website for classified Ads. The Ads can be posted by registered users or the Ads can come from external partner sites.

Therefore, I have kept 2 user tables - one for registered users and one for non-registered users, and 1 table for Classified Ads. The Ads table holds Ads from both registered users and external sites, with a column which indicates the source of Ad, i.e. it has two values internal and external.

I want to display all Ads on a page. The user info is to be read from the 2 user tables depending on the source of the Ad. Most of the fields of the 2 user tables are same. However, since we ask registered users to provide more information, the table for registered users has more fields.

Combine SELECT Function
I need to select certain records and then replace then with a new value. I have been searching through the manual and also on this forum board but to no avail. I have tried loads of different combinations but can not find the answer.
I can select my records using the following:-

SELECT numcomments FROM wsnlinks_links WHERE numcomments < 53

And I can update my records using the following:-

UPDATE wsnlinks_links SET numcomments = 22

What I can't do is to combine these into one SQL query which is what I need.

Combine Multiple Records
I have a table, call it TEST, in which a records of a person's test dates and scores are kept.
For instance, a person might have taken twice a toefl test and one a math test. The table would look like this:
personId    test    score  date
1111        toefl   234    02/04/2005
1111        toefl   444    03/07/2006
1111        math    333    05/06/2006

I woudl like to loop through the records of that person and to put them all on one line. That is, I woudl like the result to show something like this:
personId test_1 score_1 date_1 test_2 score_2 date_2 test_3 score_3 date_3 ....

Is this at all possible with MySQL ? I am using Oracle 9i.

Two Dbs And Combine Result List
I have two dbs with identical structures. One of them is archive db, it contains over a million entries. The other is a smaller one and I use it for storing the new information. I want to create a query to search in both dbs (preferably starting with the smaller one for better performance) and display results from both dbs, possibly discarding duplicate entries (although there shouldnt be duplicates, but its possible).
I thought about querying the smaller DB first and if no results were returned, query the archive (I use PHP), but it would be hard to combine results from both dbs this way.

Combine Records Of A Column Of The Same Table
i have all my portal contents on the SQL Database in the following manner:

Node_id Contents
12 this the first page
12 welcome to the portal
12 this is some sample data
13 this the second page,
13 sample data

when a page is being loaded, depending on the node_id contents are retrieved and displayed. Now i need a query which would combine all records of the same Node_id (page) and display as one record.

Eg:
Node_id Contents
12 this the first page welcome to the portal this is...
13 this is the second page, sample data

Combine (Merge, Bring Togehter) 2 Databases ?
1st of all, i never used mySQL, so iÂ´m a totaly newbee - i just need to know one thing:

iÂ´ve 2 databases with the same values but with different entries, it is possible to combine both db into a single database.

Join Or Combine Two Tables Based On Unique Column
I use MySQL query browser and have made two tables in one database:

First table: Test
Column names: Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255))

Second table: Hashtest
Column names: Filename (Varchar(255)), RowNumber (Integer), MD5 (Varchar(255)), SHA1 (Varchar(255)), Pad (Varchar(255))

Of both tables the column RowNumber is the primary key. Also, in both tables the column Pad is the same (the same content, not exactly in the same order)

Now, I want to combine the two tables into one table, based on 'Pad'

I suppose I have to use the Script function of MySQL query browser. Please explain to me what I have to do to combine the two tables into one table. This third table has this columns:

Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255)), MD5 (Varchar(255)), SHA1 (Varchar(255))

Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state.

I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different.

Example:
The 'objects' table looks like this:
object_id, name, room
1, Hammer, 1
2, Mirror, 1
3, Spade, 2

So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade.
Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table

player_id, object_id, room
12, 1, 2

So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*

One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices:

Is it better to have one table which has many fields
or
many tables which each has few fields?

Is it true that the latter is worse because it will require many join operations?
What is the limitation of the first option (one table with many fields)?

Delimited Fields Or Unused Fields
I have a table which has, for instance, 5 fields and maybe people only need the first one to be filled in often.  So 4 might well be empty.  Is it better to join them into 1 delimited field or leave them as their own fields?
And - if I have some fields that are only relevant to some types of rows, is it better to have 1 field for them delimited, or have them as separate fields?  (the same issue there really)

The other side of it is - is it better to split such delimited fields in the mysql call, or split them afterwards in php?
I might know which is easier though I can do it either way now I've researched, but I don't know which is better.  I also don't know how big this db will get or how often these fields won't be used so it comes down more to the overall design issue of which is better.  I'm thinking efficiency, speed, db load, and so on.

Need To Combine A Delete Stmt And Select Stmt
I want to delete information from one table why using another table... Here it was the code looks like now...
CODEdelete from hl_data where type_number=4 and (select software_name from hl_software where software_name='" & lsSoftwareName & "') "

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

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:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A..

Example:

B.part = "abcdefg" matches A.part ="cde"

I cannot put literal in query.. it must be from column data..

It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.

Converting From Many Columns/few Rows To Few Columns/many Rows
So I have a table that looks like:

Code:

char_id1234(...up to 30)
582NULL416
25739NULL12
391NULLNULLNULL

Each char_id is the primary key in another table, each numerical column value is the primary key (skill_id) in a third table, and each data value is the primay key (rank_id) in a fourth table.

I am attempting to join all four tables into a single query, but it seems impossible without converting the table to something like this:

Code:

char_idskil_idrank_id
5812
5834
58416
2517
25239
25412

Int Fields...
I am using ASP, connecting to MySQL.

I have an issue, that appeared to be working OK, but having just read my web
logs, have found a problem.

I am using MySQLFront to connect to my ISPs MySQL Server.

I have created a table, with a defined int field, 11 wide (e.g. MyField
Int(11) )

Now, I am inserting a number that is 10 wide into it. However, just lately,
I am getting...

Microsoft Cursor Engine '80040e21'

Multiple-step operation generated errors. Check each status value.

Two typical numbers I am inserting...
8340801334
5731311330

In order to get around the problem, I have made the field a varchar(11) but
would prefer to go back to int(11).

Key Fields?
I have a few MySQL tables that do not have key fields associated with them.
I am linking to this MySQL database from Microsoft Access 2000 using the
DoCmd.TransferDatabase acLink method. When the link is created, I get
prompted with a "Select Unique Record Identifier". Here are my questions:

1. Is there a way to make this message not appear (or programatically
repsond to it) other than by adding a key field to the table?

2. If I select no Unique Record Identifier, the table becomes Read Only.
Is there a way to make this table link updatable without assigning a key
field to it?

Sum Of 6 Fields?
I need to build a table of 5 countries each countries have 6 users and each user has 6 grades and an extra field to have the sum of the 6 grades, I use PHP to display my results but when I come to display the result of each user I simply sum all the variables and then I display my total field, my question is there a way to solve this without adding my variables? A way that can be done though my tables?

I am asking this bocz at some part later I will have to display the top 10 students and I will the total of every user, one option I thought of is after adding the 6 variables is to insert into total the total grade, but I want to know if there are any better ideas, thanks in advance, this is part of my code which I use to fetch my data and display them:
while($row = mysql_fetch_array($query_list))
{
$user_name = $row['user_name'];
$country_name = $row['country_name'];
$score_1 = $row['score_1'];
$score_2 = $row['score_2'];
$score_3 = $row['score_3'];
$score_4 = $row['score_4'];
$score_5 = $row['score_5']; $score_6 = $row['score_6'];
$total = $score_1 + $score_2 + $score_3 + $score_4 + $score_5 + $score_6;
}

Int Fields...
I have an issue, that appeared to be working OK, but having just read my web
logs, have found a problem.

I am using MySQLFront to connect to my ISPs MySQL Server.

I have created a table, with a defined int field, 11 wide (e.g. MyField
Int(11) )

Now, I am inserting a number that is 10 wide into it. However, just lately,
I am getting...

Microsoft Cursor Engine '80040e21'

Multiple-step operation generated errors. Check each status value.

Two typical numbers I am inserting...
8340801334
5731311330

In order to get around the problem, I have made the field a varchar(11) but
would prefer to go back to int(11).

2 Fields
I have a search procedure for a db of users:
the form has one input box that lets them enter first name, last name, both, etc.
I split the keywords into an array (searchterm1, searchterm2, etc) (using php)
I'm wonderig if I can search on SearchName (rather than LName, FName, etc) in the following query:
$query="SELECT CONCAT(LName,', ',FName,' ',MName) as SearchName WHERE $and
I want to concat fields FIRST so that I can use a loop to query if SearchName is like searchterm1 AND searchterm2, rather than if LName is like searchterm1 or searchterm2 OR FName is like searchterm1 or searchterm2, which gets me too many results.
I don't want to use full-text search, because these are simply names, and therefore does not seem appropriate for full-text.
I am using MySQL 3.23.58

100 Fields
I want to store many attributes in a db. Like this:

User_1 field_1, field_2, field_3, etc, etc, etc------------>field_100
User_2
User_3
User_4
User_5
etc
etc

Is this possible? Is this bad db design?

Cap Fields
Is it possible to run a query and CAP all fields in a column. Can mysql do this kind of stuff?

Getting All Fields From A Table And ONLY ONE From Another One
maybe it's time to get some rest, today...

SELECT * , User_Avatar
FROM restaurantsrate, users
WHERE Card_ID =222443
AND Ratings_Deleted =0
AND Ratings_UserID = User_ID
LIMIT 0 , 30

I'm trying to get all fields from table restaurantsrate and only User_Avatar from table users, but I'm getting ALL fields from both tables? anyone can help?