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
 General SQL Server Forums
 New to SQL Server Programming
 Plz help with joins in a query

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.. lol

Anyhow, 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, LastName


To 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, ENTEREDONDTM


Any 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.PERSONNUM
and 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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -