Multiple inner joins with multiple tables

Question

So I have four tables. Each table has a single id for the previous table id. So my in click table has an id and an id for the ad from which it came. In the ad table, it has an id for the ad and one for the campaign it's from. So here's an example.

Table4 -
id   company      table_id
11     hp           20
12     apple        23
13     kohls        26  
14     target       21
15     borders      28

Table3 - 
id    value    table2_id
21     ks          53
22     al          54
23     tx          53 
24     fl          55
25     co          51

Table2 -
id    value    table1_id
51     ks          34
52     al          34
53     tx          33 
54     fl          35
55     co          31

Table1 -
id    value    
31     ks        
32     al          
33     tx          
34     fl          
35     co  

So to find out where the values in Table 4 came from, I need to work back through each table and check which id they have. Basically, I want to know which values in table 1 are associated with the values in table 4.

This of table 4 as visitors to a website and Table 1 as internet ads. I want to know which visitors came from which ads. Unfortunately, the data is set up so that I can only take single steps back from visitor to source to ad group to ad. Does that make sense?

Anyways, I'm wondering if using 4 innner joins was the optimal strategy for this problem or is there some simpler mysql solution that i'm not aware of.

Solution

Inner joins are probably the best method, and you only need 3.

This will give you a result set with two columns: company and associated values.

SELECT Table4.company, table1.id, table1.value
FROM Table1
    INNER JOIN Table2
        ON Table2.table1_id = Table1.id
    INNER JOIN Table3
        ON Table3.table2_id = Table2.id
    INNER JOIN Table4
        ON Table4.table3_id = Table3.id

Comments

[Brad Christie]

Although, given the data I see, I would almost use a LEFT JOIN in case there is no actual match (unless the integrity is guaranteed to have a match in the other table).

[Narnian]

Agreed. It's possible, though, that he may not want any data if there are no matches.

[Swastik Padhi]

@Narnian Shouldn't INNER JOIN Table1 be INNER JOIN Table2?