Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2014
    Posts
    5

    Unanswered: Join a table with different tables but with a field in common

    Hi, nice to meet you. I am new here.
    I have this scenario. I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity.
    An opportunity and a contact could be also related with an account.
    My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
    This is my current query:

    SELECT AC.IdActividad, CU.idcuenta
    FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad
    LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta
    LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta
    LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta

    As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.

    Any suggestions ?

    Thanks in advance

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    I do not know if it will be the best option in terms of performance but try:

    Code:
    SELECT AC.IdActividad, CU.idcuenta 
    FROM Actividades AC 
    LEFT JOIN Tareas TA 
        ON TA.IdActividad = AC.IdActividad 
    
    LEFT JOIN ActividadesXCuenta AXC 
        ON AC.IdActividad = AXC.IdActividad 
    
    LEFT JOIN dbo.ActividadesXOportunidad axo 
        ON AC.IdActividad = axo.IdActividad 
    LEFT JOIN dbo.Oportunidades o 
        ON axo.IdOportunidad = o.IdOportunidad 
    
    LEFT JOIN dbo.ActividadesXContacto axco 
        ON AC.IdActividad = axco.IdActividad 
    LEFT JOIN dbo.Contactos con 
        ON axco.IdContacto = con.IdContacto 
    
    LEFT JOIN dbo.Cuentas CU 
        ON CU.IdCuenta = coalesce(axc.IdCuenta, o.IdCuenta, con.IdCuenta)
    Hope this helps.

  3. #3
    Join Date
    Jan 2014
    Posts
    5
    Thanks Imex for your quick reply.
    The proposed solution seems to work but the problem that I have is that an activity is not always related with an account but this query is always returning a value for accountID and it should retrieve NULL in some cases. I dont know why

    Any helps?

  4. #4
    Join Date
    Jan 2014
    Posts
    5
    Sorry! I found an error in my query, that's why I never see the NULL value .
    Thanks IMEX, your solution is what I need (I will check then if is OK in performance point of view)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •