Joining data from 2 fields into the first one

[Larry]

OK, I'm just learning MySQL, or at least trying to. I have a table with data
as follows

USER FIELDID VALUE
1 1 Bob
1 2 Smith
2 1 John
2 2 Smith
etc.

I want to concatenate the first and last names and update the firstname fields
with the full name throughout the whole table. I know how to loop through the
data (in PHP), concatenate them, and write the result back to the first name
field, but I'm trying to learn SQL.

Isn't there an obvious way to accomplish the same thing with just SQL? Somehow
I just can't see it.

Many thanks,
Larry

[Bill Karwin]

"Larry" <no***@none.com > wrote in message
news:4r******** *********@torna do.socal.rr.com ...

OK, I'm just learning MySQL, or at least trying to. I have a table with
data
as follows

USER FIELDID VALUE
1 1 Bob
1 2 Smith
2 1 John
2 2 Smith
etc.

I want to concatenate the first and last names and update the firstname
fields
with the full name throughout the whole table. I know how to loop through
the
data (in PHP), concatenate them, and write the result back to the first
name
field, but I'm trying to learn SQL.

Isn't there an obvious way to accomplish the same thing with just SQL?
Somehow
I just can't see it.

You need a self-join:

SELECT CONCAT(firstNam e.value, ' ', lastName.value) AS FullName
FROM myTable AS firstName INNER JOIN myTable AS lastName
ON firstName.user = lastName.user AND firstName.field id = 1 AND
lastName.fieldi d = 2

However, I'm assuming you have more than just these two fields. You'll find
that you need as many joins as you have fields (minus one) if you store the
fields the way you're storing them. That is, if you need to retrieve all
fields for a given user in one query.

The design you're using is called Entity-Attribute-Value, or EAV. It's
often criticized because it doesn't scale well, and lacks referential
integrity. Aside from the lots-of-joins problem described above, here's
another problem: how can you make sure a given field has a value for each
user?

In a standard table design, you can make the field "NOT NULL" and any
attempt to INSERT or UPDATE the record without supplying a value for that
field results in an error.

In the EAV design, there's no way to enforce it, except by a comparatively
expensive task of querying for the field for that user and making your
application raise an error if the field is absent. Querying must be done
using an outer join:

SELECT CONCAT('User ID ', u.user, ' has no last name!') AS errorString
FROM myTable AS u LEFT OUTER JOIN myTable AS lastName
ON u.user = lastName.user AND lastName.fieldi d = 2
WHERE lastName.fieldi d IS NULL

Repeat the above test for all mandatory fields.

See also:
http://ycmi.med.yale.edu/nadkarni/In...%20systems.htm
http://classweb.gmu.edu/kersch/inft8...son/JAMIA5.pdf

Regards,
Bill K.

[Larry]

In article <e0********@ene ws2.newsguy.com >, "Bill Karwin" <bi**@karwin.co m> wrote:

"Larry" <no***@none.com > wrote in message
news:4r******* **********@torn ado.socal.rr.co m...

OK, I'm just learning MySQL, or at least trying to. I have a table with

big snip
See also:
http://ycmi.med.yale.edu/nadkarni/In...%20systems.htm
http://classweb.gmu.edu/kersch/inft8...son/JAMIA5.pdf

Regards,
Bill K.

Bill, thanks so very much for the informative lesson. I'll definitely check
out the links. I understand what you mean about the structure. As I said I'm a
novice at this, and was trying to keep my login table as compact as possible
and yes there is other info in this table which for most users will be blank,
which is OK, so I was trying to conserve total size.

One learns as one goes, thanks for the lesson!

Larry

[noone]

Larry wrote:

In article <e0********@ene ws2.newsguy.com >, "Bill Karwin" <bi**@karwin.co m> wrote:

"Larry" <no***@none.com > wrote in message
news:4r****** ***********@tor nado.socal.rr.c om...

OK, I'm just learning MySQL, or at least trying to. I have a table with

big snip

See also:
http://ycmi.med.yale.edu/nadkarni/In...%20systems.htm
http://classweb.gmu.edu/kersch/inft8...son/JAMIA5.pdf

Regards,
Bill K.

Bill, thanks so very much for the informative lesson. I'll definitely check
out the links. I understand what you mean about the structure. As I said I'm a
novice at this, and was trying to keep my login table as compact as possible
and yes there is other info in this table which for most users will be blank,
which is OK, so I was trying to conserve total size.

When you think about total size, what takes more total space?

1, 1, bob, some, more, data, here,
1 2 smith ,,,,

or
1, bob, smith, some, more, data, here,

??? :) One thing to remember each record will have so many bytes of
record overhead. in this case you saving one byte in the record plus
n-bytes in the index.

Short double records > single record


One learns as one goes, thanks for the lesson!

Larry

Yes, but these are also the lessons you remember :)

[RaptureZN]

I want to concatenate the first and last names and update the firstname fields with the full name throughout the whole table ... Isn't there an obvious way to accomplish the same thing with just SQL?

Yup ... I had a similar situation recently and found that the following worked just fine for me ... I know my post is a little late, but perhaps it may help someone else in the future :)

UPDATE ContactsDatabas e SET FirstName = CONCAT(FirstNam e,LastName)

... of course, you need to replace the DB name and field names with your own.