Adding a line breaks the query

what's the error message?

from what I see, the JOIN clause should come with ON clause.

so like:

..
from yourTable a
join anotherTable b on a.Field = b.Field
join anotherTable2 c on a.Field2 = c.Field2
..

Select allOpen in new window

It should be returning one therapist. But it's not.

If I hard code like this I get the correct answer.

Select also tbl_answer_therapist.state to correct join it

SELECT tbl_answers_therapist.therapist_id,tbl_answer_therapist.state, therapist_name, photo,details

Select allOpen in new window

Nope...

@Ryan is right, use the correct JOIN syntax, currently you're logically doing a Cartesian product, which is then filtered. Also use table alias names. E.g.

SELECT *
FROM   tbl_answers_therapist AT
       INNER JOIN tbl_therapist_type TT ON TT.therapist_id = AT.therapist_id
       INNER JOIN tbl_answers A ON A.state = AT.state
WHERE  A.client_id = 456
       AND AT.status = 'Yes'
       AND A.type_id = TT.type_id
       AND A.practice_id = TT.practice_id
LIMIT 3;

Select allOpen in new window

or

SELECT *
FROM   tbl_answers_therapist AT
       INNER JOIN tbl_therapist_type TT ON TT.therapist_id = AT.therapist_id
       INNER JOIN tbl_answers A ON A.state = AT.state
                                   AND A.type_id = TT.type_id
                                   AND A.practice_id = TT.practice_id
WHERE  A.client_id = 456
       AND AT.status = 'Yes'
LIMIT 3;

Select allOpen in new window

Now you can see that we have a triangular relationship. These are pretty uncommon in a data model as they are pretty complex when implementing the necessary constraints. Here you need to check your data, cause  your empty result set indicates that there is redundancy in your model, which has incorrect data.

I believe that it should work as such.
tbl_answers.country = tbl_answers_therapist.country
tbl_answers.state = tbl_answers_therapist.state
Then
tbl_answers.type_id = tbl_therapist_type.type_id
tbl_answers.practice_id = tbl_therapist_type.practice_id
tbl_therapist_type.therapist_id = tbl_answers_therapist.therapist_id

So basically there are five fields being matched.
Country, State, Type, Practice, and Status = Yes

I jusrt ran the above query I got results for client 1 and 3  nothing for 456


There will not always be a match..so it can return nothing

In the picture you posted above with the syntax error there's a comma before the FROM clause, that's why you got syantax error 

Ferruccio you are right about that. Removed  Still not getting the results.  Only on client 1 or 3 not on 456

This is greatly stripped down and works with hard code.  Missing is the TYPE and PRACTICE

SELECT therapist_id, therapist_name, photo, details FROM tbl_answers_therapist
    JOIN tbl_answers USING(country,state)
    WHERE tbl_answers.client_id = 456 AND tbl_answers_therapist.status = 'Yes'
    LIMIT 3

Select allOpen in new window

Then went a step further...broken.

SELECT therapist_id, therapist_name, photo, details FROM tbl_answers_therapist
JOIN tbl_answers USING(country,state)
WHERE tbl_answers.client_id = 456
AND tbl_answers_therapist.status = 'Yes'
AND tbl_answers.type_id = tbl_therapist_type.type_id
AND tbl_answers.practice_id = tbl_therapist_type.practice_id
AND tbl_therapist_type.therapist_id = tbl_answers_therapist.therapist_id
LIMIT 3

Select allOpen in new window

This I think works...

Now you've introduced a new table (threrapist_practice)...

Maybe you should explain or review your data model.

tbl_answers contains the clients answers
tbl_answers_therapist contains the therapist answers
I am matching country and state on these two tables
on tbl_answers there are also the fields TYPE and PRACTICE  these matches come from
tbl_therapist_type and tbl_therapist_practice.  
I hope this somewhat clarifies the situation.
Any comments on the validity of he query I posted above?

Imho it is a data/model issue. But this is hard to tell.. on the other hand, it's pretty simple:

If you think the model is correct and thus your JOIN's are, then the data is incorrect. This means that there is redundancy in the model or a way to enter incorrect data by missing constraints.

If you think the data is correct, then your JOIN's are not. This indicates that you need understand the model better to get them right.

Hi. I believe the modal is correct.  In a nut shell the Therapist is being selected on 5 fields.
Country     -->tbl_answers and tbl_answers_therapist
State         -->tbl_answers and tbl_answers_therapist
Type         -->tbl_answers and tbl_therapist_type
Practice    -->tbl_answers and tbl_therapist_practice
Status       -->tbl_answers and tbl_answers_therapist
These are the tables that they are located in.  Country, State, and Status are no brainers.  The issue is grabbing the TYPE and PRACTICE fields in different tables to match up with tbl_answers once the Vountry, State, and Status are filtered out. 

As

Countries -1:m-> States

Thus

States  -1:m-> tbl_answers and tbl_answers_therapist

Conclusion

Country     --> tbl_answers and tbl_answers_therapist

is incorrect.

tbl_answers
tbl_answers_therapist

Yup. Under mostly any cicumstances, country, state, and therapis_name do not belong to those tables.

2NF and 3NF are violated.

Right.  I get that.  I did this to keep it simple.  The data is not accessed after the initial sign-up.  These tables are used for one purpose and one purpose only. So back to my query.  Do you believe that it's okay?  Or do I need to do something different?

 SELECT client_id
FROM tbl_answers a
JOIN tbl_therapist_practice p USING(practice_id);
SELECT client_id
FROM tbl_answers a
JOIN tbl_therapist_type t USING(type_id);
SELECT * FROM tbl_answers_therapist
JOIN tbl_answers USING(country,state)
WHERE tbl_answers.client_id = 3
LIMIT 3;

Select allOpen in new window

Closing in...This works when hard coded.

But not like this...
SELECT * FROM tbl_answers_therapist
WHERE
tbl_answers_therapist.country = tbl_answers.country
AND
tbl_answers_therapist.state = tbl_answers.state;
UNION
SELECT tbl_therapist_type.type_id ,tbl_therapist_type.practice_id
FROM tbl_therapist_type
WHERE
tbl_therapist_type.type_id = tbl_answers.type_id
AND
tbl_therapist_type.practice_id = tbl_answers.practice_id
LIMIT 3;

So now I basically need to switch the hard coded values with the fields from tbl_answers
tbl_answers.country
tbl_answers.state
tbl_answers.type_id
tbl_answers.practice_id

Even closer...

ooops forgot the client_id........:(

SELECT * FROM tbl_answers_therapist
WHERE
tbl_answers_therapist.country = 'United States'
AND
tbl_answers_therapist.state = 'Alabama'
AND
tbl_answers_therapist.status = 'Yes'
LIMIT 3;
UNION
SELECT * FROM tbl_therapist_type
WHERE
tbl_therapist_type.type_id = 1
AND
tbl_therapist_type.practice_id = 22;
UNION
SELECT * FROM tbl_answers
WHERE
tbl_answers.client_id = 456;
Still works..

But...if the wrong state is put in for the client_id I still get a return if there is a record with that state in it...

Select allOpen in new window

Hey David,

Looks like a tricky one :)

Just a couple of observations. Those last few screen shots are not doing what you think they are. Each of those queries is actually just 3 separate, non-related queries. You'll notice you have a couple of semi-colons in there, so when you run that in your client, all you're actually doing is running the first query (and throwing away the rest!), which is just this:

SELECT * FROM tbl_answers_therapist
WHERE tbl_answers_therapist.country = 'United States'
AND tbl_answers_therapist.state = 'Alabama'
AND tbl_answers_therapist.status = 'Yes'
LIMIT 3

Select allOpen in new window

All you're doing here is pulling in records from Alabama, US with a Status of 'Yes' - that's it ... nothing more !! No JOINs, no UNIONs ! And besides .... a UNION will only work if 2 or more queries return the same number of columns of the same datatype, so I don't think it's suitable in your case.

When joining tables, you can either use explicit JOINs using 'JOIN table1 ON table1.col1 = table2.col1' or you can use implicit joins, using WHERE - where table1.col1= table2.col1. For clarity, I would suggest you use explicit JOINs. This keeps it clear what is used to JOIN data and what is used to filter data (WHERE).

I think I get that you want to match clients (tbl_answers) to therapists (tbl_therapist_answer), based on a matching Country and State (so if a specific Client is in New York, US, you only want to match to Therapists in New York, US).

I don't really get the Type and Practice bit. Throughout this conversation, you've bounced between 2 tables -  tbl_therapist_type and tbl_therapist_practice - which is it,  and how and what do they relate to (clients or therapists)

It's not entirely clear from the conversation what you want, and I think that's playing into the confusion about how to achieve it. It might be worth taking a step back and explaining in very simple, plain English what your goal is, including what data the tables hold and how they relate to one another.

Something like this will select the therapists based on Country and State

SELECT `therapists`.therapist_id, `therapists`.therapist_name, `therapists`.photo, `therapists`.details
FROM tbl_answers `clients`
JOIN tbl_answers_therapist `therapists` ON `therapists`.country = `clients`.country AND `therapists`.state = `clients`.state 
WHERE `clients`.client_id = 1
AND `therapists`.status = 'Yes'

Select allOpen in new window

Now it's just a case of figuring out how the practice / type comes into play.

Good morning Chris.  Thank you.  Yes Country, State and Status are straight forward...the trick is a further filter on the third table tbl_therapist_type using the therapist_id we got from the first query and matching the TYPE and PRACTICE to the tbl_answers table.

In simple english once we have the country, state and status match we would then filter on the tbl_therapist_type table only selecting at most three therapist that match the type and the practice.  Does this make sense?  The type is if you remember the questionnaire (Individual, Couples, Group.)  Practices are (Addiction ADHD, etc)

Hi Chris..getting this.

Had to rename a couple of fields...

I believe it's working!  I've run test on three different ids and got the correct answer on all three,

Excellent news David. Figured I'd have a few typos in my code ... keeps you on your toes though :)

Hopefully you can see the logic in what we've done. Basically, we broke the process down into 2 parts - firstly we got the therapists with all the practice and type info. This effectively gave you a temporary dataset with all the right columns. We then just joined that 'temporary dataset' with the client info. We could join on the 4 lmiting columns because both datasets have those matching columns.

It may not be the most elegant solution, but I do think your DB Schema makes it a little tricky. I would say that it could be improved on somewhat. I know 2NF and 3NF were mentioned earlier. You may be too far into the development of this app to consider a refactor, but moving forward and onto your next project ... getting your schema right at the beginning can save you hours of headaches later down the line. Something to bear in mind :)

Thank you Chris.  I appreciate the advice....it was tricky.  And this set of data is fairly isolated from the rest of the program and only used to find the initial therapist and remember we allotted  for the possibility of no therapist found earlier so I am good to go.  Once again Thank you!

No worries :)