Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: Max with Left Join

    Hi,

    I need to extract from a database of an association, the name of the associate and its last paid share, but after a day trying I simply cannot extract that data.
    The table senhas represents the shares, the table associados the associate data and senhas_associados, represents the shares paid by an associate, as it was a many to many relationship.
    To query this, I'm trying to query the share with the maximum id (senha) which is in seassoc_senha_id in senhas_associados which represents a paid share.

    Here's what I've tried so far:
    Firstly, I've tried this statement, the output is the required, but it doesn't show associates who haven't paid any share which is needed (no records in senhas_associados):

    SELECT assoc_nome, senha_desig
    FROM associados, senhas, senhas_associados
    WHERE assoc_id = seassoc_assoc_id
    AND seassoc_senha_id = senha_id
    AND seassoc_senha_id IN (SELECT max(seassoc_senha_id)
    FROM senhas, senhas_associados
    WHERE seassoc_senha_id = senha_id
    AND seassoc_assoc_id = assoc_id)

    the output:
    assoc_nome senha_desig
    Carlos Costa Maio
    Rodrigo Abril

    I've even tried adding this:
    SELECT assoc_nome, senha_desig
    FROM associados, senhas, senhas_associados
    WHERE assoc_id = seassoc_assoc_id
    AND seassoc_senha_id = senha_id
    AND seassoc_senha_id IN (SELECT max(seassoc_senha_id)
    FROM senhas, senhas_associados
    WHERE seassoc_senha_id = senha_id
    AND seassoc_assoc_id = assoc_id) OR senha_desig is null

    To no avail, the output was the same.

    Then, I've changed the "tactic" and went to left join:
    SELECT assoc_nome, senha_desig
    FROM associados
    LEFT JOIN senhas_associados ON associados.assoc_id = senhas_associados.seassoc_assoc_id
    LEFT JOIN senhas ON senhas.senha_id = senhas_associados.seassoc_senha_id

    The output:
    assoc_nome senha_desig
    Carlos Costa Abril
    Carlos Costa Janeiro
    Carlos Costa Fevereiro
    Carlos Costa Maio
    Rodrigo Janeiro
    Rodrigo Fevereiro
    Rodrigo Abril
    Pedro Soares NULL

    Now it shows NULL's, but I can't find where to put the max function.

    So I'm requesting your help, since today's afternoon that I'm stuck with this.

    Thanks everyone,

    Regards.

    I've attached the relational model of the involved tables.
    Attached Thumbnails Attached Thumbnails relational_model.PNG  

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that you want:
    Code:
    SELECT assoc_nome, Max(senha_desig)
       FROM associados
       LEFT JOIN senhas_associados
          ON associados.assoc_id = senhas_associados.seassoc_assoc_id
       LEFT JOIN senhas
          ON senhas.senha_id = senhas_associados.seassoc_senha_id
       GROUP BY assoc_nome
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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