Author |
Topic |
DouglasH
Starting Member
2 Posts |
Posted - 2014-02-26 : 04:06:37
|
Hi all..I'm so new to SQL I don't even know what to call anything, so forgive me for my weird questions.. I'm still booked to do SQL officially.. also, my queries are a mess, but they work, for now.. lolAnyhow, wrote a query right, and need to get further information out of another table, so did a Join, but then also need to get further information out of another table..So, I need to add this query:SELECT PERSONNUM as EmployeeCode ,[FIRSTNM] as Firstname ,[LASTNM] as LastName ,[HOMELABORLEVELDSC1] as Site ,[HOMELABORLEVELNM1] as SiteID FROM [tkcsdb].[dbo].[VP_ALLPERSONV42]where USERACCOUNTSTATUS = 'active' and homelaborlevelnm1 in ('11', '13', '32') order by SiteID, LastNameTo be included in this query:SELECT a.PERSONNUM, a.PERSONFULLNAME, HOMELABORLEVELDSC1, [SERVER] ,[CLIENT] ,[CLIENTUSERNAME] ,[EVENTDATE] ,[ENTEREDONDTM] ,[TIMEINSECONDS] ,[DURATIONINSECONDS] ,[MONEYAMOUNT] ,[PAYCODENAME] ,[WORKRULENAME] ,[TIMESHEETITEMTYPE] ,[OVERRIDETYPE] --,HOMELABORLEVELDSC1 FROM [tkcsdb].[dbo].[VP_TIMECARDAUDIT] a inner join dbo.VP_EMPLOYEE on a.PERSONNUM = dbo.vp_employee.PERSONNUM where ENTEREDONDTM between '2014-01-01 00:00:00.000' and '2014-01-31 12:59:59.999' order by homelaborleveldsc1, clientusername, EVENTDATE, ENTEREDONDTMAny help is appreciated.Thanks,Doug |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-02-26 : 09:44:26
|
Something like:SELECT a.PERSONNUM, a.PERSONFULLNAME, HOMELABORLEVELDSC1, [SERVER] ,[CLIENT] ,[CLIENTUSERNAME] ,[EVENTDATE] ,[ENTEREDONDTM] ,[TIMEINSECONDS] ,[DURATIONINSECONDS] ,[MONEYAMOUNT] ,[PAYCODENAME] ,[WORKRULENAME] ,[TIMESHEETITEMTYPE] ,[OVERRIDETYPE] --,HOMELABORLEVELDSC1,[FIRSTNM] as Firstname ,[LASTNM] as LastName ,[HOMELABORLEVELDSC1] as Site ,[HOMELABORLEVELNM1] as SiteID FROM [tkcsdb].[dbo].[VP_TIMECARDAUDIT] a inner join dbo.VP_EMPLOYEE on a.PERSONNUM = dbo.vp_employee.PERSONNUM inner join [tkcsdb].[dbo].[VP_ALLPERSONV42] b on b.PERSONNUM=dbo.vp_employee.PERSONNUMand b.USERACCOUNTSTATUS = 'active'and b.homelaborlevelnm1 in ('11', '13', '32') where ENTEREDONDTM between '2014-01-01 00:00:00.000' and '2014-01-31 12:59:59.999' order by homelaborleveldsc1, clientusername, EVENTDATE, ENTEREDONDTM |
|
|
DouglasH
Starting Member
2 Posts |
Posted - 2014-02-27 : 01:12:03
|
Thanks bitsmed... this is great. I had to add "b." to the [FIRSTNM] and [LASTNM] fields, then remove the homelaborleveldsc1 as it was coming up with an ambiguous error.Now, this works, BUT, it returns the First name and Last name of the origial query, not the first name or last name corresponding to the personnum in the vp_allpersonv42 table.tnx for your help. |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-02-27 : 15:54:57
|
The reason why you had to put b. infront of FIRSTNM and LASTNM is, that those two field are in more than one table. The same goes for HOMELABORLEVELDSC1.Now the last part you wrote about, is probably the same thing. You are probably just refering to the "wrong" table, so you could try putting VP_EMPLOYEE. infront of FIRSTNM and LASTNM (if VP_EMPLOYEE is the table holding those two fields. |
|
|
|
|
|