September 30, 2003 at 2:33 am
Hello,
I have two table (employes and cars). One employee can have more then one car and in that case in cars table I will have two or more records with car data and emplID. I need to make select which will give result like :
ID Name CarNumber
1/001 John Smith DFV-123
1/002 John Smith DER-232
2/001 Frank Harrelson AGS-123
3/001 Marie King HSS-233
etc...
First number in ID is emlID and second is first, second, ... car that this employee
has in cars table, so for every car I need to have number like 001,002 depending how many cars employee has.
Any ideas how I can do it ?
Thanks,
Oliver
September 30, 2003 at 5:18 am
select CAST(e.EmpID AS varchar(8)) + '/' +
RIGHT('000' + CAST(
(SELECT COUNT(*)
FROM Cars
WHERE EmpID = e.EmpID AND CarNumber <= c.CarNumber)
AS varchar(3)),3) ECarID,
e.EmpName, c.CarNumber
FROM Employees e JOIN Cars c ON e.EmpID = c.EmpID
ORDER BY ECarID
--Jonathan
--Jonathan
September 30, 2003 at 8:00 am
Thanks, it works 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply