Query many to many - SQL Server Forums

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)

Query many to many

Author

Topic

callevib
Starting Member

5 Posts

Posted - 2009-03-26 : 06:05:30

I nead help now please.
I have 3 tables like this:
Address:
Id
Name
ect

Activites:
Id
Name
Active (bol)
etc

Adr_akt:
AdrId
ActId

The last is a Many To Many realtion

I have the following T-Sql query:

select k.kndadresseid, k.kndnavn,
'akt1' =
case ka.aktid when 1 then 'true' else 'false' end,
'akt2' =
case ka.aktid when 2 then 'true' else 'false' end,
'akt3' =
case ka.aktid when 3 then 'true' else 'false' end,
'akt4' =
case ka.aktid when 4 then 'true' else 'false' end
from adresser as k
full join knd_akt as ka
on k.kndadresseid = ka.kndadresseid

With a output like this:
1241 Frede Hejlskov Jensen true false false false
1754 Arne Andersen true false false false
1755 Hans Aksel Andersen true false false false
1762 Hanne og Flemming Barrett true false false false
1762 Hanne og Flemming Barrett false false true false
1762 Hanne og Flemming Barrett false false false true
1766 Arne Brøgger Christiansen false false true false
1766 Arne Brøgger Christiansen true false false false
1767 Else Aagaard Christensen false false true false
1785 Cai Andersen Hoe false false false true

So fare very vell.
As you se I have some of the names more than on time. How can I avoid this?

I have 4 activities in the sample. I only want the activities whitch are activ, which change over time. It means that the numbers off activities had to be dymanic.
Is it possible and how can i do this?

I want to show the table in a gridview in a web page with checkboxes for the activities.
I use MS SQL2005, Asp.net 2.0 (Visual studio 2005)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-26 : 06:31:45

When I have a problem then I will give:
- create SampleTable
- insert SampleData
- example output

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

callevib
Starting Member

5 Posts

Posted - 2009-03-26 : 07:50:10

Hope i have explained clear enough.
I ask two ?.
Can any answer my first?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-26 : 08:04:00

You told us about 3 tables
Address
Activites
Adr_akt

You have posted a query that uses none of them.

So I think that is not explained clear enough.

Sorry
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

callevib
Starting Member

5 Posts

Posted - 2009-03-26 : 08:30:53

Ok you are right.
I have 3 tabels. The right names are:

dbo.Adresser: with
kndAdresseId
kndNavn
etc.

dbo.Aktivitet: with
aktId
aktNavn
aktAktiv
etc:

dbo.Knd_akt: with
kndAdresseid
aktId

I do not use the table Aktivitet in my query.

The names akt1,akt2.. in my query are just labels. The right will be to use aktName from the table Aktivitet, and only seek aktID in Knd_akt for aktNavn with aktAktiv = true.

A query in dbo.knd_akt gives the following:

1241 1
3345 3
3315 2
1762 1
1766 1
1762 3
1762 4
1766 3
1788 1
2563 3

And a query in dbo.aktivitet looks like:
aktId aktNavn Opretdate aktAktiv
1 aktTest1 2009-01-21 00:00:00.000 1
2 aktTest2 2009-01-22 00:00:00.000 0
3 aktTest3 2009-01-26 00:00:00.000 1
4 aktTest4 2009-01-26 15:16:02.783 1

Hope that helpes

callevib
Starting Member

5 Posts

Posted - 2009-03-26 : 10:44:40

Back again!

I have solved my 2. question - the query is now dynamic
The query looks like:

declare @navne nvarchar(2000) declare @ider nvarchar(2000)
select @ider = aktId,
@navne = COALESCE(@navne + ', ' + aktnavn +' = case ka.aktid when ' + @ider + ' then 0 else 1 end' , '' + aktnavn +
' = case ka.aktid when ' + @ider + ' then 1 else 0 end')
from aktivitet
where aktaktiv = 'TRUE' order by aktid

declare @query1 nvarchar(4000)
set @query1 = N'select k.kndadresseid, k.kndnavn, ' + @navne + ' from adresser as k
full join knd_akt as ka
on k.kndadresseid = ka.kndadresseid'

execute(@query1)

And the output:
ID Name aktTest1 aktTest3 aktTest4
1241 Frede Hejlskov Jensen 1 1 1
1754 Arne Andersen 1 1 1
1755 Hans Aksel Andersen 1 1 1
1762 Hanne og Flemming Barrett 1 1 1
1762 Hanne og Flemming Barrett 0 0 1
1762 Hanne og Flemming Barrett 0 1 0
1766 Arne Brøgger Christiansen 0 0 1
1766 Arne Brøgger Christiansen 1 1 1
1767 Else Aagaard Christensen 0 0 1
1785 Cai Andersen Hoe 0 1 0
1788 Kim Hørslev 1 1 1
The only problem now is the double names??

callevib
Starting Member

5 Posts

Posted - 2009-03-26 : 11:01:58

The above script generate a query looking like:

select k.kndadresseid, k.kndnavn,
aktTest1 = case ka.aktid when 1 then 1 else 0 end,
aktTest3 = case ka.aktid when 3 then 0 else 1 end,
aktTest4 = case ka.aktid when 4 then 0 else 1 end
from adresser as k
full join knd_akt as ka
on k.kndadresseid = ka.kndadresseid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources