please help me its driving me nuts

[minckle]

Please can some 1 help me

I have 3 tables
1 with contacts in
1 with groups in
and 1 to link the 2 together

each contact and group have id's which are used in the link table

i need 2 sql queries
1 saying which contacts are in each group (i think ive cracked this)
and 1 saying which contacts are not in each group(which i need help)

Some 1 please help its driving me nuts

[RabsterUK]

You could try this mate. As you can see I don't do things simply when there is a hard way to do. As I say you could try it till an expert comes along.

Select Distinct lnk.[GroupID],
(Select Distinct lnk2.[CustomerID]
from [LinkTable] lnk2
Where lnk.[GroupID] not in
(Select Distinct lnk3.[GroupID]
from [LinkTable] lnk3
where lnk3.[CustomerID] =
Lnk2.[CustomerID))
as 'Customers not in Group'
From [LinkTable] lnk as 'Groups'

[minckle]

sorry youve lost me with the Inks should this be the Link table. hope im not being thick.

thanx

[RabsterUK]

Sorry it's because it is lowercase LNK will do just as well. Let me know how you get on. If it doesn't work then I'll try and fix it. Someone will hopefully come on with a quicker and tidier method.

[minckle]

hi again sorry bout this im new to sql a i normally use access
i think ur gonna have to spell it out for me

i have 1 table called 'contact'
1 table called 'group'
and 1 called 'contact_group'(which is the link table)

sorry for being a pain i really appreciate your help

thanx

[jkoopmann]

minckle,
please supply a bit more information before someone or myself takes a crack at this.
A. please remember to give the database platform you are working on.
B. please provide the full table names and column names.
C. please give a bit more explanation to the two queries you are trying to solve. the verbage is a bit misleading. in particular for :
1. do you mean / want a sql statement that returns the contacts that exist in every group, ie. only return a contact_id that has a relationship to every group in the group table? or do you want a statement that returns the contacts and the groups they belong to. these are very much diferent queries.

2. are you asking for a query that returns the contact_id(s) that do not have a relationship with each and every group? or do you just need a sql statement that returns the contact_id(s) that do not have a relationship to any groups?

i know that i am being a stickler for syntax here but depending on how you answer these questions, means a very simplistic sql statement to a somewhat difficult sql statement.

thanks,

[minckle]

im using MySQL

Table 1
Name 'contact'
Fields 'id', 'name'

Table 2
Name 'grp'
Fields 'id', 'name'

Table 3
Name 'con_grp'
Fields 'id', 'con_id', 'grp_id'

Example data
2 Contacts 'Matt' , 'Chris'
4 Groups 'grp1', 'grp2', 'grp3', 'grp4'

Statement 1
the result should say which contacts are in which groups.
I need the result to show e.g

'Matt' is in 'grp1'
'Matt' is in 'grp2'
'Matt' is in 'grp3'
'Chris' is in 'grp3'
'Chris' is in 'grp4'

Statement 2
i suppose this is the opposite saying this contact is not in this group and that group and this contact is not in this group and that group.
I need the result to show e.g

'Matt' is NOT in 'grp4'
'Chris' is NOT in 'grp1'
'Chris' is NOT in 'grp2'
'Chris' is NOT in 'grp3'

let me know if this doesnt make sense.

Thanx for ur help

[RabsterUK]

Try this:

Select cnt.[Name] as 'Contact Name',
(Select grp.[Name]
from [group] grp
where grp.[id] = cg.grp_id) as 'Not in Group'
from contact cnt
join con_grp cg
on cnt.[id] <> cg.con_id
Order by Cnt.[Name], 'Not in Group'

It worked for me using different headers and tables so if it doesn't work let me know as I may has forgot to change something in the syntax.

[jkoopmann]

minckle,
RabsterUK (thanks).
i was working on this also, glad to see the help line working. anyway, my baseline is oracle so just in case you want to compare SQL from oracle (don't know of it's validity for mySQL), but assuming it is standard sql here it is:

select distinct contacta.name||' is NOT in '||grp.name
from contact contacta, grp, con_grp
where grp.id = con_grp.grp_id
and grp.id not in ( select grp.id from contact, grp, con_grp
where grp.id = con_grp.grp_id
and contact.id = con_grp.con_id
and contacta.id = contact.id)

if you want, please let me know if this works in mySQL.
thanks,

[greenman]

In MySQL, given the sample data:

mysql> SELECT * FROM grp;
+------+------+
| id   | name |
+------+------+
|    1 | grp1 |
|    2 | grp2 |
|    3 | grp3 |
|    4 | grp4 |
+------+------+

mysql> SELECT * FROM contact;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Matt  |
|    2 | Chris |
+------+-------+

mysql> SELECT * FROM con_grp;
+------+--------+--------+
| id   | con_id | grp_id |
+------+--------+--------+
|    1 |      1 |      1 |
|    2 |      1 |      2 |
|    3 |      1 |      3 |
|    4 |      2 |      3 |
|    5 |      2 |      4 |
+------+--------+--------+

The first query is easy:

SELECT contact.name,grp.name 
FROM grp,con_grp,contact 
WHERE contact.id=con_grp.con_id 
AND grp.id = con_grp.grp_id;
+-------+------+
| name  | name |
+-------+------+
| Matt  | grp1 |
| Matt  | grp2 |
| Matt  | grp3 |
| Chris | grp3 |
| Chris | grp4 |
+-------+------+
Alternatively:
SELECT contact.name,grp.name 
FROM con_grp 
LEFT JOIN contact 
ON contact.id=con_grp.con_id 
LEFT JOIN grp 
ON grp.id=con_grp.grp_id;
+-------+------+
| name  | name |
+-------+------+
| Matt  | grp1 |
| Matt  | grp2 |
| Matt  | grp3 |
| Chris | grp3 |
| Chris | grp4 |
+-------+------+

As for the second query... That depends on the version of MySQL! No stable version of MySQL can do subqueries (so the Oracle and SQL Server code supplied by the others won't work) - usually you can get around it easily, but not this time.. I'd need to give some thought to doing this - it's been a long day!

[greenman]

Argh, the joys of cross-posting. This query was also posted in another forum, where it's been answered much better than all of us so far: You can check the MySQL forum