Help With Distinct Query

[mdumka]

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:

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

[r937]

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?

[mdumka]

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

[r937]

yes, thanks, that makes sense, i just wasn't sure there was only one table involved

select distinct
       p.ItemID
     , p.Name
  from items as c
inner
  join items as p
    on c.ParentID
     = p.ItemID

[mdumka]

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

[r937]

SQL for Smarties, by Joe Celko, will be issued later this spring as a revised edition