Left Joins and Exists 1

[tshad]

I have an issue where I am looking for missing records. I can set up queries with Left Joins and look for nulls to tell if records are missing but Left Joins will also give you multiple records for parent. I only want to know if any record exists or not. The actual records are irrelevant here.

I just want to see if any record (showing the ID - any ID) or a null. If it has 3 records I don't want to see 3 records, I want to see only one and display one of the keys (doesn't matter which one). If there is a null, a message gets printed.

Here is a sample of what I am trying to do:

If OBJECT_ID('tempdb..#Employee') IS NOT NULL
	DROP TABLE #Employee

If OBJECT_ID('tempdb..#Name') IS NOT NULL
	DROP TABLE #Name

If OBJECT_ID('tempdb..#Phone') IS NOT NULL
	DROP TABLE #Phone

CREATE TABLE #Employee
(
	EmployeeID int
)

CREATE TABLE #Name
(
	NameID int,
	EmployeeID int,
	Name varchar(100)
)

CREATE TABLE #Phone
(
	PhoneID int,
	NameID int,
	PhoneNumber varchar(100),
	PhoneType varchar(20)
)

INSERT #Employee (EmployeeID) Values (1)
INSERT #Employee (EmployeeID) Values (2)
INSERT #Employee (EmployeeID) Values (3)
INSERT #Employee (EmployeeID) Values (4)

INSERT #Name (NameID, EmployeeID, Name) Values (1,1,'Tom')
INSERT #Name (NameID, EmployeeID, Name) Values (2,2,'Mary')
INSERT #Name (NameID, EmployeeID, Name) Values (3,4,'Joe')

INSERT #Phone (PhoneID, NameID, PhoneNumber, PhoneType) Values (1,1,'2129991234','Home')
INSERT #Phone (PhoneID, NameID, PhoneNumber, PhoneType) Values (2,2,'4159991111','Home')
INSERT #Phone (PhoneID, NameID, PhoneNumber, PhoneType) Values (3,2,'4158112222','Business')
INSERT #Phone (PhoneID, NameID, PhoneNumber, PhoneType) Values (4,2,'7149998888','Mobile')

SELECT e.EmployeeID, n.NameID, p.PhoneID
FROM #Employee e
LEFT JOIN #Name n
ON e.EmployeeID = n.EmployeeID
LEFT JOIN #Phone p
ON n.NameID = p.NameID

Result:

EmployeeID	NameID	PhoneID
1		1	1
2		2	2
2		2	3
2		2	4
3		NULL	NULL
4		3	NULL

What this tell me is:
Employee 1 has a name record and a phone.
Employee 2 has a name record and 3 phones.
Employee 3 had no name records and as such has no phones
Employee 4 has a name record but no phones.

What I want to see is:

EmployeeID	NameID	PhoneID
1		1	1
2		2	2
3		NULL	NULL
4		3	NULL

I don't care that employee 2 has 3 phones. I only want to know that the employee has at least one phone record.

How can I change my query to show this? I had thought of using "Exists" but that filter out the employee. I still want to see the employee.

Thanks,

Tom

 

[fredericofonseca]

SELECT e.EmployeeID
     , n.NameID
     , p.PhoneID
FROM #Employee e
LEFT JOIN #Name n
ON e.EmployeeID = n.EmployeeID
outer apply (select top 1 *
             from #Phone p
             where n.NameID = p.NameID 
             order by p.PhoneID)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[tshad]

That works with one small change:

SELECT e.EmployeeID
     , n.NameID
     , temp.PhoneID
FROM #Employee e
LEFT JOIN #Name n
ON e.EmployeeID = n.EmployeeID
outer apply (select top 1 *
             from #Phone p
             where n.NameID = p.NameID 
             order by p.PhoneID) temp

You have to add an alias to the apply and then use the alias in the select list.

I was curious why you used the ORDER BY in the apply?

I had also come up with another solution that wasn't what I was originally looking for but solves the issue as well.

SELECT e.EmployeeID, 
       n.NameID, 
      (
	SELECT count(*) 
	FROM #Phone p 
	WHERE p.NameID = n.NameID
      ) as PhoneRecs
FROM #Employee e
LEFT JOIN #Name n
ON e.EmployeeID = n.EmployeeID

This result looks like:

EmployeeID	NameID	PhoneRecs
1		1	1
2		2	3
3		NULL	0
4		3	0

So I can test on 0 instead of null.

Both solutions work. Not sure which is most efficient. Probably not enough to matter.

Thanks,

Tom

 

[Olaf Doschke]

> I had thought of using "Exists" but that filter out the employee.
No. Why? How did you apply exists?

Exists will have another problem, it'll only either list employees with or without phones, but the latter is what you want to know, isn't it? So did you mean that with filtering out the employees having phone? Yes, and that's fine, isn't it? Do you really need an overview list of all employees or a todo list with those needing a phone record (or even a real phone)?

This'll give you the todo list:

SELECT e.EmployeeID, n.NameID
FROM #Employee e
LEFT JOIN #Name n
ON e.EmployeeID = n.EmployeeID
WHERE NOT EXISTS (Select 1 FROM #Phone p WHERE n.NameID = p.NameID )

Of course data can also be outdated, eg numbers are assigned different already. But to check that fully you'd need the list with all joins, not just the first number. Maybe your real case differs, then the analogy is failing to reflect your needs.

Bye, Olaf.

 

[Olaf Doschke]

You can put the exist check into the filed list and have a bit field indicating phone yes/no:

SELECT e.EmployeeID, n.NameID,
CASE WHEN EXISTS (Select 1 FROM #Phone p WHERE n.NameID = p.NameID ) Then 1 Else 0 END as Phoneexists
FROM #Employee e
LEFT JOIN #Name n
ON e.EmployeeID = n.EmployeeID

Or you could group by employee to get a phone count:

SELECT e.EmployeeID, n.NameID, Count(PhoneID) as "Phone number Count"
FROM #Employee e
LEFT JOIN #Name n ON e.EmployeeID = n.EmployeeID
LEFT JOIN #Phone p ON  n.NameID = p.NameID
GROUP BY e.EmployeeID, n.NameID
ORDER BY e.EmployeeID, n.NameID

Count(PhoneID) will only count non null values. Count(*) would give a count of 1 for employess without phone. Inner join would fix that, but also elemniate the employee from the list.

Bye, Olaf.

 

[gk53]

Why not just simple?

SELECT e.EmployeeID, isnull(n.NameID,0) as NameID, count(p.PhoneID)
	FROM #Employee e
		LEFT JOIN #Name n
			ON e.EmployeeID = n.EmployeeID
		LEFT JOIN #Phone p
			ON n.NameID = p.NameID 
group by 	 e.EmployeeID, n.NameID
order by 	e.EmployeeID

 

[tshad]

All good ideas.

The exists won't do what I want to do as I do not want to filter out the employee in this example. This is just an example of what I am doing and this query would be used by another query.

I need all employees and some way to determine if they have at least one phone or no phone whether it be checking if Null or checking if 0.

Thanks,

Tom

 

[fredericofonseca]

I used the "order by" as the desired outcome had the lowest number of phone id of employee id 2 - only way to ensure that was the desired output was to have the order by - and yes I know you probably don't need it.

George example will work as well as mine for this small set of tables/fields - but... if you need other fields from the other tables, and IF the other tables may have more than 1 record per employee you may not wish to use a group by - in that case my solution is easier to code.

Performance wise it all depends on volumes and other settigns. might not be important in this case for either of the solutions presented.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

> I do not want to filter out the employee in this example
My second and third queries don't do that. Have you tried them?

And the last one giving the count of phone numbers is my favorite anyway, maybe also in the form gk53 then gave, which also converts NULL nameID to 0. The one you starred with the outer apply is over the top, too complicated for the simple thing you want to achieve.

Bye, Olaf.