SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Plz help with joins in a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DouglasH
Starting Member

2 Posts

Posted - 02/26/2014 :  04:06:37  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

433 Posts

Posted - 02/26/2014 :  09:44:26  Show Profile  Reply with Quote
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 - 02/27/2014 :  01:12:03  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

433 Posts

Posted - 02/27/2014 :  15:54:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000