Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34

    Unanswered: Help with a SQL query

    I am selecting search results from the following view. The problem I have is, t5 (a junction) is returning multiple rows, which makes each search result show up several times. I just need one uniqe row from t5 ... (it has to be one row from just t5, not one row from the whole query). Can someone show me how I would accomplish this?

    If anymore information is needed, let me know and I will post here. Thanks!

    SELECT t1.*, t2.doc_type_description AS doc_type_description, t3.file_id AS file_id, t3.file_path AS file_path, t3.active AS active,
    t5.soq_id AS soq_id, t5.soq_name AS soq_name
    FROM dbo.tblSOQDocuments t1 INNER JOIN
    dbo.tblSOQDocTypes t2 ON t1.doc_type_id = t2.doc_type_id INNER JOIN
    dbo.tblSOQFiles t3 ON t1.doc_id = t3.doc_id INNER JOIN
    dbo.jctSOQDocument t4 ON t1.doc_id = t4.doc_id INNER JOIN
    dbo.tblSOQs t5 ON t4.soq_id = t5.soq_id
    ORDER BY t2.doc_type_description, t5.soq_name, t1.doc_name
    -Wigz

    ------------------------------------------------
    docendo discimus.

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    Use sub-queries.

    instead of doing:

    INNER JOIN dbo.tblSOQs AS t5 ON t4.soq_id = t5.soq_id

    do:

    INNER JOIN (SELECT soq_id, MIN(soq_name) FROM dbo.tblSOQs GROUP BY soq_id) AS t5 ON t4.soq_id = t5.soq_id

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For a unique row from t5, use:

    INNER JOIN (SELECT DISTINCT soq_id, soq_name FROM dbo.tblSOQs) AS t5 ON t4.soq_id = t5.soq_id
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by blindman
    For a unique row from t5, use:

    INNER JOIN (SELECT DISTINCT soq_id, soq_name FROM dbo.tblSOQs) AS t5 ON t4.soq_id = t5.soq_id
    That will potentially return multiple rows which is what the original poster wants to avoid

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "I just need one uniqe row from t5 ..."

    He does not specify "One unique soq_id", which is why I asked for clarification. "MIN(soq_name)" seems kind of arbitrary to me. Why includ soq_name in the result set if it is going to exclude some values?

    ...but perhaps that is what he wants.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34

    Clear as mud?

    When I select from this view, it will return several rows. Potentially 2 rows for each result (each of these two rows carries different file information). One result from this query can potentially be associated with several soq_id's, but I just want the first one for each result.

    Clear as mud?
    -Wigz

    ------------------------------------------------
    docendo discimus.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Then do an EXISTS...no?

    Do you need data from both tables or just the one?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    I need data from all the tables that I have joined here ... the relationship will always exist.
    -Wigz

    ------------------------------------------------
    docendo discimus.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well either SELECT DISTINCT which would eliminate duplicates (I know...too easy) or you want 1 row, WHERE the keys are the same, but you will have different values for those keys.

    YOU have to decided what to do with those values. They exists.

    Either USE MAX or MIN, ect, or s series of temp tables.

    Either way, it has to be handled.

    And YOU have to make the decision.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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