[SOLVED] Query Structure Question

[CloudNine]

I'm working with a MySQL database table that looks, basically, like this...

 

| fid | uid |  value          |

|------------------------------|

|  1  |  1  |  Bob            |

|  2  |  1  |  Jones          |

|  3  |  1  |  102356        |

|  1  |  2  |  Todd          |

|  2  |  2  |  Smith          |

|  3  |  2  |  652891        |

|------------------------------|

 

The 'fid' column refers to a number assigned to a custom user profile field.  In this example, the value in the row with the fid of 1 corresponds to a First Name field, the value in the row with the fid of 2 to a Last Name field, and the fid of 3 to, let's say, an employee ID number.

 

The 'pid' column denotes the ID number of the profile that this information corresponds to.  In this case the pid or profile ID of 1 holds all the values relevant to Bob Jones, and the pid of 2 holds Todd Smith's values.

 

MY QUESTION:

 

What is the best query structure/code for obtaining a value or value(s) if I have a particular profile ID in mind and I want to say map the value in the row with pid=2 and fid=1 to a firstName variable, pid=2 and fid=2 to lastName, and pid=2 and fid=3 to employID?

 

Thank you!

[fenway]

You're looking to make a pivot table... data stored in this way isn't not really easily coerced into other formats.

[CloudNine]

A pivot table, excellent, I will look into this further.

 

Thanks!

[Barand]

try

SELECT a.fvalue as fname, b.fvalue as lname, c.fvalue as empID
FROM user2 a
JOIN user2 b USING (uid)
JOIN user2 c USING (uid)
WHERE a.uid = 2
AND a.fid = 1
AND b.fid = 2
AND c.fid = 3

 

-->

[pre]+-------+-------+--------+

| fname | lname | empID  |

+-------+-------+--------+

| Todd  | Smith | 652891 |

+-------+-------+--------+[/pre]

[fenway]

Using CROSS JOIN makes the purpose of the JOIN explicit....

[Barand]

Shoudn't a CROSS JOIN be used when there are no join criteria (ie a cartesian join) otherwise it's just an (INNER) JOIN

 

EDIT: In answer to that question, std SQL Yes.

In MySQL' date=' CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise. [/quote']

[fenway]

I never used "JOIN" as a bareword... it's always INNER, LEFT, or CROSS.  The first two require (or should, IMHO) an ON clause -- so I expect to see one right away.  The last one doesn't -- I use it when trying to find multiple matches in a many-to-many table -- so I use CROSS JOIN to indicate that the specified value will be in the where clause.  Basically, my rule is like this: if the "join" compares two DB fields, it goes in an ON clause; otherwise, it goes in a WHERE clause while referencing a constant (e.g. for CROSS JOIN).  Obviously, LEFT JOIN is sometimes an exception.  Besides, why not stick to standard SQL whenever possible?

[Barand]

I'll agree I should probably have specified INNER JOIN (but then again I never specify OUTER in LEFT OUTER JOINS) but they certainly aren't (standard sql) CROSS JOINs

[fenway]

To each his own... but if I'm not using an ON clause, then I like to be explicit so that I don't think it's a mistake.

[Barand]

But they are using ON clauses

 

"USING(X)" is identical to "ON a.X = b.X"

[fenway]

Sorry, I entirely missed that when I read the query... my bad.  When there are const join conditions, my brain has issues.

 

Generally, I write those as follows to make my brain hurt less:

 

SELECT a.fvalue as fname, b.fvalue as lname, c.fvalue as empID
FROM user2 AS a
JOIN user2 AS b ON (b.uid = a.uid AND b.fid = 2 )
JOIN user2 AS c ON (c.uid = a.uid AND c.fid = 3 )
WHERE a.fid = 1 AND a.uid = 2