与多个表的多个内联接

发布于 2024-11-30 23:19:59 字数 928 浏览 3 评论 0原文

所以我有四张桌子。每个表都有一个与前一个表 id 相同的 id。所以我的点击表有一个 id 和一个来自它的广告的 id。在广告表中，它有一个广告 ID 和一个广告活动 ID。这是一个例子。

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

因此，为了找出表 4 中的值来自何处，我需要回溯每个表并检查它们具有哪个 id。基本上，我想知道表 1 中的哪些值与表 4 中的值相关联。

表 4 是网站的访问者，表 1 是互联网广告。我想知道哪些访问者来自哪些广告。不幸的是，数据的设置使得我只能从访问者到源到广告组再到广告返回单个步骤。这有道理吗？

不管怎样，我想知道使用 4 个内部连接是否是解决这个问题的最佳策略，或者是否有一些我不知道的更简单的 mysql 解决方案。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晨与橙与城 2024-12-07 23:19:59

内部联接可能是最好的方法，您只需要 3 个。

这将为您提供一个包含两列的结果集：公司和关联值。

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

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

回复收藏 0 原文

~没有更多了~