469,927 Members | 1,835 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

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

Mar 24 '06 #1
4 3665
"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.
Mar 24 '06 #2
In article <e0********@enews2.newsguy.com>, "Bill Karwin" <bi**@karwin.com> wrote:
"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

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
Mar 24 '06 #3
Larry wrote:
In article <e0********@enews2.newsguy.com>, "Bill Karwin" <bi**@karwin.com> wrote:
"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


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 :)
Mar 25 '06 #4
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 ContactsDatabase SET FirstName = CONCAT(FirstName,LastName)

... of course, you need to replace the DB name and field names with your own.
Apr 7 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ben Willcox | last post: by
2 posts views Thread by James | last post: by
9 posts views Thread by Eric Sabine | last post: by
3 posts views Thread by VMI | last post: by
4 posts views Thread by Rnt6872 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.