求助，加入3表MySQL（图解说明）

Question

Good night,

I'm not an expert in MySQL, I have 3 tables, but I can't find the way to join in a final table in a specific order.

Here are the 3 tables, and finally the last table as I want the final table. Thankyou

Table 1 (Joomla Users)

-------------------------------
|id(K)|username   |email      | 
|-----|-----------|-----------|
|71   |JOHN       |[e-mail] |
|-----|-----------|-----------|
|72   |ANA        |[e-mail] | 
|-----|-----------|-----------|
|73   |PETER      |[e-mail]|
|-----|-----------|-----------|
|74   |MARK       |[e-mail]|
|-----|-----------|-----------|
|75   |TONY       |[e-mail]|
|-----|-----------|-----------|




Table 2 (ExtraFields)

-------------------------
|Field_id(K)| Title     | 
|-----------|-----------|
|1          |MARRIED    |
|-----------|-----------|
|2          |CELL       |   
|-----------|-----------|
|3          |GENRE      |
|-----------|-----------|
|4          |AGE        |
|-----------|-----------|
|5          |WEBPAGE    |
|-----------|-----------|




Table 3 (ExtraFields values)

-------------------------------------
|Field_id(K)|JoomlaId(K)|Value      | 
|-----------|-----------|-----------|
|1          |71         |YES        |
|-----------|-----------|-----------|
|2          |71         |555-4565   |   
|-----------|-----------|-----------|
|3          |71         |Male       |
|-----------|-----------|-----------|
|4          |71         |34         |
|-----------|-----------|-----------|
|5          |71         |www.go.com |
|-----------|-----------|-----------|
|1          |75         |NO         |
|-----------|-----------|-----------|
|3          |72         |Female     |   
|-----------|-----------|-----------|
|5          |72         |www.me.com |
|-----------|-----------|-----------|
|4          |74         |38         |
|-----------|-----------|-----------|
|3          |74         |Male       |
|-----------|-----------|-----------|
|1          |73         |NO         |
|-----------|-----------|-----------|
|2          |73         |234-5654   |   
|-----------|-----------|-----------|
|3          |75         |MALE       |
|-----------|-----------|-----------|




Desired Resultant table ( i don't know hoe=w to do it)

----------------------------------------------------------------------------------
|id(K)|username   |email      |Married |Cell     |Genre  |Age    |WebPage        |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|71   |JOHN       |[e-mail] |YES     |555-4565 |Male   |34     |www.go.com     |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|72   |ANA        |[e-mail] |NO      |         |Female |       |               |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|73   |PETER      |[e-mail]|NO      |234-5654 |       |       |               |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|74   |MARK       |[e-mail]|        |         |Male   |38     |               |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|75   |TONY       |[e-mail]|NO      |         |Male   |       |               |
|-----|-----------|-----------|--------------------------------------------------|

Answer 1

You've got a couple of options here.

The basic answer to your question is this query..

SELECT u.*,
c1.value AS 'Married',
c2.value AS "Cell",
c3.value AS "Genre",
c4.value AS "Age",
c5.value AS "WebPage"
FROM users u
LEFT JOIN extrafields_values c1 ON c1.joomla_id = u.id AND c1.field_id = 1
LEFT JOIN extrafields_values c2 ON c2.joomla_id = u.id AND c2.field_id = 2
LEFT JOIN extrafields_values c3 ON c3.joomla_id = u.id AND c3.field_id = 3
LEFT JOIN extrafields_values c4 ON c4.joomla_id = u.id AND c4.field_id = 4
LEFT JOIN extrafields_values c5 ON c5.joomla_id = u.id AND c5.field_id = 5

But his is horrid. You're performing 5 joins every time you run this query, and if you add new extra fields you'll need to add joins.

You could do this dynamically using a stored procedure, but however you do it you'll be asking MySQL to do "unnatural" things.

My strong advice is for fields like these you ought to create a single table and do a singe join to it.

There are cases where it does make sense to have the kind of key/value pair table you've set up - where there are many extra_fields that are very sparsely populated, but for this kind of data (which is essentially user meta-data) I think you'd be better off creating a single table - it's ok to have null fields (they have almost no impact on the size of the DB) and your life will be simpler!