select query - 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 2000 Forums

Transact-SQL (2000)

select query

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-25 : 10:43:51

I have two tables:
Students:
SSN Objective
123 Complete 121
234 Complete 123

Attendance
SSN ATTENDDATE
123 03/21/2007
123 03/22/2007
234 03/21/2007
234 03/22/20007

I have to join by SSN and in my final query I want to select SSN, objective, Attendate and should return only 4 rows and not 8 rows.
Below is the output I am looking for

Output:
SSN Objective AttendDate
123 Complete121 03/21/2007
123 Null 03/22/2007
234 Complete123 03/21/2007
234 Null 03/22/2007.
I want in this way for reporting purposes.

Thanks

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-25 : 10:47:03

What have you tried?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-25 : 10:48:32

select
S.ssn
a.attendate
s.objective
from students s
join attendance a
on s.ssn = a.ssn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 10:53:47

The only way for you to get 8 rows is by using old style join (non-ansi) and forget about join-bindings.
Try this

DECLARE @Students TABLE (SSN VARCHAR(3), Objective VARCHAR(20))

INSERT	@Students
SELECT	'123', 'Complete 121' UNION ALL
SELECT	'234', 'Complete 123'

DECLARE	@Attendance TABLE (SSN VARCHAR(3), ATTENDDATE DATETIME)

SET DATEFORMAT MDY

INSERT	@Attendance
SELECT	'123', '03/21/2007' UNION ALL
SELECT	'123', '03/22/2007' UNION ALL
SELECT	'234', '03/21/2007' UNION ALL
SELECT	'234', '03/22/2007'

SELECT		s.SSN,
		CASE
			WHEN y.SSN IS NULL THEN NULL
			ELSE s.Objective
		END AS Objective,
		a.AttendDate
FROM		@Students AS s
INNER JOIN	@Attendance AS a ON a.SSN = s.SSN
LEFT JOIN	(
			SELECT		SSN,
					MIN(AttendDate) AS minDate
			FROM		@Attendance
			GROUP BY	SSN
		) AS y ON y.SSN = a.SSN
			AND y.minDate = a.AttendDate
ORDER BY	s.SSN,
		a.AttendDate

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-25 : 10:55:20

[code]
declare @Students table (SSN int, Objective varchar(100))
insert into @students
select 123, 'Complete 121'
union all select 234, 'Complete 123'

declare @Attendance table (SSN int, ATTENDDATE datetime)
insert into @Attendance
select 123, '20070321'
union all select 123, '20070322'
union all select 234, '20070321'
union all select 234, '20070322'

select *
from @students s
join @attendance a
on s.ssn = a.ssn

SSN Objective SSN ATTENDDATE
----------- ---------------------------------------------------------------------------------------------------- ----------- -----------------------
123 Complete 121 123 2007-03-21 00:00:00.000
123 Complete 121 123 2007-03-22 00:00:00.000
234 Complete 123 234 2007-03-21 00:00:00.000
234 Complete 123 234 2007-03-22 00:00:00.000

[/code]

don't understand. it would 4 rows, not 8. and your expected output...

SSN Objective AttendDate
123 Complete121 03/21/2007
123 Null 03/22/2007 why would this be null?
234 Complete123 03/21/2007
234 Null 03/22/2007 why would this be null?

Em

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-25 : 10:56:44

Did you even run yours?
I get 4 rows.

DECLARE @Students TABLE (SSN VARCHAR(3), Objective VARCHAR(20))

INSERT @Students
SELECT '123', 'Complete 121' UNION ALL
SELECT '234', 'Complete 123'

DECLARE @Attendance TABLE (SSN VARCHAR(3), ATTENDDATE DATETIME)

SET DATEFORMAT MDY

INSERT @Attendance
SELECT '123', '03/21/2007' UNION ALL
SELECT '123', '03/22/2007' UNION ALL
SELECT '234', '03/21/2007' UNION ALL
SELECT '234', '03/22/2007'

select
S.ssn,
a.ATTENDDATE,
s.objective
from @students s
join @attendance a
on s.ssn = a.ssn

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-25 : 11:03:21

sorry I gave incorrect number of records...below is the rite now.

declare @students table
(
ssn varchar(9)
,objective varchar(20)
)

declare @attendance table
(
ssn varchar(9)
,attend datetime
)

insert into @students
select '123','complete 121' union all
select '123','complete 123' union all
select '234','complete 123' union all
select '234','complete 121'

