Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2008
    Posts
    10

    Unanswered: Help with SQl script please

    I’m trying to run a script that will check if there are more than one lCandidateID linked to mutilple lAlternativeID and have this in order .
    So first I'm looking to see if lCandidateID id is in the table more than once and how many different lAlternativeID is link to this id.

    The below script also returns lCandidateID, than are only in the table once.

    So for instances I.E

    lCandidateID 6109 as three lAlternativeID may look something like this

    Canid AlterID AgencID
    6109 3066 90160
    6109 1101982 90160
    6109 1057346 90160

    select lCandidateID, lAlternativeID, lAgencyID from tCandidateAlternativeIDs with (nolock)
    where lCandidateID in (
    select lCandidateID from tCandidateAlternativeIDs with (nolock)
    group by lCandidateID
    having count(lCandidateID) > 1)
    and lAgencyID = 90160
    Order by lCandidateID


    Can you please help?
    SQl 2005
    Thanks
    Last edited by MCSC1821; 06-04-09 at 08:57.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MCSC1821
    Can you please help?
    we'd love to

    what's your question?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2008
    Posts
    10
    Not to worry I managed to do this myself :-) with a little messing around

    declare @agencyid int
    set @agencyid = 90160

    select lCandidateID, lAlternativeID, lAgencyID from tCandidateAlternativeIDs with (nolock)
    where lCandidateID in (
    select lCandidateID
    from tCandidateAlternativeIDs with (nolock)
    where lAgencyID = @agencyid
    group by lCandidateID
    having count(lAlternativeID) > 1)
    and lAgencyID = @agencyid
    order by lCandidateID asc

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looks like it will work to me.
    But dump the nolock crap.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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