Query question

I have this query:

SELECT
    pd.person_id, pd.first,pd.email
FROM
    pd
INNER JOIN
    srf ON pd.person_key = srf.person_key
WHERE
    srf.survey_option_key IN (297,298,299)
AND
    srf.survey_question_key = '43'
AND
    pd.is_subscriber ='1'
ORDER BY
    pd.person_id ASC;

Open in new window


I would like to also grab data from a column called "option_text" in the sod table. I am having trouble figuring out how to join

sod.option_text ON sod.survey_option_key =  srf.survey_option_key and display the results . Any ideas?
lvollmerAsked:
Who is Participating?
 
keyuConnect With a Mentor Commented:
SELECT
    pd.person_id, pd.first,pd.email,sod.option_text
FROM
    pd,sod
INNER JOIN
    srf ON pd.person_key = srf.person_key
WHERE
    srf.survey_option_key IN (297,298,299)
AND
    srf.survey_question_key = '43'
AND
    srf.survey_option_key =  sod.survey_option_key
AND
    pd.is_subscriber ='1'
ORDER BY
    pd.person_id ASC
0
 
johanntagleCommented:
Should just be:

SELECT
    pd.person_id, pd.first,pd.email,sod.option_text
FROM
    pd
INNER JOIN
    srf ON pd.person_key = srf.person_key
INNER JOIN
    ON srf.survey_option_key =  sod.survey_option_key
WHERE
    srf.survey_option_key IN (297,298,299)
AND
    srf.survey_question_key = '43'
AND
    pd.is_subscriber ='1'
ORDER BY
    pd.person_id ASC

Open in new window


Or maybe you need LEFT OUTER JOIN instead of INNER JOIN, in case the entry on sod is optional.
0
 
lvollmerAuthor Commented:
I had previously tried your solution and when I do, i get a message :

Table 'sod.option_text' doesn't exist

and it does , in fact, exist.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
johanntagleCommented:
sorry typo.  line 8 above should be "sod ON srf.survey_option_key = sod.survey_option_key"
0
 
lvollmerAuthor Commented:
yeah I had caught that and fixed it but still got that error.
0
 
johanntagleCommented:
Odd.  Can you share a dump of the tables so I can test it from my end?
0
 
awking00Commented:
Is sod table in same schema as pd and srf?
0
 
lvollmerAuthor Commented:
I believe so,

PD and SRF share  a unique key that I am able to join on,  sod does not have that same key, but it does have a key that matches up with SRF

IE:

PD & SRF share "person_id"

SRF & SOD share "survey_option_key"
0
 
awking00Commented:
Can you do a describe of the sod table (i.e. column names and datatypes)?
0
 
lvollmerAuthor Commented:
it will take me a bit but I will get this info and upload it
0
 
keyuCommented:
I have tried my posted query and uts working exactly as you want can you please comment something on this
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.