Hi all....
Can anyone help me out with a select statement?

I have a 'categories' table and need to select all records where the ParentID equals a given value, eg. 137, but in the same statement i also need to select all records where the ParentID equals the CategoryID of the first part of the select statement.
So the first part would give me records like this:

CategoryID ParentID Category
1 137 17th Edition Testers
2 137 Clampmeters
3 137 General Electricals

The second part would give me records based on ParentID = CateogryID from the first part of the statement like.

CategoryID ParentID Category
9 1 Calibration Checkboxes
10 1 RCD Testers
11 1 Loop Testers
19 2 Fluke Clampmeters

etc....

But i need all of these records from one select statement!
Is this possible?

Thanks
Andy

r937 - Thanks that works perfectly :-)

Very much appreciated
Andy

My requirements have changed slightly from my original post and i hope i can get some help on my 'new headache' :-)

I need to pull the three levels (top, sub, sub sub) from my database for the menu system im making.
At present im getting two levels.
I've had a few attempts at at but so far haven't succeeded

At present i'm using the following SQL :
SELECT one.ParentID, one.Category AS Parent, one.CatID, one.CategoryID, two.Category, two.CategoryID
FROM categories AS one INNER JOIN categories AS two ON two.ParentID=one.CategoryID
WHERE one.ParentID=0 And one.CatID='e'
ORDER BY one.Category;

I have attached the categories table and SQL Query Result screengrabs to illustrate.

Thanks in advance - fingers crossed

Andy

try this --

Thanks r937

I'm getting an error when i run the query - please see attached screen grab
Looks like you know what you're doing though.
Hope you can see where the problem is

Andy

you forgot to mention that you're using access

i've edited my query to include the necessary parentheses that access requires when more than 2 tables are involved in a join

r937 - you are a star :-)
How the hell did you work it out?
I need to read up more on SQL

My local machine is using Access, but the live site is using MS SQL so both examples will be very useful.

Thanks again - fantastic
Andy

i urge you most sincerely to install SQL Server Express on your local machine so that you can test your queries properly

there are numerous differences between Access SQL and SQL Server SQL

save yourself a lot of future frustration and heartache

Hi r937
I took your advice and now have SQL Server 2008 Express on my local machine :-)
I ran the Query in SQL Server without the extra parentheses and it gives the correct results for the subsub categories.

Is there a way of getting all of the categories displaying in the three tables (parent, category, category)?
This would allow me to create just one recordset instead of two.
I've attached a screengrab of the subsub category results and another showing the desired results (subsub category needs to be in the correct order)
Sorry to be such a pain.....
How do you create your SQL queries?

Thanks
Andy

sorry, i don't know what you're asking here

in UltraEdit (a text editor)

Thanks for the reply again r937 - sorry to take up your time. It's greatly appreciated.
I've attached another screengrab. Hope it helps
Thanks
Andy

well, i can see that you're displaying a heirarchy to the 3rd level

what was the question again?

Hi r937

That screen grab is just two images stuck together, showing what i want to get out of the database :-)
I would like to get this result using one statement and one recordset instead of two.
The first SQL below displays the top and sub categories
The second only displays the subsub categories

Thanks again - owe you a beer



SELECT one.ParentID
, one.Category AS Parent
, one.CatID
, one.CategoryID
, two.Category
, two.CategoryID
FROM categories AS one
INNER
JOIN categories AS two
ON two.ParentID=one.CategoryID
WHERE one.ParentID=0 And one.CatID='e'
ORDER BY one.Category;


---------------------


SELECT one.ParentID
, one.Category AS Parent
, one.CatID
, one.CategoryID
, two.Category
, two.CategoryID
, three.Category
, three.CategoryID
FROM (
categories AS one
INNER
JOIN categories AS two
ON two.ParentID = one.CategoryID
)
INNER
JOIN categories AS three
ON three.ParentID = two.CategoryID
WHERE one.ParentID = 0
AND one.CatID = 'e'
ORDER
BY one.Category
, two.Category
, three.Category

use the second query and change the INNER JOINs to LEFT OUTER JOINs