Joining data from 2 fields into the first one

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

Re: Joining data from 2 fields into the first one

"Larry" wrote in message
news:4rPUf.9449$WK1.3536@tornado.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(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
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.fieldid = 2
WHERE lastName.fieldid IS NULL

Repeat the above test for all mandatory fields.

See also:
http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20 systems.htm
http://classweb.gmu.edu/kersch/inft864/Readings/Jamison/JAMI A5.pdf

Regards,
Bill K.

Re: Joining data from 2 fields into the first one

In article , "Bill Karwin" wrote:
>"Larry" wrote in message
>news:4rPUf.9449$WK1.3536@tornado.socal.rr.com...
>> 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/Introduction%20to%20EAV%2 0systems.htm
>http://classweb.gmu.edu/kersch/inft864/Readings/Jamison/JAM IA5.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

Re: Joining data from 2 fields into the first one

Larry wrote:
> In article , "Bill Karwin" wrote:
>
>>"Larry" wrote in message
>>news:4rPUf.9449$WK1.3536@tornado.socal.rr.com...
>>
>>>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/Introduction%20to%20EAV% 20systems.htm
>>http://classweb.gmu.edu/kersch/inft864/Readings/Jamison/JA MIA5.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 :)