insert into @attendance
select '123', '03/21/2007' union all
select '123', '03/22/2007' union all
select '123', '03/23/2007' union all
select '234', '03/21/2007' union all
select '234', '03/22/2007' union all
select '234', '03/23/2007'

select
s.ssn
,a.attend
,s.objective
from @students s
join @attendance a
on s.ssn = a.ssn

This returns 12 rows. I need only 6 rows based on the attend date and add the objective to that result set.

I think the solution that Peso gave will work. Thanks Peso

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-25 : 11:06:24

Which Objective?? The objective is at the student level and you have multiple objectives for the same student.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-25 : 11:08:28

execute the query that peso has replied...that is how I want

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-25 : 11:26:15

Peso...If I change the data in the table...I think your query is not working.

DECLARE @Students TABLE (SSN VARCHAR(3), Objective VARCHAR(20))

INSERT @Students
select '123','complete 121' union all
select '123','complete 123' union all
select '234','complete 123' union all
select '234','complete 121'

DECLARE @Attendance TABLE (SSN VARCHAR(3), ATTENDDATE DATETIME)

SET DATEFORMAT MDY

INSERT @Attendance
select '123', '03/21/2007' union all
select '123', '03/22/2007' union all
select '123', '03/23/2007' union all
select '234', '03/21/2007' union all
select '234', '03/22/2007' union all
select '234', '03/23/2007'

SELECT s.SSN,
CASE
WHEN y.SSN IS NULL THEN NULL
ELSE s.Objective
END AS Objective,
--,y.SSN
a.ATTENDDATE
FROM @Students AS s
INNER JOIN @Attendance AS a ON a.SSN = s.SSN
LEFT JOIN (
SELECT SSN,
MIN(at.ATTENDDATE) AS minDate
FROM @Attendance at
GROUP BY SSN
) AS y ON y.SSN = a.SSN
AND y.minDate = a.ATTENDDATE
ORDER BY s.SSN,
a.ATTENDDATE

The query should only return 6 rows as there are only 6 records in attendance table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 11:37:24

HOW?
What is the business rule to decide which @Student record to fetch?
The attendance table only tells us which date any given student were present.
Information about which objective for any given date is not stated.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 11:39:45

With the information you have given us this far, this is your best option

DECLARE @Students TABLE (SSN VARCHAR(3), Objective VARCHAR(20))

INSERT	@Students
SELECT	'123', 'Complete 121' UNION ALL
SELECT	'123', 'Complete 123' UNION ALL
SELECT	'234', 'Complete 123' UNION ALL
SELECT	'234', 'Complete 121'


DECLARE @Attendance TABLE (SSN VARCHAR(3), ATTENDDATE DATETIME)

SET DATEFORMAT MDY

INSERT	@Attendance
SELECT	'123', '03/21/2007' UNION ALL
SELECT	'123', '03/22/2007' UNION ALL
SELECT	'123', '03/23/2007' UNION ALL
SELECT	'234', '03/21/2007' UNION ALL
SELECT	'234', '03/22/2007' UNION ALL
SELECT	'234', '03/23/2007' 

SELECT DISTINCT	s.SSN,
		CASE
			WHEN y.SSN IS NULL THEN NULL
			ELSE s.Objective
		END AS Objective,
		a.ATTENDDATE
FROM		@Students AS s
INNER JOIN	@Attendance AS a ON a.SSN = s.SSN
LEFT JOIN	(
			SELECT		SSN,
					MIN(ATTENDDATE) AS minDate
			FROM		@Attendance
			GROUP BY	SSN
		) AS y ON y.SSN = a.SSN AND y.minDate = a.ATTENDDATE
ORDER BY	s.SSN,
		a.ATTENDDATE

E 12°55'05.25"
N 56°04'39.16"

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-25 : 12:07:02

The objectives are not tied to any date. I am trying to get result set such that it has ssn and attendance dates and kinda of like add new column with objectives to that result set. If there are 5 dates attended and only 2 objectives then result set should have 5 records and for 3 records the objective is null.

So is it not possible to write query without getting duplicates and without tying up the objective to a date???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 15:42:45

You do know how a JOIN works, right?

E 12°55'05.25"
N 56°04'39.16"

cognos79
Posting Yak Master

241 Posts

Posted - 2008-03-25 : 22:31:03

I know...the client came up with this crazy idea...I just wanted to make confirm with you guys and make sure this is not feasible to show in the report...Thanks all for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources