We help IT Professionals succeed at work.
Troubleshooting Question

Combining two queries

David Schure
David Schure asked
on
56 Views
Last Modified: 2020-10-22
I have two queries.  Separately they both work.  However, I am looking to either combine them or link them together to get the final result.  Can this be done?
 SELECT therapist_id,therapist_name, photo,details FROM tbl_answers_therapist
   JOIN tbl_answers USING(country,state)
   WHERE tbl_answers.client_id = 316 AND tbl_answers_therapist.status = 'Yes'
   LIMIT 3;

SELECT therapist_id FROM tbl_therapist_type
   JOIN tbl_answers USING(practice_id,type_id)
   WHERE tbl_answers.client_id = 316
   LIMIT 3;
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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?

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
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
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.

Author

Commented:
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;
CERTIFIED EXPERT

Commented:
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 )


Author

Commented:
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;
without say...does not work...
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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'
Not sure what you mean by "Just add the third table"
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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;

Open 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.

Author

Commented:
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

Author

Commented:
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



ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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'
This looks like it should work...but does not.

Author

Commented:
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. 
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
But it still does not work...Still getting errors.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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...

Author

Commented:
Okay Hain.  Please give me a minute on this.

Author

Commented:
Here you go..

CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

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

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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?

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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>");
getting two therapist back...
https://arise.plus/MODALHELPER.html

Author

Commented:
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

HainKurtSr. System Analyst
CERTIFIED EXPERT

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

Author

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


Author

Commented:
result 2


HainKurtSr. System Analyst
CERTIFIED EXPERT

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

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

Author

Commented:
Thank you everyone.  Another tough one for me!

Author

Commented:
Thank you Hain I will.