cannot get parents records which do not exist...

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)

cannot get parents records which do not exist...

Author

Topic

gridview
Starting Member

11 Posts

Posted - 2008-09-02 : 22:04:02

i am trying to get distinct records from order table (child table) associated
with employee table (parent table) based on the latest 'Id' field of child table-

select x.Oid, x.employeeID, o.changedate,e.followupdate,o.date from
(select max(o.id) as oId,eid from order o
left join employee e on e.id = o.employeeid
group by eid) x inner join order o on o.id=x.oid
order by x.eid

But the above query does not return the records which exists in parent table but not in child table.

Any suggestions ? what i am missing in my query?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-03 : 00:38:31

Provide sample data and your desired results.

gridview
Starting Member

11 Posts

Posted - 2008-09-03 : 20:26:25

Table Employee -

ID Title Description
1 A xxxx
2 B yyyy
3 C zzzz
4 D aaaa
------------------------

Table Order -

ID EmployeeID ChangeDate Status

21 1 01/01/2008 M
22 1 01/02/2008 S
23 1 02/10/2008 E
24 2 01/01/2008 SM
25 3 01/01/2008 A
26 3 05/20/2008 R
27 3 09/01/2008 S
28 3 09/03/2008 M
---------------------------------------
I want the below result row set -
row1: 23,1,02/10/2008,E,xxxx
row2: 24,2,01/01/2008,SM,yyyy
row3: 28,3,09/03/2008,M,zzzz
row4: -,4,-,-,aaaa

Here - means blank.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-03 : 21:53:26

select od.description,coalesce(em.ID,'')as ID,
coalesce(Em.Orderid,4)as orderid,em.changedate,
coalesce(gm.status,'')as status from order od
left join(select orderid,max(id) as ID,max(changedate) as
changedate from employee
group by orderid)em
on od.ID =em.orderid
left join
(select max(status)as status,changedate from employee
group by changedate)gm
on em.changedate =gm.changedate

gridview
Starting Member

11 Posts

Posted - 2008-09-03 : 23:10:37

Thanks for your reply.

actually when I posted the sample table. I somehow labelled table names incorrectly, please excuse me for that.

I am not sure whether your query need to be updated after the above correction.

Anyways, in your below query,I do not understand this part -
coalesce(Em.Orderid,4)?

The table which I have provided is just a small smaple there can be numerous parent records which are not associated with child table.

quote:
Originally posted by sodeep

select od.description,coalesce(em.ID,'')as ID,
coalesce(Em.Orderid,4)as orderid,em.changedate,
coalesce(gm.status,'')as status from order od
left join(select orderid,max(id) as ID,max(changedate) as
changedate from employee
group by orderid)em
on od.ID =em.orderid
left join
(select max(status)as status,changedate from employee
group by changedate)gm
on em.changedate =gm.changedate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:26:45

[code]SELECT t.ID,
t.EmployeeID,
t.ChangeDate,
t.Status,
t.Description
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY o.ChangeDate DESC) AS RowNo,
o.ID,
o.EmployeeID,
o.ChangeDate,
o.Status,
e.Description
FROM Order o
INNER JOIN Employee e
ON e.ID=o.EmployeeID
)t
WHERE t.RowNo=1[/code]

gridview
Starting Member

11 Posts

Posted - 2008-09-10 : 00:14:41

Thanks for your reply.

But when I run the below query, I get this error -
No function called ROW_NUMBER()

I have never used this function so i have no idea....

quote:

Originally posted by visakh16

SELECT t.ID, 
t.EmployeeID, 
t.ChangeDate, 
t.Status,
t.Description
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY o.ChangeDate DESC) AS RowNo,
o.ID, 
o.EmployeeID, 
o.ChangeDate, 
o.Status,
e.Description
FROM Order o
INNER JOIN Employee e
ON e.ID=o.EmployeeID
)t
WHERE t.RowNo=1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 00:18:37

Are you using SQL Server 2005 or is your database compatibility level set at 90 ?

KH
[spoiler]Time is always against us[/spoiler]

gridview
Starting Member

11 Posts

Posted - 2008-09-10 : 12:23:20

I am using SQL Server 2005.

How can i check the database compatibility level?

quote:
Originally posted by khtan

Are you using SQL Server 2005 or is your database compatibility level set at 90 ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources