How can i combine 2 results - 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)

How can i combine 2 results

Author

Topic

dbaseBln
Starting Member

2 Posts

Posted - 2010-01-06 : 06:40:45

Hello together,

i am sorry, when my english is abit odd...i am from germany but I could'nt found any good german forums. I have a tiny problem with 2 select or better nested select statements. In an existing table could be stored same data values like 01.10.2010, and again 01.10.2010 and again and again. This field ist associated with an ID fild (Ses_ID), which is an auto increment field.

What I need is this:

How can I filter the same data values ([Ses_Beendet]) but get from the same data values the last made entry?

In order to get the last made entry with the same data values I have this snippet.

Select TOP 1* from [ALSHOME].[dbo].[Session_Fragebögen_Antworten]
where [Ses_Beendet] in(select [Ses_Beendet]
from [ALSHOME].[dbo].[Session_Fragebögen_Antworten] WHERE Pat_ID = 10
group by [Ses_Beendet], Ses_ID
having Count(*) >1) order by Ses_ID DESC;

The data with highest Ses_ID is the filterd value for the same datas. This works fine.

But not all datas have the same data values, so I use this snippet tu get all the other values.

select [Ses_Beendet], count(*) As Anzahl
from [ALSHOME].[dbo].[Session_Fragebögen_Antworten] WHERE Pat_ID = 10
group by [Ses_Beendet]
having count(*) < 2;

This part works correct too.

An now the question. How can I binding these both statements togehter, so I get all datas in one result set with the conditions above. It would be nice, if somebody has an idea.

Thanks and greetings

davidagnew37
Starting Member

33 Posts

Posted - 2010-01-06 : 06:53:49

you could use UNION or UNION ALL? ..but your result set would need to have the same output columns

davidagnew37
Starting Member

33 Posts

Posted - 2010-01-06 : 06:54:43

For example:

Select TOP 1* from [ALSHOME].[dbo].[Session_Fragebögen_Antworten]
where [Ses_Beendet] in(select [Ses_Beendet]
from [ALSHOME].[dbo].[Session_Fragebögen_Antworten] WHERE Pat_ID = 10
group by [Ses_Beendet], Ses_ID
having Count(*) >1) order by Ses_ID DESC;
UNION
select [Ses_Beendet], count(*) As Anzahl
from [ALSHOME].[dbo].[Session_Fragebögen_Antworten] WHERE Pat_ID = 10
group by [Ses_Beendet]
having count(*) < 2;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 06:55:54

[code]select reqd fields..
FROM
(
Select TOP 1* from [ALSHOME].[dbo].[Session_Fragebögen_Antworten]
where [Ses_Beendet] in(select [Ses_Beendet]
from [ALSHOME].[dbo].[Session_Fragebögen_Antworten] WHERE Pat_ID = 10
group by [Ses_Beendet], Ses_ID
having Count(*) >1)
)t1
LEFT JOIN
(
select [Ses_Beendet], count(*) As Anzahl
from [ALSHOME].[dbo].[Session_Fragebögen_Antworten] WHERE Pat_ID = 10
group by [Ses_Beendet]
having count(*) < 2
)t2
ON t2.[Ses_Beendet]=t1.[Ses_Beendet]
ORDER BY t1.Ses_ID DESC
[/code]

dbaseBln
Starting Member

2 Posts

Posted - 2010-01-06 : 08:40:38

Thank you very much for your hints...its works now...GREAT.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 08:49:52

Nice design: [Session_Fragebögen_Antworten]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 09:41:20

quote:
Originally posted by webfred

Nice design: [Session_Fragebögen_Antworten]

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

Sorry I didnt get that
Is that German?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 09:48:06

Yes, 'Fragebögen' is plural of 'questionnaire' in German.
I meant nice design because I always try not to use special chars like ö,ä,ü and so on

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 09:52:15

quote:
Originally posted by webfred

Yes, 'Fragebögen' is plural of 'questionnaire' in German.
I meant nice design because I always try not to use special chars like ö,ä,ü and so on

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

Oh Ok
Thanks for translation Webfred

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-06 : 10:05:54

Since we are on that topic, what is the special name again for letters with those types of characters?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 10:27:15

special character in english.
In German: ä,ö,ü = Umlaut (plural: Umlaute)
ß, &, /, \, etc = Sonderzeichen (plural also Sonderzeichen)

do you mean this?

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

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-06 : 10:35:50

Yes, thanks! I always forget those words. Appreciate it :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources