daily pastebin goal
48%
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
Pastebin PRO CHRISTMAS Special!
Get 60% OFF Pastebin PRO accounts!
Top