Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    8

    Unanswered: SQL Query problem

    Hello,

    I am facing a conundrum at work. I have 3 tables, Table A, Table B, Table C.

    Table A has a one to many relationship with Table B and a one to one relationship with Table C.

    I wrote a query that returns this when:

    B.value1, C.value1
    B.value2, C.value1
    B.value3, C.value1
    B.value4, C.value2
    B.value5, C.value2

    I need to write this where I get all the values from table B and the distinct values of table C where they are linked on table A. I think I need a select distinct but I can't figure out how to implement it.

    Thanks,

    John

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if A is one-to-many with B and A is also one-to-many with C, and if B is not related to C, then you cannot return sensible data from all three tables in one query, unless you shoehorn them into a UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    8

    Many thanks.

    Quote Originally Posted by r937
    if A is one-to-many with B and A is also one-to-many with C, and if B is not related to C, then you cannot return sensible data from all three tables in one query, unless you shoehorn them into a UNION
    Thanks, that's what I was looking for. I'll try a union.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Could you post a small set of data and the results that you'd like to see from that data? I'm not sure that I understand what you want.

    If the relationship from A to B is one-to-many, and the relationship from A to C is one-to-one, then I think the results that you are already getting are what you asked for (although it might not be what you want).

    -PatP

  5. #5
    Join Date
    Sep 2004
    Posts
    8

    Relationship

    Hi Pat,

    I finally figured out that they were indeed related. What I forgot to account for was my selection is returning multiple records Table B and Table C, where I only wanted one record from Table C and the multiple records from Table B.

    So now I'm adding the records from Table C to a temp table and SUMming the field I want from Table B.

    I think that will work, however as I'm dead tired (most of the problem with the above) I won't find out until Monday.

    Thanks Pat,

    John

Posting Permissions

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