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 |asd@go.com |
|-----|-----------|-----------|
|72 |ANA |sad@me.com |
|-----|-----------|-----------|
|73 |PETER |pet@mine.co|
|-----|-----------|-----------|
|74 |MARK |mark@nas.co|
|-----|-----------|-----------|
|75 |TONY |tony@gma.eu|
|-----|-----------|-----------|
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 |asd@go.com |YES |555-4565 |Male |34 |www.go.com |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|72 |ANA |sad@me.com |NO | |Female | | |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|73 |PETER |pet@mine.co|NO |234-5654 | | | |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|74 |MARK |mark@nas.co| | |Male |38 | |
|-----|-----------|-----------|--------|---------|-------|-------|---------------|
|75 |TONY |tony@gma.eu|NO | |Male | | |
|-----|-----------|-----------|--------------------------------------------------|
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!
**select** tbl_1.id, tbl_1.email , tbl_2.Field_id, tbl_2.Title, tbl_3.Field_id, tbl_3.joomlaId, tbl_3.value
FROM Table1 tbl_1, Table2 tbl_2, Table3 tbl_3
WHERE tbl_1.id = tbl_2.Field_id
AND tbl_2.Field_id = tbl_3.joomlaId
This would not produce your result but this is the way to link three tables using id fields.
I hope this will give you and idea . at least.