SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help With Distinct Query

    Hello,

    First off I am using Access.

    I have a simple query where I need the Information from 2 fields, I know hoe to get the first bit of data but not the second.

    First I need the Distinct ParentID's from the table Items:

    Code:
    SELECT DISTINCT ParentID FROM Items
    This Gives me a recordset of 0, 5, 11.

    Next I need the data in the Name field based on these ParentID's. Is there a way to do this with out doing 2 queries?

    Thanks,

    Mike

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    this may turn out to be either simple or not, depending on what you mean

    which table is the Name column in?

    can you give a few sample rows of the items table?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Rudy,

    I have 1 table ...
    [items]

    For this example there are 3 fields ...
    [ItemID]=Autonum
    [Name]=Text
    [ParentID]=Number (Long Integer)

    Some Sample Data is ...
    [1][Name 1][0]
    [2][Name 2][0]
    [3][Name 3][0]
    [4][A Sub Cat 001][1]
    [5][A Sub Cat 002][1]
    [6][A Sub Cat 003][2]

    Background Info ... ItemID is the Primary Key, Name is self explanitory, ParentID is the column that shows which item is related to, by that I mean you can see ItemID's 1, 2, and 3 are Main Item's related to no other Items. ItemID's 4 and 5 are related to ItemID 1 via its ParentID and ItemID 6 is related to ItemID 2 via its ParentID.

    So ... the Record Set I want has Distinct ParentID's, And since these ParentID's are related to the ItemID's I would like the Name's as well ordered by Name asc.

    Recordset ...

    0 - [empty string]
    1 - [Name 1]
    2 - [Name 2]

    Clear as Mud? Well I accomplished this using some coding but thought it might be possible by 1 query.

    Thanks Rudy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, thanks, that makes sense, i just wasn't sure there was only one table involved
    Code:
    select distinct
           p.ItemID
         , p.Name
      from items as c
    inner
      join items as p
        on c.ParentID
         = p.ItemID
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow, thanks rudy.

    Another question though. I see you answering a ton of the SQL questions, I have learned basic SQL select, insert, update and delete.

    Do you recomend any books for programmers looking to take thier SQL to another level?

    Thanks again.

    Mike

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    SQL for Smarties, by Joe Celko, will be issued later this spring as a revised edition
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •