Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: Determining unique children

    Hi,

    I need to be able to identify if a parent has the same child names as another parent.

    Have a look at my data (in pic)

    Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?

    So you would get something like:

    Bob | 2
    Bill | 1
    Glen | 2

    thanks,
    david.
    Attached Thumbnails Attached Thumbnails pic.JPG  
    Last edited by dstachon; 11-17-05 at 18:59.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That's some interesting homework!

    Code:
    CREATE TABLE #patp (
       parent		VARCHAR(20)
    ,  child		VARCHAR(20)
       )
    
    INSERT INTO #patp (parent, child)
       SELECT 'Bob', 'Jen' UNION
       SELECT 'Bob', 'Jill' UNION
       SELECT 'Bob', 'Mike' UNION
       SELECT 'Bill', 'David' UNION
       SELECT 'Bill', 'Steve' UNION
       SELECT 'Glen', 'Jen' UNION
       SELECT 'Glen', 'Jill' UNION
       SELECT 'Glen', 'Mike'
    
    SELECT DISTINCT a.parent
    ,  (SELECT Count(*)
          FROM (SELECT b.parent AS p1, c.parent AS p2
             FROM #patp AS b
             FULL JOIN #patp AS c
                ON (c.child = b.child)
             WHERE c.parent = a.parent
             GROUP BY b.parent, c.parent
             HAVING Count(*) = Count(b.parent)
                AND Count(*) = Count(c.parent)) AS z)
       FROM #patp AS a
    
    DROP TABLE #patp
    -PatP
    Last edited by Pat Phelan; 11-17-05 at 20:16.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    OK now, David....tell us why that works,now.

    and show your work!!!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    C'mon Pat. This is not the first time this guy has posted his homework for someone else to do. Reviewing his code for errors is one thing, but writing code for him is helping him cheat.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    let's hope he turns it in using the #patp table name, at least

    ...and not even a "thank you, you can have half of my class credits"
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    but writing code for him is helping him cheat.
    Only if he's incredibly gutsy and not very bright. I think he's smarter than that.

    Quote Originally Posted by TallCowboy0614
    ...and not even a "thank you, you can have half of my class credits"
    Why only half? If he retypes it for presentation, I'd give him 20%, but that's about all.

    As TallCowboy0614 pointed out earlier, the real fun will come when David gets to explain that code for the class!

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    As TallCowboy0614 pointed out earlier, the real fun will come when David gets to explain that code for the class!
    I'd be hard pressed.....

    working on it
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Lol

    thanks for the code Pat!

    I'm not a student, and that's not a homework assignment. (Been a while since then). I'm actually going to be using it (obviously re-worked to my own data) as an intergrity check for data Im pulling from a .xls file manually entered by a business user.

    I was going to end up creating a function, but I knew someone here would have the guru-ishness to do it with a straight SELECT...obviously advantageous.

    For simplicity I provided an example that I suppose looked like a homework assignment...LOL...the real scenario just would have been really annoying and difficult to explain. (and would have been one of those posts that people skip over)

    as for explaining the code....cripes....i thought my SQL was pretty good. Lots more to learn. Although, I will be able to re-use what you provided Pat.

    cheers.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

Posting Permissions

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