Not sure how to join two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not sure how to join two tables

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-06-01 : 07:01:00

Hi

I think I need a table join, but I'm not sure which one or how.

Table 1 lists all absences (with reason) for a class location

Table 2 is a view that's filtered based on the classes a member attends. The view also lists the location ID for each class.

What I need to is to list any absences based on location (table 1) against the total list of classes the member attends.

In other words, work through table 1 and get a location ID, then look to see if that same location ID is also referenced in the members table - if it so, get the reason for absence.

Any ideas?

Thanks as always

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-01 : 08:54:16

Is this what you are looking for:
[CODE]

DECLARE @TEMP1 AS TABLE([LocID] INT, [Reason] VARCHAR(30), [SID] INT, [Period] INT);
INSERT INTO @TEMP1([LocID], [Reason], [SID], [Period])
SELECT 2, 'Reason1', 2, 1000 UNION ALL
SELECT 1, 'Reason2', 1, 2430 UNION ALL
SELECT 4, 'Reason3', 2, 2430 UNION ALL
SELECT 3, 'Reason4', 3, 2430;

DECLARE @TEMP2 AS TABLE([LocID] INT, [Class] VARCHAR(20), [SID] INT);
INSERT INTO @TEMP2 VALUES(1, 'History', 1);
INSERT INTO @TEMP2 VALUES(2, 'Math', 2 );
INSERT INTO @TEMP2 VALUES(2, 'Science', 3);
INSERT INTO @TEMP2 VALUES(1, 'English', 4);
INSERT INTO @TEMP2 VALUES(3, 'History', 1);
INSERT INTO @TEMP2 VALUES(4, 'Math', 2 );
INSERT INTO @TEMP2 VALUES(3, 'Science', 3);
INSERT INTO @TEMP2 VALUES(4, 'English', 4);

SELECT T2.LocID AS Location, T2.[SID] AS Member, Class, T1.Reason
from @TEMP2 T2 INNER JOIN @TEMP1 T1 ON T1.LocID = T2.LocID and T2.[SID] = 2;

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources