Joining data from 2 fields into the first one

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

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

  1        1          Bob
  1        2          Smith
  2        1          John
  2        2          Smith

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,

Re: Joining data from 2 fields into the first one

Quoted text here. Click to load it

You need a self-join:

SELECT CONCAT(firstName.value, ' ', lastName.value) AS FullName
FROM myTable AS firstName INNER JOIN myTable AS lastName
  ON firstName.user = lastName.user AND firstName.fieldid = 1 AND
lastName.fieldid = 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

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.fieldid = 2
WHERE lastName.fieldid IS NULL

Repeat the above test for all mandatory fields.

See also:

Bill K.

Re: Joining data from 2 fields into the first one

Quoted text here. Click to load it

big snip

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!


Re: Joining data from 2 fields into the first one

Larry wrote:
Quoted text here. Click to load it

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

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

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

Quoted text here. Click to load it

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

Site Timeline