Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Unanswered: SQL Join - One-to-many

    I have a Leads table and ActionsTracking table, of which I'm trying pull off info. I'm doing a join on both tables to pull some lead info off with the Actionstracking info (which is just a date, leadID and actionID). I am running into problems when I try to show all the records in the ActionsTracking table if the actionID = 1 AND all the records where actionID = 2, else don't show any records. I was thinking there should be some kind of join into itself, but that puts them all into the same record. What I've come up with so far is this:

    Select distinct Leads.firstName, Leads.lastName, Leads.email,
    AT1.dateAdded, AT1. actionID, AT2.actionID, AT2.dateAdded
    FROM Leads
    JOIN ActionsTracking AT1
    On (AT1.leadID = Leads.leadID)
    JOIN ActionsTracking AT2
    On (AT2.leadID = Leads.leadID)
    where AT2.actionID = 3
    and AT1.actionID = 1

    Obviously this return it one record, and what I'm trying to do is if both conditons are true, show the name, email, and ActionsTracking info for every record. If both are not satisfied no records show.

    Thanks in advance, I've been banging my head for a while on this one.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think I see what you are trying to do. There may be a more efficient method than this, but this solution involved minimal changes to your existing code.

    Use the two existing links as filters (I moved criteria from the WHERE clause to the JOIN, but in this instance it does not make a difference), and then link the table in a third time to get all the detailed data that you need.

    Select distinct
    Leads.firstName,
    Leads.lastName,
    Leads.email,
    ActionsTracking.*
    FROM Leads
    INNER JOIN ActionsTracking AT1 On AT1.leadID = Leads.leadID and AT1.actionID = 1
    INNER JOIN ActionsTracking AT2 On AT2.leadID = Leads.leadID and AT2.actionID = 3
    INNER JOIN ActionsTracking on Leads.leadID = ActionsTracking.leadID

    The idea is to separate the filtering functionality of the ActionsTracking data from the content functionality of the ActionsTracking data.

    blindman

  3. #3
    Join Date
    Dec 2003
    Posts
    5
    Good idea, and you do get what I'm doing. The problem is, when I run that query, I still run into the same problem - it returns all the records, including those with an actionID of 2. It also returns duplicates of all records it returns. The filtering is the right idea though, because if you put in an actionID of 4, no records are returned - exactly as it should. Any ideas on why it's still returning those records?

    Thanks for your help.
    Last edited by cujomama; 12-30-03 at 18:51.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It returns all records that have tracking records with IDs of both 1 and 3. It doesn't care whether or not there is also a record with and ID of 2. I was a little confused that you mentioned 1 and 2 in your narrative and then used 1 and 3 in your sample code.

    It can't be returning duplicate records if you use the DISTINCT keyword. It's impossible.

    Can you give an example of the output you are looking for?

    blindman

  5. #5
    Join Date
    Oct 2003
    Location
    Manchester UK
    Posts
    73
    Originally posted by blindman
    It returns all records that have tracking records with IDs of both 1 and 3. It doesn't care whether or not there is also a record with and ID of 2. I was a little confused that you mentioned 1 and 2 in your narrative and then used 1 and 3 in your sample code.

    It can't be returning duplicate records if you use the DISTINCT keyword. It's impossible.

    Can you give an example of the output you are looking for?

    blindman
    Perhaps I am being a bit dim here but would all this not just be a lot simpler with a Where clause that looked something like

    WHERE actionID IN (1,2)

    Which would do your select correctly. How you then join in orer to get the column refs is then pretty straight forward.
    I haven't lost my mind, there's a backup on one of these floppies, somewhere.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good one, steve, you're on the right track

    however, the join is not quite so straightforward, if instead of 2 conditions there were seven

    so instead of a multi-table join, do the condition checking in a simple subquery

    here's the model that will work for any number of conditions:
    Code:
    select Leads.firstName
         , Leads.lastName
         , Leads.email
         , AT.dateAdded
         , AT.actionID
      from Leads 
    inner
      join ActionsTracking AT 
        on Leads.leadID = AT.leadID
     where Leads.leadID
        in ( select leadID
               from ActionsTracking 
              where actionID in (1,3)
             group
                 by leadID
             having count(*)=2
           )
    rudy
    http://r937.com/

  7. #7
    Join Date
    Oct 2003
    Location
    Manchester UK
    Posts
    73
    Fair point about the increasing complexity of the joins...

    I notice a real tendency towards avoiding nested SQL. I'm guessing that's because its not supported in Enterprise Manager.

    regards

    Steve
    I haven't lost my mind, there's a backup on one of these floppies, somewhere.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When I said "There may be a more efficient method", Rudy's solution was the one I was too lazy at the moment to find. Personally I'd but the subquery in a join rather than the where clause, but six of one, a half dozen of the other...

    blindman

  9. #9
    Join Date
    Dec 2003
    Posts
    5
    Wow, you guys are good....that works exactly as it should.

    Thanks again, I'd been struggling with that one for a while.

  10. #10
    Join Date
    Dec 2003
    Posts
    5
    Actually there is one more thing.....

    Sorry I was unclear blindman. When I mentioned actionID = 2, I meant to say that it was returning that actionID also. When I mentioned actionID = 4, that was actionID that does not exist, and thus no records should be returned....the actionID's only go from 1-3.

    When I use Steve's suggestion, it works great in returning all records that are 1 and 3, but does not do the exclusion I was searching for - if there is only one record of 3, it returns that record - should return no records.

    Rudy, your query makes perfect sense when I read it - but when I run it, it returns no records at all. If I change the subquery "having" to "(1,2)", all records are returned that satisfy those conditions, but also those that have an actionID of 3......any ideas?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, as you can see, the subquery ensures that both action conditions are present

    then the outer query returns all actions for any leads that have met both conditions

    the original spec: "what I'm trying to do is if both conditons are true, show the name, email, and ActionsTracking info for every record." (my emphasis on added)

    so if you only want the selected actions, add a WHERE condition to the outer clause as well

    rudy
    Last edited by r937; 12-31-03 at 13:05.

  12. #12
    Join Date
    Dec 2003
    Posts
    5
    You're right Rudy.....I did say that.

    I added the outside where clause and it works beautifully.

    I like a nested subquery as a filter - I need to use that more often.

    Thanks again.

Posting Permissions

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