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.
Inner Join is the method of retrieval of data from multiple tables based on a required condition and necessary conditions are that there must be common columns or matched columns between the two tables of the database and the data types of columns must be the same.
The most common way of joining three tables goes something like this: SELECT * FROM Table1 INNER JOIN Table2 ON Condition INNER JOIN Table3 ON Condition; This uses an inner join, but you can specify your desired join type as with any other join. You can also combine join types if required (example below).
In this case the two tables are joined using the relationship table1.id = table2.id . It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With