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

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.

@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
)
commented: good catch +6

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.