[RESOLVED] Simple relational database query.

[doublehops]

I'm trying to write a simple relational query but not sure exactly how to do it.

I have two tables, one contains a list of categories and the other contains a parent/child relationship between the categories. How would I write one query that would return all the categories where the parent_category would be equal to zero in the second table.

Regards,
Doublehops.

[nemonoman]

Let me understand this:

You have two tables: like

category

id
name

and

categoryrelationships

parentid
childid

And you want to write a query to
SELECT *
FROM categoryrelationships
WHERE parentid=0

??

How do you have records in the categoryrelationships table where the record has no parent id?

Perhaps what you mean to say is:
I want to

SELECT *
FROM category

categories not present in the categoryrelationship table

So you'll want to JOIN those tables -- specifically a LEFT JOIN. You want to return category records (even if there are no categoryrelationship records -- in fact SPECIFICALLY when there are no category relationship records).

SELECT category.*
FROM category
LEFT JOIN categoryrelationship ON category.id=categoryrelationships.parentid

That gets you all the category records, and the relationships where they are the parentid (or not)

But you only want the "orphan" categories

SELECT category.*
FROM category
LEFT JOIN categoryrelationship ON category.id=categoryrelationships.parentid
WHERE categoryrelationships.parentid IS NULL

[doublehops]

Thanks nemonoman,

Although I left a somewhat vague description of my problem, your suggestion was perfect.