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

Question

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    [e-mail]
    3       multaps2.com         9    Bernard     [e-mail]
    4       multaps3.com         3    Bree       [e-mail]
    5       explts.com       1    exploi   [e-mail]
    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| [e-mail]  |
---------------------------------------------------------------------------
|  2   | Premium master "multaps2.com" | multaps2.com| Bernard| nard@Sedn |
---------------------------------------------------------------------------
|  2   | Premium master "multaps3.com" | multaps3.com| Bree   | [e-mail] |
---------------------------------------------------------------------------

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| [e-mail]  |
    ---------------------------------------------------------------------------
    |  2   | Premium master "multaps2.com" | multaps1.com| Bert| [e-mail]|
    ---------------------------------------------------------------------------
    |  2   | Premium master "multaps3.com" | multaps1.com| Bert   | [e-mail]|
    -------------------------------------------------------------------------

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.

Answer 1

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