0

Basically my table Itinerary table where a record can have a parent record.

so table cound have
ID
Parent ID which can be any ID from The same table.
Other details

Parent ID can be null if the record doesn't have child.

and if i delete the parent record of a record then the child record will not link to the parent.

Is there a way to look for the records which dont have a parent?

want to see all the records which have a PID then check if the PID exists as ID in the same table I want to do this in one query

sample table

ID PID Discription
1 NULL something
2 1 something
3 4 something
5 3 something
6 NULL something

so from the above table i want to select ID 3 as it has a PID and the PID doesnt exists as ID

Thanks for your help in advance

3
Contributors
3
Replies
18
Views
4 Years
Discussion Span
Last Post by pixelsoul
0

You could use a LEFT JOIN in the sql query to get both of the tables and match them on PID, and grab records where PID in the parent table is NULL.

It would be something similar to this

SELECT *
FROM Itinerary i
LEFT JOIN some_table a
ON a.pid = u.pid
AND ISNULL(a.pid)

This actually might work, you would just need to swap out some of the stuff like the parent table name for what ever you are using.

1

@pixel : there is only one table.You have to use alias of one table and join the table.
Actually it must be using a same table

The query can be something like this

SELECT *
FROM yourtable
WHERE PID NOT
IN (

SELECT ID
FROM yourtable
)

Edited by IIM

Votes + Comments
good catch
0

Hmm, thought he was talking about a foriegn key. Yup, your query would work. You forgot one thing though:

SELECT *
FROM yourtable
WHERE PID NOT
IN (
SELECT ID
FROM yourtable
)
AND PID IS NOT NULL
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.