SQL Query: joining two fields from two separate rows

Question

I have the following two tables:

USER
FID  UID     VALUE
4    3       John
3    3    Doe
4    4       Jack
3    4       Russel

Should be fairly clear that FID 3 = Surname, and FID 4 = Name.

DATEDATA
UID  DATE  
3    1234
4    4321

I want to join these two tables, so that I end up with something like this:

UID  DATE  NAME   SURNAME
3    1234  John   Doe
4    4321  Jack   Russel

or... alternatively...

UID  DATE  FULLNAME
3    1234  John Doe
4    4321  Jack Russel

Any SQL gurus out there?

This is what I have so far:

SELECT UID, DATE, VALUE from DATEDATA as D left join USER as U on U.uid = D.uid where fid = 3 OR fid = 4

But that gives me this:

UID  DATE  VALUE
3    1234  Doe
3    1234  John
4    4321  Russel
4    4321  Jack

Anyone?

Answer 1

SELECT D.UID, DATE, U.VALUE + ' ' + U2.Value as fullName
from DATEDATA as D 
left join USER as U on U.uid = D.uid and U.fid = 3
left join USER as U2 on U2.uid = D.uid and U2.fid = 4

Though this could give you a NULL name whenever either first or last is NULL. You may want to use an ISNULL to make either name an empty string in that case if you can accept cases where the user would only have one name or the other in your system.

Answer 2

SELECT A.UID, DATEDATA.DATE, A.VALUE, B.VALUE from DATEDATA, USER A, USER B 
WHERE A.UID = B.UID AND A.FID = 3 AND B.FID = 4 AND DATEDATA.UID = A.UID

Answer 3

select 
  D.UID
, D.DATE
, isnull(U.VALUE, '') 'firstName'
, isnull(U2.VALUE, '') 'surName'
from
  DateData D
left join User U on U.uid = D.uid and U.fid = 3
left join User U2 on U2.uid = D.uid and U2.fid = 4

Answer 4

You can use something like the following. It assumes you always have a first name. If you always have some field and it's not first name, make that the first from and readjust the joins. If you're never guaranteed a value, then let us know and we'll work a harder solution.

select d.uid,COALESCE(d.date,'none entered'),COALESCE(frst.value,'') as NAME,COALESCE(lst.value,'') as SURNAME
from
user frst (NOLOCK)
left join user lst (NOLOCK) on lst.uid=frst.uid
left join datedata d (NOLOCK) on d.uid = frst.uid
where
frst.fid=4
AND lst.fid=3