dongmin3754
dongmin3754
2017-03-28 12:54

从clumon“”中选择结果并加入另一个表

已采纳

Client

ClientID    UserID  CustomerID
1   1   1
2   2   2
3   3   3
4   4   4
5   5   5

client service:

    ClientServiceID   ClientID  ServiceID   Description

    28  10  6           Premium master "multaps1.com"

    48  134 6           Premium master "multaps3.com"

    58  135 6           Premium master "multaps4.com"

    68  137 6           Premium master "multaps5.com"

        366 273 1           Webhosting master "multaps10.com"

HostingAccount:

    HostingAccountID     Domain DomainIP      Username  Email

   2        multaps1.com         7    Bert    Bert@in.com
    3       multaps2.com         9    Bernard     nard.@Sedn.ca
    4       multaps3.com         3    Bree       Aenean@Cum.org
    5       explts.com       1    exploi   ante@cursusluctus.edu
    6       multaps5.com    10    Rama   quis@tempusrisusuk

Service

ServiceID  ServiceType
1     1
2     1
3     1
4     2

I have the folowing tables I need to get results like this

---------------------------------------------------------------------------
|CustomerID| Description               | Domain      | UserName | Email   |
---------------------------------------------------------------------------
|  1   | Premium master "multaps1.com" | multaps1.com| Bert| Bert@in.com  |
---------------------------------------------------------------------------
|  2   | Premium master "multaps2.com" | multaps2.com| Bernard| nard@Sedn |
---------------------------------------------------------------------------
|  2   | Premium master "multaps3.com" | multaps3.com| Bree   | Aan@C.org |
---------------------------------------------------------------------------

I am trying like this:

SELECT DISTINCT C.CustomerID,
                               CS.Description,
                               HA.Email,
                               HA.Domain,
                               HA.UserNam
                           FROM ClientService AS CS
                           INNER JOIN Client AS C ON C.ClientID=CS.ClientID
                           LEFT JOIN Service As S
                           ON CS.ServiceID = S.ServiceID
                           LEFT JOIN HostingAccount AS HA
                           On HA.Domain = ANY (SELECT DISTINCT SUBSTR(Description, LOCATE( '"', Description )+1,
                               ( CHAR_LENGTH( Description) - LOCATE('"',REVERSE(`Description`) ) -
                               LOCATE('"',Description)))
                                   from ClientService
                                   )
                                   WHERE S.ServiceType IN (1, 2, 3)
                           ORDER BY HA.HostingAccountID DESC

I keep getting multiple time same Domain, username, email and it matches with other rows like this:

---------------------------------------------------------------------------
    |CustomerID| Description               | Domain      | UserName | Email   |
    ---------------------------------------------------------------------------
    |  1   | Premium master "multaps1.com" | multaps1.com| Bert| Bert@in.com  |
    ---------------------------------------------------------------------------
    |  2   | Premium master "multaps2.com" | multaps1.com| Bert| Bert@in.com|
    ---------------------------------------------------------------------------
    |  2   | Premium master "multaps3.com" | multaps1.com| Bert   | Bert@in.com|
    -------------------------------------------------------------------------

It does this also for other rows to. the problem I need to get description between "" and then get same result as domain. that is the only way they are related and I cant change tables indexes. I don't know how to explain any help will be appreciated.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongqie2355 dongqie2355 4年前

    Your JOIN condition seems overcomplicated. Try this instead:

    LEFT JOIN HostingAccount AS HA ON  CS.Description LIKE CONCAT('%', HA.Domain, '%') 
    

    This will return a match as soon as Domain is contained within the Description.

    Demo here

    点赞 评论 复制链接分享