SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool SQL SERVER 2000 and Distinct Query

    I pull of a distinct query if I just run the first line of SQL.
    But after that I want to continue to query and join on another table and add the additional columns to my result set.

    Right now, and rightfully so, I am only getting 1 column returned, because of the DISTINCT keyword.

    How do I display additional, joined-table columns, using the result-sets from the DISTINCT query?


    SELECT Distinct Pattern_Code FROM RF_Shirt_Pattern
    JOIN Shirts
    ON Shirts.Shirts_ID = RF_Shirts_Pattern.Shirts_ID
    ORDER BY Pattern_Code

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    wouldn't each shirt pattern have its own unique pattern code? what is the purpose of DISTINCT? what is the primary key of each table? what is the purpose of the join? what is the relationship between shirts and shirt patterns? what other columns do you want? and from which table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    wouldn't each shirt pattern have its own unique pattern code? what is the purpose of DISTINCT? what is the primary key of each table? what is the purpose of the join? what is the relationship between shirts and shirt patterns? what other columns do you want? and from which table?
    Yes, each shirt should have it's own pattern code. The purpose of the distinct is because for whatever reason, (4th generation programmer on this account) the table has duplicates and I do not at this time know if they are needed. Thus the distinct.

    The primary key of the Shirts table is Shirt_Id and the primary key of the RF_Shirts_Pattern table is NOT SET.

    The purpose of the join is to provide the additional details in the 2nd table. Isn't that always the reason for a Join?

    This is fine:
    SELECT * FROM RF_Shirts_Pattern
    JOIN Shirts
    ON Shirts.Shirts_ID = RF_Shirts_Pattern.Shirts_ID
    ORDER BY Pattern_Code

    Works good.
    But now I would like to make it a DISTINCT on RF_Shirts_Pattern.Pattern_Code

    There is no documentation/diagraming of the tables. Again, we do not always GET that luxury. I would be glad to do that now however as time permits.

    I want Shirt_Name from Table Shirts


    Am I going to have to throw the first Distinct query into a Virtual lookup table?

  4. #4
    SitePoint Addict Poiesis01's Avatar
    Join Date
    Jun 2007
    Location
    Cape Town
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a shot in the dark here but would a SELECT TOP 1 work? I'm assuming you getting multiple rows with the same data with SELECT * and you just want one row to be returned?

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I got it.

    I thought I was limited to only 1 column name when using the DISTINCT command. I am not it turns out. So this query works.

    SELECT Distinct Pattern_Code, RF_Shirt_Pattern.Shirt_ID, Shirt.Shirt_Name FROM RF_Shirt_Pattern
    JOIN Shirt
    ON Shirt.Shirt_ID = RF_Shirt_Pattern.Shirt_ID
    ORDER BY Pattern_Code


Bookmarks

Posting Permissions

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