Another JOIN problem 1

[Naoise]

I have 3 tables

tblTeamMemberAreaCodes
============================
TeamMemberID | IntervalID | AreaCode
1 3 1
1 4 1
1 3 2

tblAreaCodes
===========================
AreaCode | AreaID
ABC 1
DEF 2
GHI 3

tblInterval
============================
IntervalID | Hours
1 1
2 2
3 3
4 4


How do I get all manipulations of AreaCodes and Intervals minus those that are in tblTeamMemberAreaCodes?

ie in this case I would be looking a result set like

TeamMemberID | IntervalID | AreaCode
NULL 1 1
NULL 1 2
NULL 1 3
NULL 1 4
NULL 2 1
NULL 2 2
NULL 2 3
NULL 2 4
NULL 3 2
NULL 3 3
NULL 3 4
NULL 4 2
NULL 4 3
NULL 4 4

ie every match up of Intervals and AreaCodes that are not in tblTeamMemberAreaCodes?

At the moment I have

SELECT NULL as 'TeamMemberID', IntervalID, AreaCode
FROM tblInterval a, tblAreaCodes b
WHERE a.IntervalID NOT IN (SELECT IntervalID FROM tblTeamMemberAreaCodes WHERE TeamMemberID = 1)
AND b.AreaCode IN (SELECT AreaCode FROM tblTeamMemberAreaCodes WHERE TeamMemberID = 1)
AND b.AreaID = 1
ORDER BY AreaCode, IntervalID

which is not working as the where clause is removing all IntervalID's which is incorrect. Any ideas?

 

[PHV]

Some discrepancies in your post ...
1) AreaCode or AreaID ?
2) Why this: NULL 3 2 ?

Anyway a starting point:
SELECT NULL TeamMemberID, A.IntervalID, B.AreaID AreaCode
FROM tblInterval A, tblAreaCodes B
WHERE A.IntervalID || B.AreaID NOT IN (SELECT IntervalID || AreaCode FROM tblTeamMemberAreaCodes WHERE TeamMemberID = 1)
ORDER BY 2, 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Apologies, you are right in both cases. I am looking for the AreaCode and the record NULL 3 2 should not have been in my final result set.

I had to change || to | to suit my db vendor MSSQL 2K, I know you might think at this stage I should switch the thread to a board more appropriate to my vendor, but I was hoping to find an answer here.

When I ran your query I got :
Syntax error converting the varchar value 'ABC' to a column of data type int

The problem I am guessing lies here
WHERE A.IntervalID || B.AreaID NOT IN (SELECT IntervalID || AreaCode FROM

...as two int values are being compared to an int value or the varchar AreaCode.

However I think that this query would have run into the same problems I was running in to namely that it will
exclude results which have a IntervalID or an AreaCode in tblTeamMemberAreaCodes, whereas what I am trying to
do (if this is even possible!) is to remove records from my final result set that have no matching records
ie IntervalID + AreaCode as a PK in tblTeamMemberAreaCodes.

If I were to simply it further :

tblA
=================
IDA | A

tblB
=================
IDB | B

tblC
=================
IDC | A | B
1 1 foo1
3 4 foo2
5 6 foo3

tblAllCombinationsOftblA_PLUS_tblB
=================
IDD | A | B

How would you get a result set which is basically (tblAllCombinationsOftblA_PLUS_tblB) - (tblC)?

I just can't see right now how I can accomplish this, thanks again

 

[r937]

you should probably start a new thread in the SQL Server forum

here in the ANSI SQL forum, you are going to continue to get ANSI SQL answers, and SQL Server doesn't support ANSI SQL fully

in particular, SQL Server has different operators for concatenation

r937.com | rudy.ca

 

[PHV]

You may try this (ANSI code):
SELECT NULL TeamMemberID, A.IntervalID, B.AreaCode
FROM tblInterval A, tblAreaCodes B
WHERE A.IntervalID || ',' || B.AreaCode NOT IN (SELECT IntervalID || ',' || AreaCode FROM tblTeamMemberAreaCodes WHERE TeamMemberID = 1)
ORDER BY 2, 3

Tip: || is the concatenation operator.

Another way:
SELECT NULL TeamMemberID, A.IntervalID, B.AreaCode
FROM tblInterval A, tblAreaCodes B
WHERE NOT EXISTS (SELECT * FROM tblTeamMemberAreaCodes WHERE TeamMemberID=1 AND IntervalID=A.IntervalID AND AreaCode=B.AreaCode)
ORDER BY 2, 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Merci beaucoup PHV, tu as me sauvé encore! Allez les bleus ce Mercredi