Combining two queries

Can it be done? Sure.. but I would expect the result of the second query to contain the same therapist_id as the first query. Cause otherwise the data  model needs clarification, cause it smells like redundancy.

So can you explain, what you're trying to do here?

I am trying to get the therapist_id (limit 3) based on the three tables.
tbl_answers is the constant.  The information it is comparing to is in tbl_answers_therapist and tbl_therapist_type.
The five fields that I am looking for a match on are
tbl_answers_therapist.status = yes
tbl_answers.country = tbl_answers_therapist.country
tbl_answers.state = tbl_answers_therapist.state
tbl_answers.practice_id = tbl_therapist_type.practice_id
tbl_answers.type_id = tbl_therapist_type.type_id
based on client_id which will be a variable but using 316 for now.

if i understand your needs, you need those fields to be populated when the entry exists and you still want the ids when they do not.... that would call for a left join.

your limit clause will apply to the whole query, so if you want the first 3 rows for each therapist, you need to play with subqueries

So far I understand it. But the problem arises from the semantics and the relationships as I understand them:

tbl_answers -(by location)-> tbl_answers_therapist
tbl_answers -(kind of)-> tbl_therapist_type

The imply that there must be a relationship from

tbl_answers -> tbl_therapist

otherwise the model stores redundant data, which can lead to incorrect information when not kept in sync.

I am basically trying to filter down here.in the first query I want only the therapist_id from tbl_answers_therapist that match country and state with the country and state in the tbl_answers table.  Then once I have those therapist_id's I want to further filter down based on type_id and practice_id from the tbl_therapist_type.  Can I do this with one query?  If so how.  Or do I need to two queries?  Thank you. I appreciate any helphere.

Something like this?  (I know it's off)

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

Select allOpen in new window

Can I do this with one query?  If so how

exactly as you did above, but with all 3 where clauses.

you also need to add the join clauses ( the way you use USING seems weird and it is quite useless )
i would expect something like
AND tbl_therapist_type.id_therapist = tbl_answers_therapist.id for each join ( usiing whatever field is the join key )

Do you mean something like this??

SELECT therapist_id,therapist_name, photo,details FROM tbl_answers_therapist
JOIN tbl_answers AND tbl_therapist_type.therapist_id = tbl_answers_therapist.therapist_id
WHERE tbl_answers.client_id = 316 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
LIMIT 3;

Select allOpen in new window

without say...does not work...

yeah, but close enough : something like this

SELECT therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
WHERE tbl_answers.client_id = 316
AND tbl_answers_therapist.status = 'Yes'
AND tbl_answers.type_id = tbl_therapist_type.type_
AND tbl_answers.practice_id = tbl_therapist_type.practice_id
AND tbl_therapist_type.therapist_id = tbl_answers_therapist.therapist_id

once you get it, just add the 3rd table

Getting an error on this..

[SQL] SELECT therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
WHERE tbl_answers.client_id = 316
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

[Err] 1054 - Unknown column 'tbl_therapist_type.type_id' in 'where clause'

Select allOpen in new window

Not sure what you mean by "Just add the third table"

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

Select allOpen in new window

This is exactly the problem:
If your data model is correct, then it is not necssary. Otherwise you need to explain it. Table DDL and sample data!

btw, use the JOIN..ON syntax, it's more common. Also use table alias names consequently, right now it's not clear which tables which columns contain.

Tried this...no go.

SELECT therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
WHERE tbl_answers.client_id = 316
AND tbl_answers_therapist.status = 'Yes'
JOIN tbl_therapist_type
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

Select allOpen in new window

This is the table tbl_answers
client_id
country
state
practice_id
type_id

This is the table tbl_answers_therapist
therapist_id
country
state
practice_id
type_id
therapist_name
photo
status

This is the table tbl_therapist_type
therapist_id
practice_id
type_id

Please explain the use of tbl_therapist_type. Cause its three columns are also in  tbl_answers_therapist.

That is the point I'm trying to adress. I don't understand it.

Your right. Brought something to light.  I only need it in tbl_therapist_type...neither type_id and practice_id are not needed in the tbl_answers_therapist.  So with that in mind....

SELECT therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
WHERE tbl_answers.client_id = 316
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

[Err] 1054 - Unknown column 'tbl_therapist_type.type_id' in 'where clause'

Select allOpen in new window

This looks like it should work...but does not.

Corrected
This is the table tbl_answers
client_id
country
state
practice_id
type_id

This is the table tbl_answers_therapist
therapist_id
country
state
therapist_name
photo
status

This is the table tbl_therapist_type
therapist_id
practice_id
type_id

The reason for the tbl_therapist_type is that the therapist can have many types and practices wher as the client only has one type and one practice. 

the unknown column issue is because you did not join the 3rd table.

other issues arise because all where clauses including the ones pertaining ti the joins need to be AFTER the where keyword which needs to be after the table declarations including joins

LIke this? (doesn't work)

SELECT therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers AND tbl_therapist_type
WHERE tbl_answers.client_id = 316
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

Select allOpen in new window

no : one join clause per secondary table rather than AND but otherwise ok. juste replace the first and keyword with join and you re good

I'm sorry. I don't quite understand.

SELECT therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
JOIN tbl_therapist_type
WHERE tbl_answers.client_id = 316
JOIN tbl_answers_therapist.status = 'Yes'
JOIN tbl_answers.type_id = tbl_therapist_type.type_id
JOIN tbl_answers.practice_id = tbl_therapist_type.practice_id
JOIN tbl_therapist_type.therapist_id = tbl_answers_therapist.therapist_id

Select allOpen in new window

And this...

SELECT therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
JOIN tbl_therapist_type
WHERE tbl_answers.client_id = 316
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

[Err] 1052 - Column 'therapist_id' in field list is ambiguous

Select allOpen in new window

pretty much yes. at least the structure of the query is good. the where clauses order does not matter but it is common practuce to stuck whatever joins the tables first.

But it still does not work...Still getting errors.

too much comment here...

please, create an excel file
put your table and columns and add some sample data
and show in another table what you want...
o/w we are just wasting time...

Okay Hain.  Please give me a minute on this.

Here you go..

yes it works......!

SELECT tbl_answers_therapist.therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
JOIN tbl_therapist_type
WHERE tbl_answers.client_id = 316
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

Select allOpen in new window

now you got the general idea, time to check that query returns the expected results... tell us how we can help further. sorry, i am on a mobile phone so pasting queries is a pain

Yeah....that took a minute. And yes it returned the expected results!

ok, a good start...
now, when I look at table names, data, I dont get logic behind these database structure...
for example, what is type id in table tbl_answers and tbl_therapist_id
why we have country/state in two tables
what is relation between therapist and practice

who created these tables?
is it part of another 3rd part application?

Hello Hain,
It goes like this.  The Therapist answers a set of questions that is stored in tbl_answers_therapist.  They also answer a set of questions that are stored in tbl_therapist_type.  The client answers a set of questions that are stored in tbl_answers.  I am comparing tbl_answers to tbl_answers_therapist  and tbl_answers to tbl_therapist_type.  I need the table therapist_type because there are many answers on the therapist side and only on answer on the client side.

somehow agreed the structure is a little weird. but not assuming something is wrong yet. the repetition might pertain to therapist vs customer. feels like the schema might bge correft but the field names are weird. not really the issue at hand anyways

so this works...here.

but not here....

 $db = new mySQLi($dbHost, $dbUsername, $dbPassword, $dbName); 
    $sql = <<< EOT
    SELECT tbl_answers_therapist.therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
JOIN tbl_therapist_type
WHERE tbl_answers.client_id = 316
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   
EOT;
   
$query = $db->query($sql);
printf("<div class='container'>");
$r=0;
while ($data = $query->fetch_assoc()) {
    $r++;
    if (($r % 3)==1) {
    printf("<div class='row'>");
    }; 
    printf("<div class='col-sm bioData'>");
    printf("<img class='bioImg' src='/resources/images/Therapist/%s'>", $data['photo']);
    printf("<p class='bioName'>%s</p>", $data['therapist_name']);
    printf("<p>%s</p>", $data['details']);
   printf("<button class='btnT' onclick='selBio(%s)'>Select</button>", $data['therapist_id'] );
    printf("</div>");

    if (($r % 3)==0) {
      printf("</div>");
    } 
}
if (($r % 3)!=0) {
  printf("</div>");
};  
printf("</div>");

Select allOpen in new window

getting two therapist back...
https://arise.plus/MODALHELPER.html

getting an error now..

SELECT tbl_answers_therapist.therapist_id,therapist_name, photo,details
FROM tbl_answers_therapist
JOIN tbl_answers
JOIN tbl_therapist_type
WHERE tbl_answers.client_id = 316
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;
[Err] 1146 - Table 'HMS2020.tbl_answers_therapist' doesn't exist

Select allOpen in new window

are you sure you have 2 db and web and you are connecting to same database, using same username/password?

Posiitive....Same website same db..  result 1

result 2

by the way, you should use only width property on image class

.bioImg {
    border: 1px dotted silver;
    /* height: 200px; */ >>> delete this
    width: 150px;
}

Select allOpen in new window

Thank you everyone.  Another tough one for me!

Thank you Hain I will.