Select query to filter out data

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)

Select query to filter out data

Author

Topic

vsempoux
Starting Member

5 Posts

Posted - 2008-07-17 : 03:10:14

Hello all,

I've been strugling a while with a little problem.
Let me first give you some details:
I have four tables :
table 1 = T1 (with campaign info) with a unique id (id1)
table 2 = T2 which contains additional information. each detail line is linked with an id from T1 (id1_id)
In this table i specify languages (EN, FR, DE, ....).
table 3 = T3 contains all my users with an unique id (id3)
table 4 = T4 contains additional info about the users (language knowlegde). Again eacht detail is linked to T3 (id3_id)
the languages are also specified like EN, FR, DE, ...

Now my problem. I have to make a query that will display all id's of T1 based on the language knowledge of a specific user.
example: user 1 has language EN, FR. Campaings(T1) have language specification.
The query can only display the id's from T1 where the language are only EN and/or FR. If a campaign has EN, FR and DE as
language, the query can not return the id.

Does anybody have an idea on how i could resolve this problem?

Thanks in advance.
Greetings to all!
Vincent

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-17 : 03:38:53

Are the languages on seperate lines or the same line?

If seperate lines then something like:


select <fieldlist>
from T1 
inner join T2 
on t1.id = t2.id
where t2.lang in (EN,FR) and t2.lang not in (DE,PL, etc..)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 03:49:38

please show some sample data from tables

vsempoux
Starting Member

5 Posts

Posted - 2008-07-17 : 04:09:42

Hello,

Already thanks for the reply.

The language details are stored on seperate lines.
Let me give you a sample of my tables.
T1
ID1
1234
1235
1236
1237
...

T2
ID1_ID Lang
1234 NL
1234 FR
1234 EN
1235 NL
1235 EN
1236 FR

T3
UID User
987 user1
988 user2

T4
UID_ID LangKwoledge
987 NL
987 FR
988 EN
988 FR

Ok. From my application i get the current userID (CurUID).
If curUID is 987 (just an example of course) my query should return only id 1236 because the other id's have EN in there
language specifications.

Greetings,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 04:36:22

[code]SELECT t2.ID1_ID
FROM T2 t2
INNER JOIN T4 t4
ON t4.lang=t2.lang
INNER JOIN T3 t3
ON t3.UID=t4.ID
WHERE t3.UID=@CurUID
GROUP BY t2.ID1_ID
HAVING SUM(CASE WHEN t2.lang='EN' THEN 1 ELSE 0 END)=0[/code]

vsempoux
Starting Member

5 Posts

Posted - 2008-07-17 : 05:01:06

Hello visakh16,

i have a few remarks/questions.
my query result has to display all data in T1 based on the filtering that has been done on the other tables.
I only want one unique id even if there are multiple languages.

The query is intended to show users which campaign they may handle. campaigns that contain languages
who are not in the language knowledge of a user cannot be handled by the specific user.

the query result must be something like this: (parameter=988 (user2))
ID1
1236

Also, language specification is user dependant (different users have different language knowledge) so i dont't understand
why you are using the HAVING SUM line at the end of the query.

Thanks for your help.
Greetings

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 05:06:55

quote:
Originally posted by vsempoux

Hello visakh16,

i have a few remarks/questions.
my query result has to display all data in T1 based on the filtering that has been done on the other tables.
I only want one unique id even if there are multiple languages.

The query is intended to show users which campaign they may handle. campaigns that contain languages
who are not in the language knowledge of a user cannot be handled by the specific user.

the query result must be something like this: (parameter=988 (user2))
ID1
1236

Also, language specification is user dependant (different users have different language knowledge) so i dont't understand
why you are using the HAVING SUM line at the end of the query.

Thanks for your help.
Greetings

so should you be getting only ids that have at least one language not under passed id?

vsempoux
Starting Member

5 Posts

Posted - 2008-07-17 : 05:30:44

Hi,

The objective is to get a list of id's who only correspond to the lang specs of the user.
So the list cannot contain id's who have language that are not in the specs of the user.
All id's in T1 can have one or more languages but if even one language is not in the specs of
the user, it cannot be listed.

Greetings

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources