daily pastebin goal
4%
SHARE
TWEET

Untitled

a guest Dec 7th, 2017 41 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- 1a
  2. USE library
  3. SELECT
  4.   lastname,
  5.   firstname,
  6.   ISNULL(a1.street + ' ' + a1.city + ' ' + a1.state, a2.street + ' ' + a2.city + ' ' + a2.state) AS addres,
  7.   COUNT(loan.member_no)                                                                          AS total
  8. FROM member
  9.   LEFT OUTER JOIN loan ON member.member_no = loan.member_no
  10.   LEFT OUTER JOIN adult a1 ON member.member_no = a1.member_no
  11.   LEFT OUTER JOIN juvenile ON member.member_no = juvenile.member_no
  12.   LEFT OUTER JOIN adult a2 ON juvenile.adult_member_no = a2.member_no
  13. GROUP BY lastname, firstname, a1.street, a1.city, a1.state, a2.street, a2.city, a2.state, juvenile.member_no,
  14.   member.member_no
  15.  
  16. USE library
  17. SELECT
  18.   lastname,
  19.   firstname,
  20.   (SELECT ISNULL(a1.street + ' ' + a1.city + ' ' + a1.state, a2.street + ' ' + a2.city + ' ' + a2.state)
  21.    FROM member m1
  22.      LEFT OUTER JOIN adult a1 ON member.member_no = a1.member_no
  23.      LEFT OUTER JOIN juvenile ON member.member_no = juvenile.member_no
  24.      LEFT OUTER JOIN adult a2 ON juvenile.adult_member_no = a2.member_no
  25.    WHERE m1.member_no = member.member_no
  26.   )                                         AS addres,
  27.   (SELECT COUNT(loan.member_no)
  28.    FROM loan
  29.    WHERE member.member_no = loan.member_no) AS total
  30. FROM member
  31.  
  32. -- 1b
  33. USE library
  34. SELECT
  35.   lastname,
  36.   firstname,
  37.   ISNULL(a1.street + ' ' + a1.city + ' ' + a1.state, a2.street + ' ' + a2.city + ' ' + a2.state) AS addres,
  38.   COUNT(loan.member_no)                                                                          AS total,
  39.   CASE
  40.   WHEN juvenile.member_no IS NULL
  41.     THEN 'adult'
  42.   ELSE 'kid'
  43.   END                                                                                            AS 'kid/adult'
  44.  
  45. FROM member
  46.   LEFT OUTER JOIN loan ON member.member_no = loan.member_no
  47.   LEFT OUTER JOIN adult a1 ON member.member_no = a1.member_no
  48.   LEFT OUTER JOIN juvenile ON member.member_no = juvenile.member_no
  49.   LEFT OUTER JOIN adult a2 ON juvenile.adult_member_no = a2.member_no
  50. GROUP BY lastname, firstname, a1.street, a1.city, a1.state, a2.street, a2.city, a2.state, juvenile.member_no,
  51.   member.member_no
  52.  
  53. USE library
  54. SELECT
  55.   lastname,
  56.   firstname,
  57.   (SELECT ISNULL(a1.street + ' ' + a1.city + ' ' + a1.state, a2.street + ' ' + a2.city + ' ' + a2.state)
  58.    FROM member m1
  59.      LEFT OUTER JOIN adult a1 ON member.member_no = a1.member_no
  60.      LEFT OUTER JOIN juvenile ON member.member_no = juvenile.member_no
  61.      LEFT OUTER JOIN adult a2 ON juvenile.adult_member_no = a2.member_no
  62.    WHERE m1.member_no = member.member_no
  63.   )                                         AS addres,
  64.   (SELECT COUNT(loan.member_no)
  65.    FROM loan
  66.    WHERE member.member_no = loan.member_no) AS total,
  67.   (SELECT CASE
  68.           WHEN juvenile.member_no IS NULL
  69.             THEN 'adult'
  70.           ELSE 'kid'
  71.           END
  72.    FROM member m2 LEFT OUTER JOIN juvenile ON m2.member_no = juvenile.member_no
  73.    WHERE m2.member_no = member.member_no
  74.   )                                         AS 'kid/adult'
  75. FROM member
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Pastebin PRO 'SUMMER SPECIAL'!
Get 60% OFF Pastebin PRO accounts!
 
Top