CODEWITHSUNDEEP
mysqljoin
Rick Good
Rick Good

Reputation: 59

Joining 3 tables with 2 being SELECT *

I am doing research with the test-retest. I have three tables. One table is for Demographics and I only need a limited amount of data from it. The other two tables each have 235 fields the only difference being the table name. I've tried statements like:

SELECT * FROM research_statement_answers, statement_answers, demographic_answers
WHERE (( research_statement_answers.id_Questions = demographic_answers.id_Demographic)
and (statement_answers.id_Questions = demographic_answers.id_Demographic)
and (demographic_answers.Research_Completed = 1))

But that does not work. All examples I've seen would require me to name all 235 fields in both tables. I can do that, but I cannot believe that's the best way to proceed. Suggestions are therefore most welcomed. Thank you. Rick

Upvotes: 2

Views: 47

Answers (3)

John Nuñez
John Nuñez

Reputation: 1830

    SELECT 
    research_statement_answers.*, 
    statement_answers.*,
    demographic_answers.*
 FROM research_statement_answers
    JOIN demographic_answers ON demographic_answers.id_Demographic = research_statement_answers.id_Questions
    JOIN statement_answers ON demographic_answers.id_Demographic = statement_answers.id_Questions
    WHERE demographic_answers.Research_Completed = 1

Upvotes: 0

Mihai Stancu
Mihai Stancu

Reputation: 16107

SELECT
    research_statement_answers.*,
    statement_answers.*,
    demographic_answers.*
FROM ...

Since these tables are different only in name wouldn't it be better to use UNION JOIN on them?

The result would be that you have the reunion of all table records.

With a normal join the result is 235*3 columns per record a subset of records representing the intersection between the three tables.

Upvotes: 1

Joseph Woodward
Joseph Woodward

Reputation: 9281

You can select * from multiple tables and only include specific colums of a third table by doing the following:

SELECT
    research_statement_answers.*,
    statement_answers.*,
    demographic_answers.id,
    demographic_answers.title,
    demographic_answers.etc
FROM ...

Upvotes: 0

Related Questions