Select distinct rec from two tables

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)

Select distinct rec from two tables

Author

Topic

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-04-21 : 13:16:24

Help...

I have two tables - Emp and Emp_Img

Emp has two columns - EmpID (int) and Name (varchar)
Emp_Img has three columns - EmpImgID (int), EmpID (int) and ImgURL (varchar)

The EmpID in Emp_Img table is a foreign key point to the EmpID in Emp table.

The data in Emp table is -
1, 'Tom'
2, 'Joe'
3, 'Mary'

The data in Emp_Img table is -
1, 1, '/image/SpecialChar-1.jpg'
2, 1, '/image/SpecialChar-2.jpg'
3, 2, '/image/SpecialChar-2.jpg'
4, 3, '/image/SomeOtherImage-1.jpg'

I want one query to return me a resultset from these two tables. The condition is
A. If the word 'SpecialChar-1' exists in the ImgURL column for an employee, then return only '/image/SpecialChar-1.jpg'
B. If 'SpecialChar-1' doesn't exists for an employee, but 'SpecialChar-2' does, then return '/image/SpecialChar-2.jpg' for that employee.
C. If the word 'SpecialChar' doesn't exists for an employee, then return NULL.

The resultset would look like -

1, 'Tom', '/image/SpecialChar-1.jpg'
2, 'Joe', '/image/SpecialChar-2.jpg'
3, 'Mary', NULL

Thanks in advance,

George

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-21 : 13:49:08

Well, if your limited to thos 2 Values Then how about:


   SELECT e.EmpId
	, CASE WHEN x.imgCheck = 1 THEN '/image/SpecialChar-1.jpg'
	,      WHEN y.imgCheck = 2 THEN '/image/SpecialChar-2.jpg'
				 ELSE Null
	  END
     FROM	  Emp e
LEFT JOIN (SELECT EmpId, 1 AS imgCheck FROM Emp_Img Where ImgURL = '/image/SpecialChar-1.jpg') AS x ON x.EmpId = e.EmpId
LEFT JOIN (SELECT EmpId, 2 AS imgCheck FROM Emp_Img Where ImgURL = '/image/SpecialChar-2.jpg') AS y ON y.EmpId = e.EmpId

Brett

8-)

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-04-21 : 15:09:26

It's not limited to the 2 values. But your idea absolutely helps me alot. Thanks.

George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources