I have 5 tables from which i need to create one table, here are the 5 tables and their pertinent fields only. I color coded them to indicate how they relate to one another:

jml_jvotesystem_votes
• user_id = jml_jvotesystem_users : id
• answer_id = jml_jvotesystem_answers : id

jml_jvotesystem_users
• id = jml_jvotesystem_votes : user_id
• jid = jml_users : id

jml_jvotesystem_answers
• id = jml_jvotesystem_votes : answer_id
• answer

jml_users
• id = jml_jvotesystem_users : jid
• name
• email

jml_comprofiler
• id
• user_id
• cb_city
• cb_instrumentsplayed
• cb_contact

The end result is I need list of all the votes from the first table, jml_jvotesystem_votes, and the table should have these fields shown by” table name: field name”

1) jml_jvotesystem_answers : answer
2) jml_users : name
3) jml_users : email
4) jml_comprofiler: city
5) jml_comprofiler : cb_instrumentsplayed
6) jml_comprofiler : cb_contact

Please provide the SQL Query I should use to get this to display.

I deeply appreciate the help,

Gorden

Hello,

Try to use the next SQL query:



Regards,
ARI Soft

Thanks, but I can not get it to work. I get a Syntax error on line 10 but I can;t see anything wrong, though I think there was one error as highlighted below. I will email my phpMyAdmin Access to you, then you can quickly test it out.

Do I need a Where statement? I want to show all results.

===============================================================

SELECT
A.answer,
U.name,
U.email,
CP.cb_city AS city,
CP.cb_instrumentsplayed AS instrumentsplayed,
CP.cb_contact AS contact
FROM
#__jvotesystem_votes V INNER JOIN #__jvotesystem_users VU
ON V.user_id = VU.id
INNER JOIN #__jvotesystem_answers A
ON V.answer_id = A.id
INNER JOIN #__users U
ON VU.jid = U.id
INNER JOIN #__jml_comprofiler CP
ON U.id = CP.user_id

Use the following query:

SELECT
A.answer,
U.name,
U.email,
CP.cb_city AS city,
CP.cb_instrumentsplayed AS instrumentsplayed,
CP.cb_contact AS contact
FROM
#__jvotesystem_votes V INNER JOIN #__jvotesystem_users VU
ON V.user_id = VU.id
INNER JOIN #__jvotesystem_answers A
ON V.answer_id = A.id
INNER JOIN #__users U
ON VU.jid = U.id
INNER JOIN #__comprofiler CP
ON U.id = CP.user_id

Regards,
ARI Soft

THANK YOU, THANK YOU, THANK YOU, WORKS BEAUTIFULLY.