SELECT help...

[pgferro]

I have two tables : students and contacts
Contacts can be related to several students and viceversa

I'm trying to come up with a select (prior 4.1) that will allow me to choose a contact which is not related to a particular student, but also not related to any other student.

Contact table example :

ContactID   ContactStudentId
  1            1
  1            2
  2            2
  3            3

Criteria is 'select contacts that are not related to student id 1 but aso not related to any other student'

With the above example the result would be contact 2 and contact 3

I've tried all I could think about... I'm really stuck
Appreciate the help !

Thanks

--
PG

 

[TonyGroves]

Your information is not totally clear. Is there one table, or two? Maybe you could also give us a bigger example with expected results.

However, based on my understanding of the problem, the following should work, assuming there are no null fields involved:
[tt]
select
contactid,
sum(studentid=1 or contactid<>studentid) s
from contab
group by contactid
having s=0
[/tt]

 

[pgferro]

Thanks Tony,

You are right, it is not very clear..

There are 2 tables
1 is contact_to_students (example above)
2 is contact_bio (with name address etc)

Goal is to get the names of contacts 2 and 3 (always based on the above example)
There is no Null value

Thanks

--
PG

 

[TonyGroves]

That's what I thought. Have you tried the solution?

 

[pgferro]

Tony,

I understand you are using just one table, how can I fit the query to my example ?

Sorry... I'm aware that I have big limits..

Thanks !

--
PG

 

[TonyGroves]

Sorry, I see you want the contact name, not just the id. In that case, how about:
[tt]
select
cs.contactid,c.contactname,
sum(cs.studentid=1 or cs.contactid<>cs.studentid) s
from constud cs join contacts c on (cs.contactid=c.id)
group by contactid
having s=0
[/tt]

 

[pgferro]

Tony,
Thanks again.

Always no results...

Maybe you got confused by the table example, which would be more like :

ContactID   ContactStudentId
  1            125
  1            158
  2            158
  3            140

Thanks again for your time

--
PG

 

[pgferro]

The subquery would look like :

SELECT name, contact
FROM contact
WHERE contact NOT
IN (
SELECT contact
FROM contact_to_students
WHERE student = whatever_id
)

 

[TonyGroves]

Sorry, I can't figure out your requirements. Maybe you could post your problem again with more explanation and a bigger example.