cannot get parents records which do not exist in child table - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » cannot get parents records which do not exist in child table

cannot get parents records which do not exist in child table

Options

Previous Topic · Next Topic

gridview

Posted: Tuesday, September 02, 2008 9:19:44 PM

Rank: Newbie

Joined: 9/2/2008
Posts: 5
Points: 15

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?

Scott Whigham

Posted: Wednesday, September 03, 2008 11:13:46 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hi there

You seem to be confused about what you want. In your first sentence, you say that you want the orders (children) that have parents (employee table). Later you want parents with no children. I'm confused.

Tell me, in plain English, what you are wanting and let's see about starting from scratch with a new query Smile

gridview

Posted: Wednesday, September 03, 2008 7:37:15 PM

Rank: Newbie

Joined: 9/2/2008
Posts: 5
Points: 15

Scott Whigham wrote:

Hi there

Thanks for your reply.

I am working on this query since a long time and I donot know why I am still not able to figure it out.

Here is what I want -

1. To get distinct fields from order table (child table) associated
with employee table (parent table) based on the latest 'Id' field of child table.

2. But there are few records in parent table which do not have associated records in child table.
I want to get distinct fields from employee table (parent table) as well.

Please let me know if I still do not make sense.

Here is sample of tables (there are around 742 rows in parent table which do not have entry in child table) and data I want -

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.

Scott Whigham

Posted: Thursday, September 04, 2008 6:20:15 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Code:

SELECT e.EmployeeId, o.MostRecentOrder AS OrderId, e.Name, o2.ChangeDate, o2.FollowUpDate
FROM Employee e LEFT JOIN
(
  SELECT EmployeeId, MAX(OrderId) AS MostRecentOrder
  FROM [Order]
  GROUP BY EmployeeId
) AS o
  ON e.EmployeeId = o.EmployeeId
LEFT JOIN [Order] o2
  ON o.MostRecentOrder = o2.OrderId

You want Employees to be the driving table since you want to see "Employees with no orders". The derived table gives you the most recent orderId for each employee. This query requires [Order] to be present twice - one to get the most recent orderId and again to get the date info.

gridview

Posted: Tuesday, September 09, 2008 11:17:22 PM

Rank: Newbie

Joined: 9/2/2008
Posts: 5
Points: 15

Thanks Scott, i will try the below query and let you know.

Scott Whigham wrote:

Code:

gridview

Posted: Sunday, September 21, 2008 10:18:19 AM

Rank: Newbie

Joined: 9/2/2008
Posts: 5
Points: 15

Hi Scott,

thanks, it worked.

Scott Whigham wrote:

Code:

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.