Where comparison on 2 seperate joined tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Where comparison on 2 seperate joined tables?

Author

Topic

HiroEX
Starting Member

4 Posts

Posted - 2011-04-29 : 05:48:52

I'm a bit new to SQL so hopefully this isn't too much of a silly/confusing question :)

OK this is a bit of a silly concept but i'm more just trying to learn what to do in this situation.
I have 3 tables

Project (Name, FK_VersionID)
Versions (PK_VersionID, Version)
Employees (EmpName, FK_VersionID)

Each project has 1 version
and
Each Employee is working on 1 version

What would the sql query be in the update trigger be if every time a project changed versions that it would return all the employees working on a version range (+-0.X) close to the updated value.

So for example
Project Table joined with Version table might be
ProjectName, Version Number (joined table using inner join, otherwise its just an autoincrement integer for FK_VersionID)
'A','0.1'
'B','0.2'
'C','0.5'
'D','0.7'
'D','1.0'

Employee Table joined with Version table might be
Employee Name, Version Number (joined table using inner join, otherwise its just an autoincrement integer for FK_VersionID)
'Steve','0.1'
'Liz','0.2'
'John',0.5'
'Mike',0.7'
'Kerry','1.0'

so if project 'A' changed versions to '0.6'
the query would have a fixed version range (in this example +- 0.2) and return 'Steve', 'John', 'Mike'

Ugh I know its kind of confusing and a really ridiculous example (please don't try to make sense of the purpose/schema :P) and any help would be greatly appreciated! :)
Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-29 : 06:04:59

1. Please give exact table structures.
2. Please give all needed example data (here at least the versions are missing).
3. Please give then needed result in relation to the sample data.
4. What do you mean when you say that an update trigger should return the employees???

No, you're never too old to Yak'n'Roll if you're too young to die.

HiroEX
Starting Member

4 Posts

Posted - 2011-04-29 : 06:27:16

1. Sorry thought I gave a general table structure, its a fictitious table structure so don't really have an exact table structure.
2. Also is the sample data I provided not enough?
Hows this

Project Table
ProjectName, FK_VersionID
'A',1
'B',2
'C',3
'D',4
'D',5

Version Table
PK_VersionID, Version
1, 0.1
2, 0.2
3, 0.5
4, 0.7
5, 1.0

Employee Table
Employee Name, FK_VersionID
'Steve',1
'Liz',2
'John',3
'Mike',4
'Kerry',5

3. So if project 'A' changed versions to '0.6'
the query would have a fixed version range (in this example +- 0.2) and return 'Steve', 'John', 'Mike'

4. When I say "an update trigger" I mean I'd want this sql code to give me values in a "CREATE TRIGGER TrigProjectUpdate ON PROJECT AFTER UPDATE"
Thanks

HiroEX
Starting Member

4 Posts

Posted - 2011-04-29 : 06:31:10

I think where I'm trying to get at is, how do i do a WHERE query comparing 2 tables where each one is an inner joined table
For example (this isnt real code)

So if project 'A' changed versions to '0.6'

(SELECT * FROM Inserted(projects)
INNER JOIN Versions as V
ON Projects.FK_VersionID = V.PK_VersionID) AS ProjVersions

(SELECT * FROM Employees
INNER JOIN Versions as VE
ON Employees.FK_VersionID = VE.PK_VersionID) as EmpVersions

Select EmpNames FROM EmpVersions
WHERE ProjVersions.Version <= EmpVersions.Version+0.2 and ProjVersions.Version >= EmpVersions.Version-0.2

would then return 'Steve', 'John', 'Mike'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources