多个连接和条件全部在一起

Question

I've been pulling my hairs for hours that finally I've come to SO to post this question. I read many questions, but it seems that nothing right is happening.

Table 1

a1
a2
a3 <-- linked with b3
a4

Table 2

b1
b2
b3  <-- linked with c3

Table 3

c1
c2
c3  <-- linked with d3

Table 4

d1
d2
d3 
d4

Columns to display with conditions:-

Column: a1 Condition: BETWEEN '2013-9-13' AND '2013-9-14'

Column: a2 Condition: a2 == '2'

Column: a3 Condition: only those results where d3's value is either 1, 2, 3 or 4

I've a PHP string like this "1,2,3,4". d3 should be selected if it is any of the coma separated value. Please also tell how can I use the PHP string for this purpose. In Oracle I used to use 'IN' operator like

'1' IN ('1','2','3')

and it used to solve the problem. But in Mysql I'm pretty confused.

Only those rows should be displayed where all conditions are met.

Thanks

(Note: The question requires a complete SQL query.)

Answer 1

I would start with this:

SELECT
    *
FROM
    Table1 AS T1
    INNER JOIN Table2 AS T2 ON T1.A3 = T2.B3
    INNER JOIN Table3 AS T3 ON T2.B3 = T3.C3
    INNER JOIN Table4 AS T4 ON T3.C3 = T4.D3
WHERE
        T1.A1 BETWEEN '2013-09-13' AND '2013-09-14'
    AND T1.A2 = '2'
    AND T4.D3 IN (1, 2, 3, 4)