Selecting employee with and without image

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 2008 Forums

Transact-SQL (2008)

Selecting employee with and without image

Author

Topic

dkekesi
Starting Member

38 Posts

Posted - 2010-09-13 : 10:42:53

Hi All,

I need your help in designing a query to accomplish the following task.
I have 2 tables: EmployeeData and EmployeeImage. There's a connection between the 2 tables using EmployeeID which is present in both tables. Each employee can have multiple pictures in the EmployeeImage table but each picture is related to only a single record in the EmployeeData table. The EmployeeImage table has a column called DefaultImage, which designates the default image for each employee. If an employee has image in the EmployeeImage table then there's always single default image (DefaultImage must be set to TRUE and can be set to TRUE only once for each employee).
I need to return a single row for each employee from the EmployeeImage table where the first column of the result set is the image where DefaultImage for that employee is set to TRUE in the EmployeeImage table. The problem is that some employees have no picture in the database, but I still need to display their data so that the first column (which should be the image) in the result set is set to NULL.

How do I accomplish this task?
Thanks for your help in advance.

Best Regards,
Daniel

Best Regards,
Daniel

DuncanP
Starting Member

12 Posts

Posted - 2010-09-13 : 10:58:59

If I've understood correctly it looks like you need a left join. Something like:


SELECT EmployeeData.EmployeeID, EmployeeImage.Image -- plus any other columns
FROM EmployeeData LEFT OUTER JOIN EmployeeImage
ON EmployeeData.EmployeeID = EmployeeImage.EmployeeID
AND EmployeeImage.DefaultImage = 'true' -- or 1 if bit column

You need the DefaultImage condition in the join rather than in a where clause since in a where clause it would filter out employees without images which you don't want.

Duncan

CSears
Starting Member

39 Posts

Posted - 2010-09-13 : 11:01:44

[code]SELECT * FROM
EmployeeData e
LEFT JOIN EmployeeImage i
ON e.EmployeeID = i.EmployeeID
WHERE ISNULL(i.DefaultImage, TRUE) = TRUE[/code]

Left joins allow the primary table to still have all it's records intact even if there are no records in the joining table. Having the i.DefaultImage = TRUE in the where clause should be fine since it will only filter out the rows that have DefaultImage set to false. Those would be the secondary images anyways :)

dkekesi
Starting Member

38 Posts

Posted - 2010-09-13 : 13:33:13

Hi DuncanP, CSears,

Thanks for your expert help, both of your solutions work fine. I knew that left join was needed I just had trouble setting the conditions right.

Best Regards,
Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources