Getting the right data from a linking table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Getting the right data from a linking table

Author

Topic

Othar
Starting Member

2 Posts

Posted - 2011-02-12 : 16:43:49

I have 2 comma separated lists of people - let's call them @speaker and @visitor. I want to get
- the meeting_id and person_id of the member of @speaker
- from my linking table
- for all meeting_ids where a member of @speaker ist involved and
a) only members of @speaker are involved or
b) at least one member of @visitor is involved.

All statements I tried got me to few or to many results. So I hope someone here can help me.

Here's some sample data (see expected results below):


CREATE TABLE xfer (
	meeting_id	INT NOT NULL,
	person_id	INT NOT NULL
)

INSERT INTO xfer (meeting_id, person_id)
SELECT 1, 9     UNION ALL
SELECT 2, 8     UNION ALL
SELECT 2, 20    UNION ALL
SELECT 3, 301   UNION ALL
SELECT 4, 7     UNION ALL
SELECT 4, 302   UNION ALL
SELECT 5, 21    UNION ALL
SELECT 6, 6     UNION ALL
SELECT 6, 22    UNION ALL
SELECT 6, 303   UNION ALL
SELECT 7, 5     UNION ALL
SELECT 7, 4

@speaker consists of (4,5,6,7,8,9)
@visitor consists of (20,21,22)
The person_ids > 300 are people I don't care about.

I have chosen these numbers because I find it easer to see what's going on; the actual numbers are just a bunch of primary keys.

Expected result:

meeting_id person_id
1          9
2          8
6          6
7          5
7          4

Thanks,
Othar

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-13 : 00:21:36

First you'll need to convert the @speaker and @visitor values into table format .. e.g. CSV to table .. how would you do that check google

you will find a ready code for this just convert it with your namings ..

Once you convert the values to tabular format, you'll get two tables having the values of your variables in tabular format. Let say names of those table would be Speakers and Visitors

And your query would be as follow

Select DISTINCT MeetingID,PersonID
From Xfer
Inner Join Speakers -- this is the table where CSV values are in tabular format

By the way i Dont see a role of Visitors value in your desired output ???? values under the person_ID column are only of the Speaker variable .. shed some light over it ..

Cheers
MIK

Othar
Starting Member

2 Posts

Posted - 2011-02-13 : 04:16:23

quote:
By the way i Dont see a role of Visitors value in your desired output ???? values under the person_ID column are only of the Speaker variable .. shed some light over it ..

I want to create a report of the activities of speakers of one branch. The problem here is that some speakers belong to several branches and there is no way to see which branch a meeting belongs to. So how do I decide whether a meating belongs to branch A or B if the speaker is in both of the branches? What I do know is which visitors belong to which branch. (I don't even want to think of the possibility of visitors belonging to more than one branch...)

As far as I can see my best bet is to get all meetings where a visitor of the current branch is involved and all meetings where only speakers that belong to the branch are involved. That approach does not solve the whole problem - there is still the possibility of a meeting of speakers only where everyone belongs to branch A and B. Then the meeting appears in the reports of both branches. But it's the best I can do with the data I have.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources