need to update a view

[CesarePlay]

I need help updating a view. I got the first part right but the second part I not sure how to do.

The column I entered has data but this data comes from another table which stores the employees names.

The code is

Code: Select all

SELECT     dbo.AREA2.TITLE AS BU, dbo.AREA3.TITLE AS DEPT, dbo.AREA4.TITLE AS [Mine Section], dbo.AREA5.TITLE AS AOR, 
                      dbo._VFL_Observations.TITLE AS [VFL No], dbo._VFL_Observations.VFL_Description, dbo._VFL_Observations.PERFORMEDBY, 
                      dbo._VFL_Observations.OBSDATE, dbo._VFL_Observations.SAFETY, dbo._VFL_Observations.HEALTH, dbo._VFL_Observations.ENVIRONMENT, 
                      dbo._VFL_Observations.COMMUNITY, dbo._VFL_Observations.COMPLETED
FROM         dbo._VFL_Observations INNER JOIN
                      dbo.AREA5 ON dbo._VFL_Observations.AREA = dbo.AREA5.ID INNER JOIN
                      dbo.AREA4 ON dbo.AREA5.AREA_PARENT = dbo.AREA4.ID INNER JOIN
                      dbo.AREA3 ON dbo.AREA4.AREA_PARENT = dbo.AREA3.ID INNER JOIN
                      dbo.AREA2 ON dbo.AREA3.AREA_PARENT = dbo.AREA2.ID
WHERE     (dbo.AREA2.REC_COUNT = 0) AND (dbo.AREA3.REC_COUNT = 0) AND (dbo.AREA4.REC_COUNT = 0) AND (dbo.AREA5.REC_COUNT = 0)

I added dbo._VFL_Observations.PERFORMEDBY . This column needs to get data from the _Employees table linking by the EMP_ID field.

I need help for this.

I tried to use Google but google will not open.

[RuadRauFlessa]

Code: Select all

SELECT     dbo.AREA2.TITLE AS BU, dbo.AREA3.TITLE AS DEPT, dbo.AREA4.TITLE AS [Mine Section], dbo.AREA5.TITLE AS AOR, 
                      dbo._VFL_Observations.TITLE AS [VFL No], dbo._VFL_Observations.VFL_Description, dbo._VFL_Observations.PERFORMEDBY, 
                      dbo._VFL_Observations.OBSDATE, dbo._VFL_Observations.SAFETY, dbo._VFL_Observations.HEALTH, dbo._VFL_Observations.ENVIRONMENT, 
                      dbo._VFL_Observations.COMMUNITY, dbo._VFL_Observations.COMPLETED, dbo._VFL_Employees.*
FROM         dbo._VFL_Observations INNER JOIN
                      dbo.AREA5 ON dbo._VFL_Observations.AREA = dbo.AREA5.ID INNER JOIN
                      dbo.AREA4 ON dbo.AREA5.AREA_PARENT = dbo.AREA4.ID INNER JOIN
                      dbo.AREA3 ON dbo.AREA4.AREA_PARENT = dbo.AREA3.ID INNER JOIN
                      dbo.AREA2 ON dbo.AREA3.AREA_PARENT = dbo.AREA2.ID LEFT INNER JOIN
                      dbo._VFL_Employees ON dbo._VFL_Observations = dbo._VFL_Employees.EMP_ID
WHERE     (dbo.AREA2.REC_COUNT = 0) AND (dbo.AREA3.REC_COUNT = 0) AND (dbo.AREA4.REC_COUNT = 0) AND (dbo.AREA5.REC_COUNT = 0)

Also as a rule of thumb you can get more speed out of it by doing the below.

Code: Select all

