"Larry" <no***@none.com> wrote in message
news:4r*****************@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/In...%20systems.htm http://classweb.gmu.edu/kersch/inft8...son/JAMIA5.pdf
Regards,
Bill K.
