Simple SQL Query (beating my head off desk here)

ve

This is driving me mad, it's been a while since I did any work with SQL, but I am trying to formulate a simple query and I can't for the life of me get it working.

Right 2 tables

Table 1

• id
• name
• etc

Table 2

• id
• a_id
• b_id

In the above Table 2 manages a list of relationships between rows in table 1 (where table2.a_id, table2.b_id are both foreign keys of table1.id). So what I'm trying to do is....

1. Get a list of all table2.b_id where table2.a_id is given, and then the name in table1.name that corresponds to the list of table2.b_id that's returned
2. Then give me the list of all names that are not returned from the last query (out of the entire set of rows in table1)

I am trying joins and nested queries, but I just can't get my head around it. Anyway, driving me mad because I have spend way too much time on this already . If you can help me I would really REALLY appreciate it. Thanks

Ri_Nollaig

why would you need two foreign keys in table2 ?
anyway this might work

select table1.name
from table1, table2
where table2.b_id = table1.id
and table2.a_id = x

and changing the last line to 'and table2.a_id != x' would answer part 2. if this is useless or you have already tried it and it didnt work then my bad.

ve

Thank you very much

Your first query works perfectly, however the second one does not.

In the second one I am trying to get all remaining names from Table1 that are not returned from the first query.

ve

OK I have finally sorted it out. Right I want to point out that based on my description of the problem in my initial post your second query was in fact correct, and it was I who was at fault.

The reason why your second query didn't work in my system is that when there was no relationships between any rows in table1, the query was not returning the inverse selection. Outside of that scenario your query worked fine, so I simply did a check for that scenario in the code. It also turns out that this scenario could not occur when the software is complete, but only now while I am debugging. Later there will be certain other features enabled that will enforce rules that will impact on whether or not various eventualities can occur. So I put the issue down to actual system design, while we are still in the component development/testing phase.

Anyway, your help I could not have succeeded without. Thank you

Ri_Nollaig

no problem