CODEWITHSUNDEEP
sql
BeginnersMindTruly
BeginnersMindTruly

Reputation: 731

Inner join with multiple conditions across multiple tables (SQL)

Looking for advice regarding combining multiple SQL queries into a multi-part inner join statement.

I have the following three tables (Table: Columns):

I have the following atomic SQL queries:

select pid, visit_date, visit_start_datetime, visit_end_datetime
from VisitTable
where visit_id = XYZ AND visit_start_datetime > '01/01/2009'

select pid, cond_date, cond_datetime
from CondTable
where cond_id in (ABC, DEF, GHI)

select pid, prod_date, prod_start_datetime, prod_end_datetime
from ProdTable
where prod_id in (123, 456, 789)

Let's call the result of the first query (Temp1), the second query (Temp2), and the third query (Temp3).

I'd like to inner join Temp1 with Temp2 on pid where (Temp1.visit_start_datetime <= Temp2.cond_datetime <= Temp1.visit_end_datetime), i.e. in the case where Temp2.cond_datetime is in range [Temp1.visit_start_datetime, Temp1.visit_end_datetime] for that pid. Let's call the result of this join Temp4.

I would then like to take this result (Temp4) and inner join it with Temp3 on pid where Temp3.prod_start_datetime is in range [Temp1.visit_start_datetime, Temp1.visit_end_datetime].

Questions:

  1. How can I do this using 1 SQL query with multiple inner joins and conditions?

I've tried the following syntax but it's not correct:

select pid, visit_date, visit_start_datetime, visit_end_datetime
from VisitTable
where visit_id = XYZ AND visit_start_datetime > '01/01/2009'
left join
    (select pid, cond_date, cond_datetime
     from CondTable
     where cond_id in (ABC, DEF, GHI)) Temp2 on Temp2.pid = VisitTable.pid
where Temp2.cond_datetime between VisitTable.visit_start_datetime and VisitTable.visit_end_datetime
left join
    (select pid, prod_date, prod_start_datetime, prod_end_datetime
     from ProdTable
     where prod_id in (123, 456, 789)) Temp3 on Temp3.pid = VisitTable.pid
where Temp3.prod_start_datetime between VisitTable.visit_start_datetime and VisitTable.visit_end_datetime

  1. In the first atomic SQL query, I originally tried using a WHERE clause with visit_date but got an error saying "visit_date" is an integer. I then tried using visit_start_datetime and it accepted the condition >= '01/01/2009'. Why does this happen and is there anyway I can use visit_date instead?

  2. After I construct this query, how can I then group by date and count the number of entries that appear for each date?

Thank you!

Upvotes: 0

Views: 454

Answers (1)

Vlam
Vlam

Reputation: 1798

Please try the following SQL, your date is not formatted in a way that can be understood by the SQL queries.

select Temp1.* from
(
select pid, visit_date, visit_start_datetime, visit_end_datetime
from VisitTable
where visit_id = XYZ AND visit_start_datetime > '2009-01-01'
) as Temp1 inner join
(
select pid, cond_date, cond_datetime
from CondTable
where cond_id in (ABC, DEF, GHI)
) as Temp2 on Temp1.pid = Temp2.pid
and Temp1.visit_start_datetime <= Temp2.cond_datetime and Temp2.cond_datetime <= Temp1.visit_end_datetime
inner join
(
select pid, prod_date, prod_start_datetime, prod_end_datetime
from ProdTable
where prod_id in (123, 456, 789)
) as Temp3 on Temp1.pid = Temp3.pid
and Temp1.visit_start_datetime <= Temp3.prod_start_datetime and Temp3.prod_start_datetime <= Temp1.visit_end_datetime

Upvotes: 2

Related Questions