SELECT     dbo.AREA2.TITLE AS BU, dbo.AREA3.TITLE AS DEPT, dbo.AREA4.TITLE AS [Mine Section], dbo.AREA5.TITLE AS AOR, 
                      dbo._VFL_Observations.TITLE AS [VFL No], dbo._VFL_Observations.VFL_Description, dbo._VFL_Observations.PERFORMEDBY, 
                      dbo._VFL_Observations.OBSDATE, dbo._VFL_Observations.SAFETY, dbo._VFL_Observations.HEALTH, dbo._VFL_Observations.ENVIRONMENT, 
                      dbo._VFL_Observations.COMMUNITY, dbo._VFL_Observations.COMPLETED, dbo._VFL_Employees.*
FROM         dbo._VFL_Observations WITH (NOLOCK) INNER JOIN
                      dbo.AREA5 WITH (NOLOCK) ON dbo._VFL_Observations.AREA = dbo.AREA5.ID INNER JOIN
                      dbo.AREA4 WITH (NOLOCK) ON dbo.AREA5.AREA_PARENT = dbo.AREA4.ID INNER JOIN
                      dbo.AREA3 WITH (NOLOCK) ON dbo.AREA4.AREA_PARENT = dbo.AREA3.ID INNER JOIN
                      dbo.AREA2 WITH (NOLOCK) ON dbo.AREA3.AREA_PARENT = dbo.AREA2.ID LEFT INNER JOIN
                      dbo._VFL_Employees WITH (NOLOCK) ON dbo._VFL_Observations = dbo._VFL_Employees.EMP_ID
WHERE     (dbo.AREA2.REC_COUNT = 0) AND (dbo.AREA3.REC_COUNT = 0) AND (dbo.AREA4.REC_COUNT = 0) AND (dbo.AREA5.REC_COUNT = 0)

[CesarePlay]

Ok. I can do that but what about getting the inner join for the employees table to work? All it gives at the moment is numbers but I need the names to appear.

Edit: Did not see new code. Will try it first.

[RuadRauFlessa]

Use the second query I posted and run it. You should find the name of your employee towards the end of the output cols.

Code: Select all

SELECT     dbo.AREA2.TITLE AS BU, dbo.AREA3.TITLE AS DEPT, dbo.AREA4.TITLE AS [Mine Section], dbo.AREA5.TITLE AS AOR, 
                      dbo._VFL_Observations.TITLE AS [VFL No], dbo._VFL_Observations.VFL_Description, dbo._VFL_Observations.PERFORMEDBY, 
                      dbo._VFL_Observations.OBSDATE, dbo._VFL_Observations.SAFETY, dbo._VFL_Observations.HEALTH, dbo._VFL_Observations.ENVIRONMENT, 
                      dbo._VFL_Observations.COMMUNITY, dbo._VFL_Observations.COMPLETED, dbo._VFL_Employees.*
FROM         dbo._VFL_Observations WITH (NOLOCK) INNER JOIN
                      dbo.AREA5 WITH (NOLOCK) ON dbo._VFL_Observations.AREA = dbo.AREA5.ID INNER JOIN
                      dbo.AREA4 WITH (NOLOCK) ON dbo.AREA5.AREA_PARENT = dbo.AREA4.ID INNER JOIN
                      dbo.AREA3 WITH (NOLOCK) ON dbo.AREA4.AREA_PARENT = dbo.AREA3.ID INNER JOIN
                      dbo.AREA2 WITH (NOLOCK) ON dbo.AREA3.AREA_PARENT = dbo.AREA2.ID LEFT INNER JOIN
                      dbo._VFL_Employees WITH (NOLOCK) ON dbo._VFL_Observations = dbo._VFL_Employees.EMP_ID
WHERE     (dbo.AREA2.REC_COUNT = 0) AND (dbo.AREA3.REC_COUNT = 0) AND (dbo.AREA4.REC_COUNT = 0) AND (dbo.AREA5.REC_COUNT = 0)

Note the

Code: Select all

dbo._VFL_Employees.*

in the select and the

Code: Select all

LEFT INNER JOIN dbo._VFL_Employees WITH (NOLOCK) ON dbo._VFL_Observations = dbo._VFL_Employees.EMP_ID

in the from section. This should cover your bases. You should however replace

Code: Select all

dbo._VFL_Employees.*

With only the cols you need.

[CesarePlay]

Ok Thanks. I had to change left inner join to inner join because left inner join did not want to work.

[Ron2K]

There is no such thing as a left inner join.

[RuadRauFlessa]

Arrrrrg sorry. But does the rest work for you?

[CesarePlay]

Arrrrrg sorry. But does the rest work for you?

Yes. It works fine thanks.I now must check that the data is correct.

[RuadRauFlessa]

There is no such thing as a left inner join.

Oh come on. I was in the middle of writing another query that uses outers so please don't mind the peanut gallery.

[CesarePlay]

Ok.

[Ron2K]

There is no such thing as a left inner join.

Oh come on. I was in the middle of writing another query that uses outers so please don't mind the peanut gallery.

I have a disturbing tendency to get my left outer joins and right outer joins mixed up, so don't worry.

[RuadRauFlessa]

Hey where is your ave? Oh wait mine is also still missing.

[Ron2K]

Hey where is your ave? Oh wait mine is also still missing.

Still trying to get them from the old server - there are 97 uploaded avatars that I can't access; FTP client returns code 550 with the message "permission denied". The old web host has so far failed to even acknowledge the problem, let alone fix it so that I can get those avvies off the old server and on to our new one (and you wonder why we moved?).

And now I suppose I'd better stop derailing this thread.

[CesarePlay]

For anyone interested I had to modify the top piece of the code to have the employees names show as well.

The code is now:

Code: Select all

SELECT     dbo.AREA2.TITLE AS BU, dbo.AREA3.TITLE AS DEPT, dbo.AREA4.TITLE AS [Mine Section], dbo.AREA5.TITLE AS AOR,
                      dbo._VFL_Observations.TITLE AS [VFL No], dbo._VFL_Observations.VFL_Description,  dbo._VFL_Observations.PERFORMEDBY + ' ' + dbo._EMPLOYEES.EMP_NAME + ' 

' + dbo._EMPLOYEES.EMP_SURNAME AS [Performed By], ,
                      dbo._VFL_Observations.OBSDATE, dbo._VFL_Observations.SAFETY, dbo._VFL_Observations.HEALTH, dbo._VFL_Observations.ENVIRONMENT,
                      dbo._VFL_Observations.COMMUNITY, dbo._VFL_Observations.COMPLETED, dbo._VFL_Employees.*

This is the new part I had to add in:

Code: Select all

 dbo._VFL_Observations.PERFORMEDBY + ' ' + dbo._EMPLOYEES.EMP_NAME + ' 

' + dbo._EMPLOYEES.EMP_SURNAME AS [Performed By], 

[Ron2K]

This is the new part I had to add in:

Code: Select all

 dbo._VFL_Observations.PERFORMEDBY + ' ' + dbo._EMPLOYEES.EMP_NAME + ' 

' + dbo._EMPLOYEES.EMP_SURNAME AS [Performed By], 

For future reference, it's almost always better to keep those as separate fields - let your application code perform the concatenation.

[CesarePlay]

This is the new part I had to add in:

Code: Select all

 dbo._VFL_Observations.PERFORMEDBY + ' ' + dbo._EMPLOYEES.EMP_NAME + ' 

' + dbo._EMPLOYEES.EMP_SURNAME AS [Performed By], 

For future reference, it's almost always better to keep those as separate fields - let your application code perform the concatenation.

Ok. I will bear that in mind. Is it possible to use application code on a dataviewer? This whole piece is only in sql not in vb.

[RuadRauFlessa]

For future reference, it's almost always better to keep those as separate fields - let your application code perform the concatenation.

Ok. I will bear that in mind. Is it possible to use application code on a dataviewer? This whole piece is only in sql not in vb.

He did say almost always.