Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009

    Unanswered: SQL Join and pivot multiple tables

    I am not sure how to accomplish what I need. Perhaps a kind soul can push me in the right direction.

    I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.

    The DocType and Docs tables are relational. DocType.ID = Docs.tID
    The Users and Docs tables are relational. Users.ID = Docs.uID

    Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.

    What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.

    For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.
    Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.

    Does that make sense?

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by keb1965 View Post
    Does that make sense?
    yup, it does

    however, the "as columns" part is something that should best be done in your application layer

    obtaining the boolean results is straightforward
    SELECT AS user_id
         , AS doctype_id
         , CASE WHEN d.uid IS NULL
                THEN 'nope'
                ELSE 'yup'  END  AS boolean
      FROM users AS u
      JOIN doctypes AS dt
      JOIN docs AS d
        ON d.uid =
       AND d.tid = | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Cool, that was the push I needed.

    I did manage to get the query returning exacly what I wanted. All I need to do now is add parameters so I can pass values to the stored procedure.


Posting Permissions

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