Item not on list 1

[Wiszh]

I have a table containing a one to many relationship.

I want to select all of the 'many' items that the a specific 'one' is NOT related to.

Any advice is appreciated!

Thanx

 

[carp]

OK - if you wanted to find all of the children that ARE related to the parent, your query might look like:

SELECT <desired columns>
FROM my_table
WHERE child_column = parent_column;

so the complementary data set would be found with

SELECT <desired columns>
FROM my_table
WHERE child_column <> parent_column;

 

[Wiszh]

I may not have been clear with my post.

Your suggestion would likely get me all of the children related to other parents, not those related to the children themselves.

I think that the answer I need is a NOT IN sub query. However, this may not be the most efficient approach.

Thanks

Wiszh

 

[carp]

Are you asking for
(1) A list of children that are not related to a given parent (which I originally assumed).
(2) A list of parents who have no children
or
(3) A list of children who have no parents

You're right - the above query would give you all of the children that don't belong to a given parent - potentially a huge list!

 

[Wiszh]

I am looking for a list of children (which may or may not be related to other parents) but are not related to a given specific parent.

Thanks for all of your help

 

[carp]

Ah - then my original suggestion will give you exactly that. However, I might have confused you - instead of
SELECT <desired columns>
FROM my_table
WHERE child_column <> parent_column;

I should have said

SELECT <desired columns>
FROM my_table
WHERE child_column <> parent_column_value;

You would supply the parent's value when you run the query.

However, you confuse me with your response:
&quot;Your suggestion would likely get me all of the children related to other parents, not those related to the children themselves.&quot;

I didn't see where there was a requirement to relate children to children.

But if what you are looking for is &quot;a list of children (which may or may not be related to other parents) but are not related to a given specific parent&quot;, then why is a query that will &quot;get me all of the children related to other parents&quot; not what you are looking for?

The only other thing I can see is if you have some children that have a null value in the column(s) that form the foreign key relationship. If that is the case, then you could try the following:

SELECT <desired columns>
FROM my_table
WHERE child_column <> parent_column_value
OR child_column IS NULL;

 

[Wiszh]

I may have been confused when I said one to many relationship. (I orignally view it that way because or the parent-child logic being used (the children do not have any meaning on their own).) There are actually three tables here.

1) A list of parents
2) A list of children
3) A join between the two

I want everything in table 2 that has not been joined with parent X in table 3.

If I select everything in table 2 that is not related to parent X, I will get all of the children related to the other parents in table 1.

Thanks

 

[carp]

OK - so have a go at this:

SELECT *
FROM table2 t2
WHERE NOT EXISTS (SELECT t3.column
FROM table3 t3
WHERE t3.column = t2.column);

There may be more efficient ways to do this, depending on which RDBMS you are using. For example, if you are using Oracle then I would recommend:

SELECT child_column FROM table2
MINUS
SELECT child_column FROM table3;

But this would not be an ANSI standard solution.

 

[Wiszh]

I using MS Access and I was succesfully able to use your first suggestion with NOT IN, rather than NOT EXISTS

Thank you for all of your help.

 

[carp]

You bet!