Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: Need help with sub-query

    I am trying to only pull one row per case_id but I need the same field twice. There is a party table and an identity table. The party table tells me type, DFDNT or SPO, the identity table tells me name. I need the last_name for both the DFDNT and the SPO. Here is my code so far which works on a single case.

    Code:
    SELECT cases.case_id
    , cases.dscr
    , cases.file_dt
    , cases.ticket_nbr
    
    , jdg.jdg_abr
    
    , dbo.casepbsc.spcond_cd
    , dbo.casepbsc.entry_dt
    
    , (SELECT idnt.last_name
    	FROM dbo.idnt
    	INNER JOIN dbo.pty ON idnt.idnt_id = pty.idnt_id
    	WHERE pty.case_id =cases.case_id
    	AND pty.pty_cd = 'DFNDT')
    
    , (SELECT idnt.first_name
    	FROM dbo.idnt
    	INNER JOIN dbo.pty ON idnt.idnt_id = pty.idnt_id
    	WHERE pty.case_id =cases.case_id
    	AND pty.pty_cd = 'DFNDT')
    
    , (SELECT idnt.middle_name
    	FROM dbo.idnt
    	INNER JOIN dbo.pty ON idnt.idnt_id = pty.idnt_id
    	WHERE pty.case_id =cases.case_id
    	AND pty.pty_cd = 'DFNDT')
    
    , (SELECT idnt.dod
    	FROM dbo.idnt
    	INNER JOIN dbo.pty ON idnt.idnt_id = pty.idnt_id
    	WHERE pty.case_id =cases.case_id
    	AND pty.pty_cd = 'DFNDT')
    
    , (SELECT idnt.idnt_id
    	FROM dbo.idnt
    	INNER JOIN dbo.pty ON idnt.idnt_id = pty.idnt_id
    	WHERE pty.case_id =cases.case_id
    	AND pty.pty_cd = 'DFNDT')
    
    , (SELECT idnt.last_name
    	FROM dbo.idnt
    	INNER JOIN dbo.pty ON idnt.idnt_id = pty.idnt_id
    	WHERE pty.case_id =cases.case_id
    	AND pty.pty_cd = 'SPO')
    
    FROM dbo.cases 
    
    INNER JOIN dbo.dspjdg ON cases.case_id = dspjdg.case_id
    
    INNER JOIN dbo.jdg ON dspjdg.jdg_id = jdg.jdg_id
    
    INNER JOIN dbo.casepbsc ON cases.case_id = casepbsc.case_id
    
    WHERE cases.ofic_cd = 'M021601'
    AND cases.case_id = '769143'
    
    AND dbo.casepbsc.spcond_cd = 'COD     2I'
    It works fine on a single case, but I need to get rid of
    Code:
    AND cases.case_id = '769143'
    , so it will pull all cases. Currently it throws Subquery returned more than 1 value

    Thanks in advance for any help.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    SELECT cases.case_id
         , cases.dscr
         , cases.file_dt
         , cases.ticket_nbr
    
         , jdg.jdg_abr
    
         , dbo.casepbsc.spcond_cd
         , dbo.casepbsc.entry_dt
     
         , id.last_name
         , id.first_name
         , id.middle_name
         , id.dod
         , id.idnt_id
    
         , is.last_name
    
    FROM dbo.cases 
    
    INNER JOIN dbo.dspjdg ON cases.case_id = dspjdg.case_id
    
    INNER JOIN dbo.jdg ON dspjdg.jdg_id = jdg.jdg_id
    
    INNER JOIN dbo.casepbsc ON cases.case_id = casepbsc.case_id
    
    INNER JOIN dbo.pty AS pd ON pd.case_id = cases.case_id AND pd.pty_cd = 'DFNDT'
    INNER JOIN dbo.idnt AS id on id.idnt_id = pd.idnt_id
    
    INNER JOIN dbo.pty AS ps ON ps.case_id = cases.case_id AND ps.pty_cd = 'SPO'
    INNER JOIN dbo.idnt AS is on is.idnt_id = ps.idnt_id
    
    WHERE cases.ofic_cd = 'M021601'
    
    AND dbo.casepbsc.spcond_cd = 'COD     2I'
    Hope this helps.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT dbo.cases.case_id
         , dbo.cases.dscr
         , dbo.cases.file_dt
         , dbo.cases.ticket_nbr
         , dbo.jdg.jdg_abr
         , dbo.casepbsc.spcond_cd
         , dbo.casepbsc.entry_dt
         , dfndt.idnt_id
         , dfndt.dod
         , dfndt.last_name
         , dfndt.first_name
         , dfndt.middle_name
         , spo.last_name
      FROM dbo.cases 
    INNER 
      JOIN dbo.dspjdg 
        ON dbo.dspjdg.case_id = dbo.cases.case_id 
    INNER 
      JOIN dbo.jdg 
        ON dbo.jdg.jdg_id = dbo.dspjdg.jdg_id 
    INNER 
      JOIN dbo.casepbsc 
        ON dbo.casepbsc.case_id = dbo.cases.case_id
       AND dbo.casepbsc.spcond_cd = 'COD     2I'
    INNER
      JOIN dbo.pty AS dfndtpty
        ON dfndtpty.case_id = dbo.cases.case_id
       AND dfndtpty.pty_cd = 'DFNDT'    
    INNER
      JOIN dbo.idnt AS dfndt
        ON dfndt.idnt_id = dfndtpty.idnt_id
    INNER
      JOIN dbo.pty AS spopty
        ON spopty.case_id = dbo.cases.case_id
       AND spopty.pty_cd = 'SPO'    
    INNER
      JOIN dbo.idnt AS spo
        ON spo.idnt_id = spopty.idnt_id
     WHERE cases.ofic_cd = 'M021601'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2012
    Posts
    2

    Thanks

    Thanks for your help, I actually came up with a similar solution right after posting.

    Code:
    SELECT cases.case_id
    , cases.dscr
    , cases.file_dt
    , cases.ticket_nbr
    
    , jdg.jdg_abr
    
    , dbo.casepbsc.spcond_cd
    , dbo.casepbsc.entry_dt
    
    , DI.last_name
    , DI.first_name
    , DI.middle_name
    , DI.dod
    , DI.idnt_id
    
    , SI.last_name
    
    FROM dbo.cases 
    
    INNER JOIN dbo.dspjdg ON cases.case_id = dspjdg.case_id
    
    INNER JOIN dbo.jdg ON dspjdg.jdg_id = jdg.jdg_id
    
    INNER JOIN dbo.casepbsc ON cases.case_id = casepbsc.case_id
    
    INNER JOIN dbo.pty AS DP ON cases.case_id = DP.case_id
    AND DP.pty_cd = 'DFNDT'
    
    INNER JOIN dbo.pty AS SP ON cases.case_id = SP.case_id
    AND SP.pty_cd = 'SPO'
    
    INNER JOIN dbo.idnt AS DI ON DP.idnt_id = DI.idnt_id
    
    INNER JOIN dbo.idnt AS SI ON SP.idnt_id = SI.idnt_id
    
    WHERE cases.ofic_cd = 'M021601'
    
    AND dbo.casepbsc.spcond_cd = 'COD     2I'

Posting Permissions

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