Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    4

    Question Unanswered: View problem - select by value

    My problem:
    I can't describe the whole thing but essentially it is this.
    I have say 3 tables. All tables have fields that in one way or another is related to eachother using primary keys and foreign keys.
    From these tables I can select data to create a view. In this example we can say its one field from each table.
    The problem is that the last 2 columns of the view is to be populated with 0 or 1 depending on whether a certain value is found in a specific table.

    Here are example tables. Question is how can I get the last 2 columns in the view? Probably a subquery but it still has to be for the correct CustID. I can't restrict the select to only ProductID 5 and 3 because if the CustID does not have ProductID 5 or 3 there should be a 0 in that column. Please help!

    Table 1 [Employees]
    ------------------------
    CustID Value
    1 500
    2 400

    Table 2 [Owner]
    -------------------
    CustID Value
    1 'Smith'
    2 'Jones'

    Table 3 [Products]
    ---------------------
    CustID ProductID
    1 5
    1 3
    1 2
    2 1
    2 4
    2 5
    2 6

    View
    -----
    CustID Employees Owner Product5 Product3
    1 500 'Smith' 1 1
    2 400 'Jones' 1 0

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: View problem - select by value

    What about this:

    select e.CustID, e.[value], o.[value]
    ,case when exists (select ProductID from Products where CustID=e.CustID and ProductID=5)
    then 1 else 0 end as Product5
    ,case when exists (select ProductID from Products where CustID=e.CustID and ProductID=3)
    then 1 else 0 end as Product3
    from Employees e
    join Owner o on o.CustID=e.CustID

  3. #3
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    4

    Re: View problem - select by value

    Thanks snail.
    Really you saved my day.
    The solution is - Of Course! But that's now that you've solved it.
    Have a nice day.

  4. #4
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    4

    Re: View problem - select by value

    There is one problem though.
    With the solution snail posted I get duplicates of the primary key. I use DISTINCT on the main SELECT but this subquery generates multiple rows with the same PK. The number of rows is the number of possible combinations of the values.
    With snails solution I get:
    ID A B C
    1 0 0 0
    1 1 0 0
    1 0 1 0

    But what I need is:
    ID A B C
    1 1 1 0

    This is a bit over my head. Does anyone have a suggestion?

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: View problem - select by value

    What about this?

    select e.CustID
    ,case when exists (select ProductID from Products where CustID=e.CustID and ProductID=5)
    then 1 else 0 end as Product5
    ,case when exists (select ProductID from Products where CustID=e.CustID and ProductID=3)
    then 1 else 0 end as Product3
    from Employees e
    join Owner o on o.CustID=e.CustID
    group by e.CustID

  6. #6
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    4

    Re: View problem - select by value

    Thanks for helping snail.
    I think I'll have to give you the whole thing though.
    It's a single SELECT statement (like a view). It works fine except for the last 4 columns which generates duplicate rows and so the primary key is repeated and that of course does'nt work.
    The BIG question is how to get the last 4 columns with values without them getting repeated in multiple rows.
    Attached Files Attached Files

Posting Permissions

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