Can I join a table to itself in a select?

rbuser

Greetings,

I have a tree structure stored in a table with the following relevent
columns :

ItemID : integer
CatID : integer
Description : integer;
GroupID : integer;

The table actually stores multiple trees each tied to one root node
with a description of that tree. Inside the tree, the description and
other columns store data as needed.

I need to show a list of possible trees with their group number,
description, and the number of items inside the tree. I believe
this required a self-join using alaises.

Here is the query that returns the groupid's with their respective
counts :

SELECT groupid, count(*) as itemsum
FROM items
GROUP BY groupid
ORDER BY groupid

Here is the query that returns the descriptions of the root nodes

SELECT groupid, description
FROM items
WHERE catid = 0
ORDER BY groupid

Can I join these without using a View?

I would like something like the following :

SELECT A.groupid, count(A.groupid), B.description
FROM items A INNER JOIN items B ON A.groupid = B.groupid
WHERE B.catid = 0
GROUP BY A.groupid
ORDER BY 1

Are they are syntax changes that I can make to make this query work?
Any help is appreciated.

Scott Lynn

nicocizik

Hi Scott,

Yes, the syntax you are using to join two tables should work fine. Is there
an issue with the SQL you gave below? You might also want to check out
DADE, which comes with ReportBuilder Pro or higher. Using DADE you can
build queries such as the one below visually and run them based on the data
you choose.

--