[SOLVED] Need JOIN help -- table ID displaying incorrectly with added R JOIN ??

[thepip3r]

mysql ver:  5.0.67

 

so i couldn't figure out what i was doing wrong.  sqaid (auto inc, p.key) is unique.  yet for one of my joins, it would be showing multiple records with the same sqaid.  i've narrowed it down to one part of my join but due to my ignorance of sql queries, i'm having a problem figuring out why...

 

when i use this query:

SELECT * 
FROM survey_question_answers sqa
RIGHT JOIN survey_questions sq ON (sqa.sqid=sq.sqid)
RIGHT JOIN survey_main sm ON (sm.smid=sq.smid)
ORDER BY sqa.sqaid

 

...everything displays fine with sqa.sqaid being unique... but, when i add:

 

SELECT * 
FROM survey_question_answers sqa
RIGHT JOIN survey_questions sq ON (sqa.sqid=sq.sqid)
RIGHT JOIN survey_main sm ON (sm.smid=sq.smid)
RIGHT JOIN survey_submitter ss ON (ss.smid=sm.smid)
ORDER BY sqa.sqaid

 

~five records share the sqaid in the result set when i add that last join.  any thoughts on what i'm doing wrong that's making mysql display the wrong sqaid with that added join?  how would i keep it unique so i could use it to sort by?

[fenway]

Why right join?

[thepip3r]

tbh fenway... i've read many tutorials on joins and am not really sure what's the diff between r/l/inner/outer joins.  i just play with a sql query until i get the result set i want and go with that.  =/

 

p.s. don't worry, i haven't ever worked on any serious projects. =P

[fenway]

So let's switch to INNER JOIN for now (i'm assuing there are matching rows).

 

Could you show some sample output?

[thepip3r]

sample output:

 

sqaid 	sqid	vc	txt	sqid	smid	question	question_type	question_order	smid	cid	name	ssid	smid	org	first_name	last_name	timestamp
1	1	Very Satisifed	 	1	1	How satisfied are you with your ability to reach a...	multi	1	1	1	Technical Support Feedback	1	1	acme	steve	johnson	1238195393
1	1	Very Satisifed	 	1	1	How satisfied are you with your ability to reach a...	multi	1	1	1	Technical Support Feedback	2	1	acme	steve	johnson	1238195407
1	1	Very Satisifed	 	1	1	How satisfied are you with your ability to reach a...	multi	1	1	1	TTechnical Support Feedback	3	1	falsetto inc.	jennifer	stevens	1238195703
1	1	Very Satisifed	 	1	1	How satisfied are you with your ability to reach a...	multi	1	1	1	Technical Support Feedback	4	1	falsetto inc.	jennifer	stevens	1238195718

 

derived from:

SELECT * 
FROM survey_question_answers sqa
INNER JOIN survey_questions sq ON (sqa.sqid=sq.sqid)
INNER JOIN survey_main sm ON (sm.smid=sq.smid)
INNER JOIN survey_submitter ss ON (ss.smid=sm.smid)
ORDER BY sqa.sqaid

[thepip3r]

to elaborate further w/examples, here is my problem exactly.  with the query:

SELECT sqa.sqaid, sq.question, sqa.vc, sqa.txt
FROM survey_question_answers sqa
RIGHT JOIN survey_questions sq ON ( sq.sqid = sqa.sqid ) 
RIGHT JOIN survey_main sm ON ( sm.smid = sq.smid ) 
WHERE sm.cid =  '1'
AND sm.smid =  '1'
ORDER BY sqa.sqaid
LIMIT 0 , 30

..iget a result set looking like: 

sqaid 	question	vc	txt
1	How satisfied are you with your ability to reach a...	Very Satisifed	 
2	How satisfied were you with our representative's l...	Very Satisifed	 
3	How satisfied were you with the representative's l...	Very Satisifed	 
4	When solving your problem, were you satisfied with...	Very Satisifed	 
5	Do you have any additional comments regarding your...	 	Yes - make it do more!
6	How satisfied are you with your ability to reach a...	Very Dissatisifed	 
7	How satisfied were you with our representative's l...	Very Dissatisifed	 
8	How satisfied were you with the representative's l...	Very Dissatisifed	 
9	When solving your problem, were you satisfied with...	Very Dissatisifed	 
10	Do you have any additional comments regarding your...	 	It\'s horribad!

 

but when i add:

SELECT sqa.sqaid, sq.question, sqa.vc, sqa.txt, ss.first_name, ss.last_name, ss.timestamp 
FROM survey_question_answers sqa
RIGHT JOIN survey_questions sq ON ( sq.sqid = sqa.sqid ) 
RIGHT JOIN survey_main sm ON ( sm.smid = sq.smid ) 
RIGHT JOIN survey_submitter ss ON ( ss.smid = sm.smid ) 
WHERE sm.cid = '1'
AND sm.smid = '1'
ORDER BY sqa.sqaid, ss.timestamp

 

i get:

sqaid	question	vc	txt	first_name	last_name	timestamp
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	cameron	wilson	1238195393
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	cameron	wilson	1238195407
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	jennifer	stevens	1238195703
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	jennifer	stevens	1238195718
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	jerry	johnson	1238196065
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	jerry	johnson	1238196092
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	stacey	frey	1238196129
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	sue	mccabe	1238539027
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	cameron	wilson	1238629695
1	How satisfied are you with your ability to reach a...	Very Satisifed	 	cameron	wilson	1238629764
2	How satisfied were you with our representative's l...	Very Satisifed	 	cameron	wilson	1238195393
2	How satisfied were you with our representative's l...	Very Satisifed	 	cameron	wilson	1238195407
2	How satisfied were you with our representative's l...	Very Satisifed	 	jennifer	stevens	1238195703
2	How satisfied were you with our representative's l...	Very Satisifed	 	jennifer	stevens	1238195718
2	How satisfied were you with our representative's l...	Very Satisifed	 	jerry	johnson	1238196065
2	How satisfied were you with our representative's l...	Very Satisifed	 	jerry	johnson	1238196092

[thepip3r]

tried it both:

 

SELECT * 
FROM survey_question_answers sqa, survey_questions sq, survey_main sm, survey_submitter ss
WHERE sqa.sqid=sq.sqid
AND sq.smid=sm.smid
AND ss.smid=sm.smid
ORDER BY sqa.sqaid

 

and

 

SELECT sqa.sqaid, sq.question, sqa.vc, sqa.txt, ss.first_name, ss.last_name, ss.timestamp 
FROM survey_question_answers sqa
JOIN survey_questions sq ON ( sq.sqid = sqa.sqid ) 
JOIN survey_main sm ON ( sm.smid = sq.smid ) 
JOIN survey_submitter ss ON ( ss.smid = sm.smid ) 
WHERE sm.cid = '1'
AND sm.smid = '1'
ORDER BY sqa.sqaid, ss.timestamp

 

and still the same problem.  anyone have any thoughts, suggestions, a place to look?

 

edit:  both of those queries have the exact same problem.  they run fine w/o the inclusion of the survey_submitter table.  as soon as that's added into the SQL query, the sqaid field starts showing incorrect information.  btw, my hosting provider is running phpmyadmin 2.11.9.4 if that helps at all.

[thepip3r]

ok... last ditch effort to try to explain this -- here is the basic relational hierarchy I'm trying to achieve in a single query:

 

smid = survey ids
ssid = survey submitter ids
sqid = survey questions ids
sqaid = survey question answers ids

     smid
     /  \
ssid	 sqid
            \
          sqaid

 

so the basic logic behind it is that when a user submits a query for a particular survey, they will get all of the survey name, the survey submitter name, the survey questions, and the answers for each question.  my problem is that once i add that third join (no matter how i structure it), my resultset is always incorrect information in a full-mesh type scenario:  users informations is being shown next to data that they did not submit -- and not only that, each user is shown as answering the same answers everyone else did as well as their own; and again, this is ONLY when I include that last join.  <scratching head> -- is there a way to achieve the diagram above with the resultset below in a single query?

 

[surveyname, surveysubmissionid, submittername, surveyquestion, surveyquestionanswer]
basicsurvey, 1, tim, how old are u, 12
basicsurvey, 1, tim, where do u live, cali
basicsurvey, 1, tim, how many sibs?, 3
basicsurvey, 2, nancy, how old are u, 80
basicsurvey, 2, nancy, where do u live, tex
basicsurvey, 2, nancy, how many sibs?, 0
basicsurvey, 3, steve, how old are u, 55
basicsurvey, 3, steve, where do u live, ga
basicsurvey, 3, steve, how many sibs?, 2

 

have i made my case clear enough?  do i have to break this up into two queries?  pls help!

[fenway]

It seems to me that you have a 1-to-many relationship with that other table, so obviously you're getting back multiple rows...

 

Switch to inner join and try sq/sqa/sm -- without ss -- and we'll take it from there.

[thepip3r]

@fenway -- i'm sorry for wasting ur time.  with help, i was pointed to the fact that i have no relation to the submitter and their answers.  o.O  i'm sorry for such a basic mistake, i don't know why i didn't see it before.  i'm going to have to add a field to the answers section so i'll know how submitted them and redo some of my other queries -- sorry again for the waste of time and ty greatly for the attempt to help.

[fenway]

No problem.. everyone has to learn sometime.