Return Single Row per student - 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)

Return Single Row per student

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-06-09 : 09:27:01

Hi,

I have 2 tables:
Student Table:
sid int identity
firstname varchar
lastname varchar

Parents Table
pid int identity
sid int (links to student table)
parenttype int (ie mother=1 or father=2)
education varchar
work varchar

I need the following result set
sid mom_work dad_work
123 no no

However, this is what I am getting now:
sid mom_work dad_work mo
123 no NULL
123 NULL no

This is my query:select
(ls.sid) ,

(case when beruf IS NULL and parenttype=1 then 'no' else ''end )education1,
(case when beruf IS NULL and parenttype=2 then 'no' else ''end)education2

from learningstuds ls
inner join parents on parents.sid=ls.sid
where ls.sid=102024

group by beruf,parenttype,education,ls.sid
order by ls.sid

from learningstuds ls
inner join parents on parents.sid=ls.sid
where ls.sid=102024

group by beruf,parenttype,education
order by ls.sid

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 09:40:52

SELECT m.sid, CASE WHEN m.beruf IS NULL THEN 'no' ELSE '' END AS mom_work
, CASE WHEN d.beruf IS NULL THEN 'no' ELSE '' END AS dad_work
FROM Parents m JOIN Parents d m.sid = d.sid
WHERE m.parent_type = 1 AND d.parent_type = 2

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-06-09 : 10:22:17

Thanks.
What happens though if I only have one parent_type for that student in parents table. In that case I won't receive any results for that student.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 10:27:02

What's the key of the Parents table?


   SELECT   s.sid
	  , CASE WHEN m.beruf IS NULL THEN 'no' ELSE '' END AS mom_work
	  , CASE WHEN d.beruf IS NULL THEN 'no' ELSE '' END AS dad_work
     FROM ( SELECT DISTINCT sid FROM Parents) AS s
LEFT JOIN   Parents m s.sid = m.sid
LEFT JOIN   Parents d s.sid = d.sid
    WHERE   m.parent_type = 1 
      AND d.parent_type   = 2

It's easier to help out if you supply all the info. Look at the link below my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-06-09 : 10:48:44

CREATE TABLE
CONSTRAINT [PK_Parents] PRIMARY KEY CLUSTERED
(
[ParentNo]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_Parents] UNIQUE NONCLUSTERED
(
[SID],
[ParentType]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Parents_Student] FOREIGN KEY
(
[SID][Parents] (
[ParentNo] [int] IDENTITY (1, 1) NOT NULL ,
[SID] [int] NOT NULL ,
[ParentType] [tinyint] NOT NULL ,

[education] [smallint] NULL ,
[beruf] [smallint] NULL ,
) REFERENCES [Student] (
[SID]
)

) ON [PRIMARY]
GO

CREATE TABLE [Student] (
[SID] [int] IDENTITY (1, 1) NOT NULL ,
[SFirstName] [varchar] (50) COLLATE Hebrew_CI_AS NOT NULL ,
[SFamilyName] [varchar] (50) COLLATE Hebrew_CI_AS NOT NULL ,
[STZ] [numeric](10, 0) NOT NULL ,
[family] [smallint] NULL ,
[NoChildArmy] [tinyint] NULL ,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[SID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

It is possible that the student only has one parent entered in parents table.
Even if he has an entry only for mother and she does have a job (beruf)or even if she doesn't but no entry for father then
i still want to see the row for that studend in my results. Only if a student doesn't have any entry in parents then i don't need to show that student.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 11:06:08

This should be it


   SELECT   s.sid
	  , CASE WHEN m.beruf IS NULL THEN 'no' ELSE '' END AS mom_work
	  , CASE WHEN d.beruf IS NULL THEN 'no' ELSE '' END AS dad_work
     FROM   Student s
LEFT JOIN   Parents m s.sid = m.sid
LEFT JOIN   Parents d s.sid = d.sid
    WHERE   m.parent_type  = 1 
      AND   d.parent_type  = 2
      AND ( m.beruf IS NOT NULL OR d.beruf IS NOT NULL)

EDIT: and beruf...that's German, unless I'm mistaking

Wo auf Deutsch sind Sie?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-06-09 : 11:23:25

You are a life saver. I was under so much pressure to finish this query. Thanks sooo much for all your kind help.
The only change that I made in your query is:
SELECT s.sid
, CASE WHEN m.beruf IS NULL THEN 'no' ELSE '' END AS mom_work
, CASE WHEN d.beruf IS NULL THEN 'no' ELSE '' END AS dad_work
FROM Student s
LEFT JOIN Parents m s.sid = m.sid and m.parent_type = 1 LEFT JOIN Parents d s.sid = d.sid AND d.parent_type = 2 WHERE

( m.beruf IS NOT NULL OR d.beruf IS NOT NULL)

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 11:36:16

~~Hmmm did that alter the results?~~

Of course it did..I believe my code forced it to be an equi join, instead of a left join.

Good for you.

If you posted the sample data like I suggested below...we could test the sql...

Anyway, Good luck.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-06-09 : 12:11:45

I read the weblog but I am not sure how to get the sample data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources