March 28, 2017 at 2:28 pm
How to join Employee and Student tables with HR table, - using ID and date columns to join with effective dates of the HR; such that date is between HR job record effective dates.
EMployee table
EmpID
RecDate
Student Table
StdID
SevDate
HR table
HRID
VaFrmDate
VaToDate
March 28, 2017 at 2:45 pm
We need more info to respond, like; Do these tables have a relationship to each other? We would just be making a guess otherwise.
March 28, 2017 at 2:52 pm
mcfarlandparkway - Tuesday, March 28, 2017 2:28 PMHow to join Employee and Student tables with HR table, - using ID and date columns to join with effective dates of the HR; such that date is between HR job record effective dates.
EMployee table
EmpID
RecDateStudent Table
StdID
SevDateHR table
HRID
VaFrmDate
VaToDate
Please provide sample data scripts and expected output, explaining exactly why the output is correct.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 28, 2017 at 3:07 pm
all these 3 tables has relation on ID column; dates are used for validation purpose
March 28, 2017 at 3:16 pm
what results are you expecting from this.....?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 28, 2017 at 4:02 pm
If the columns ending with "ID" are the related columns:
SELECT
EmpID
, StdID
, HRID
, RecDate
, RecDate
, VaFrmDate
, VaToDate
FROM
EMployee
FULL JOIN
Student ON EmpID = StdID
FULL JOIN
HR ON StdID = HRID;
FULL JOIN or FULL OUTER JOIN returns all the data that matches or doesn't match. Not knowing which table is the parent I used this type of join. With more info a better solution could be posted.
March 29, 2017 at 12:35 am
mcfarlandparkway - Tuesday, March 28, 2017 2:28 PMHow to join Employee and Student tables with HR table, - using ID and date columns to join with effective dates of the HR; such that date is between HR job record effective dates.
EMployee table
EmpID
RecDateStudent Table
StdID
SevDateHR table
HRID
VaFrmDate
VaToDate
I assume that HR table is the parent table, and that not all HR is in EMployee AND Student.
Without more information and data to validate, my guess would be something like this.
SELECT
h.HRID
, h.VaFrmDate
, h.VaToDate
, e.RecDate
, s.SevDate
FROM HR AS h
LEFT JOIN EMployee AS e
ON h.HRID = e.EmpID
AND e.RecDate >= h.VaFrmDate
AND e.RecDate <= h.VaToDate
LEFT JOIN Student AS s
ON s.StdID = e.EmpID
AND s.SevDate >= h.VaFrmDate
AND s.SevDate <= h.VaToDate;
March 29, 2017 at 2:59 am
The assumption I've made is that HRID represents a Student OR an Employee. The employees represent the teaching staff, cleaners and caterers etc and the students are the actual pupils of the college. In this assumption the HRID is the StdID or the EmpID depending on which table is joined. On this basis there's not JOIN between the Employee and the Student table but both are joined to the HR table.
SELECT
h.HRID
, h.VaFrmDate
, h.VaToDate
, e.RecDate
, s.SevDate
FROM HR AS h
LEFT JOIN EMployee AS e
ON h.HRID = e.EmpID
AND e.RecDate >= h.VaFrmDate
AND e.RecDate <= h.VaToDate
LEFT JOIN Student AS s
ON s.StdID = e.HRID
AND s.SevDate >= h.VaFrmDate
AND s.SevDate <= h.VaToDate;
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 30, 2017 at 1:33 pm
mcfarlandparkway - Tuesday, March 28, 2017 3:07 PMall these 3 tables has relation on ID column; dates are used for validation purpose
What you posted is both incomplete and incorrect. A list of vague names is not a table! A table must have a key, by definition. This key cannot be a generic "id"; that is a belief in Kabbalah magic. Can your magical id also identify an octopus? An automobile? A pack of crackers?
Your choice of table names is also wrong. Unless you truly do have only one employee (this should have been personnel, the collective name for this set), likewise, do you really have only one student as you told us? Also I have no idea what HR is; who deliberately created SQL from the beginning to allow long names that you could be clear and eventually follow. What would become the ISO 11179 standards. If you get around to reading Dr. Codd or any good book on RDBMS, you run into Codd's information principle. It's one of his fundamental 12 laws. It says that all relationships are expressed as scalar values in the columns of rows of a table. Not by some vague narrative in a posting. Computers are very bad at implementing narrative.
Why did you go back and follow the forum rules (which you been in effect for over 30 years!) And post real DDL with keys that include references, or relationship tables? We would also like to see some sample data and expected results.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 30, 2017 at 2:26 pm
Say it ain't so Joe. Which ANSI Standard should the OP use? The OP seems to have difficulty with how to join tables even though he knows the related columns. It seem a stretch to expect him/her to understand what you're talking about. The naming of objects conforming to the plurality of the data set is a bit off topic and is a bone of contention however it isn't the hill I want to die on. I agree posting DDL for the tables, what you've tried so far and what result you expect should be posted for every question, that at least would help the OP to get more helpful and more responses. Remember Joe, not everyone is as much of a SQL Smarty as you are.
March 30, 2017 at 8:24 pm
jcelko212 32090 - Thursday, March 30, 2017 1:33 PMA table must have a key, by definition. This key cannot be a generic "id"; that is a belief in Kabbalah magic.
In all the time I've known you to spill such nonsense, you've never ever suggested what should be used as the primary key of an "employee" table (for just one of many examples) even though you've been asked dozens of times by me alone. Unless you can come up with a non arbitrary, immutable key to uniquely and certainly identify an employee for the life of an employee that doesn't also violate PII requirements, please stop publishing this falsehood.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2017 at 6:13 pm
The ANSI/ISO standards which you should use should begin with the current version of the SQL language. His vendor will probably not support everything, or will have some local dialect for standardized features (Oracle really bad about this!). You should be aware of these things, and not code in needless dialect.
After that, ISO 8601 for temporal data, since it actually is implemented by every vendor I know of. Then we have the ISO currency code, the ISO country codes, the entire metric system (did you really want to use the Potrzebie system of weights and measures?), And then look for industry standards.
At the metadata level, they need to read ISO 11179 and the metadata committee standards. This defines how you name data elements. These standards are actually being required by the US federal government! I don't if you ever had to go through a full audit on the DOD contract, but it is not pleasant.. Frankly not conforming to the to the standards is sort of like running into an idiot engineer who thinks it's just fine to use the Potrzebie system instead of the SI measurements. You know they are dangerously incompetent, or the system you got is horribly out of date.
I just wish people posting with at least read the forum's netiquette and try to match it. I'm afraid at this point what we get our lazy students (do my homework for me! Do my homework for me!) Or people who really don't know enough to post yet and there incompetent at their jobs. If they give a try. I be much happier
Please post DDL and follow ANSI/ISO standards when asking for help.
April 1, 2017 at 8:20 pm
Joe Torre - Thursday, March 30, 2017 2:26 PM
The ANSI/ISO standards which you should use should begin with the current version of the SQL language. His vendor will probably not support everything, or will have some local dialect for standardized features (Oracle really bad about this!). You should be aware of these things, and not code in needless dialect.
After that, ISO 8601 for temporal data, since it actually is implemented by every vendor I know of. Then we have the ISO currency code, the ISO country codes, the entire metric system (did you really want to use the Potrzebie system of weights and measures?), And then look for industry standards.
At the metadata level, they need to read ISO 11179 and the metadata committee standards. This defines how you name data elements. These standards are actually being required by the US federal government! I don't if you ever had to go through a full audit on the DOD contract, but it is not pleasant.. Frankly not conforming to the to the standards is sort of like running into an idiot engineer who thinks it's just fine to use the Potrzebie system instead of the SI measurements. You know they are dangerously incompetent, or the system you got is horribly out of date.
I just wish people posting with at least read the forum's netiquette and try to match it. I'm afraid at this point what we get our lazy students (do my homework for me! Do my homework for me!) Or people who really don't know enough to post yet and there incompetent at their jobs. If they give a try. I be much happier
... and people ranting about ISO standards and the use of "ID" columns without providing the alternative. What should the PK of an Employee table be, Joe?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2017 at 8:33 pm
Let's keep it civil. It looks like we have two sides that might be getting a bit agitated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2017 at 11:33 am
Jeff Moden - Thursday, March 30, 2017 8:24 PMjcelko212 32090 - Thursday, March 30, 2017 1:33 PMA table must have a key, by definition. This key cannot be a generic "id"; that is a belief in Kabbalah magic.In all the time I've known you to spill such nonsense, you've never ever suggested what should be used as the primary key of an "employee" table (for just one of many examples) even though you've been asked dozens of times by me alone. Unless you can come up with a non arbitrary, immutable key to uniquely and certainly identify an employee for the life of an employee that doesn't also violate PII requirements, please stop publishing this falsehood.
Did you ever have a freshman philosophy course? Where they went into Aristotelian logic and the foundations of Western thought. One of our principles is the Law of Identity, which has nothing to do with the Sybase proprietary table property of the same name. The law of identity gets summarized as "A is A" in the popular literature. It actually says that to be something is to be something in particular. To be nothing in particular or anything in general, is to not be at all. So the first question you would ask in the case of a database is, what is the role being played by an entity in a data model.
The reason I mention Kabbalah numbers in my postings is that this is the exact opposite of Western thought and mathematics. This asserts that God has given everything a number, which is written in Hebrew letters (they use the letters for digits, a really bad design that leads to ambiguity). If you know the magic number you gain magical powers over the entity. For example, one of the names of God is 216 letters long. This is essentially what you are asking this question about a universal magic employee or whatever name.
In data modeling were basically the descendants of Aristotle's genus and species model of knowledge, not Hebrew mysticism. This is why for decades, and in all my books, I said you need to find an appropriate standard encoding for the particular data element with which you are working in your particular data model, your particular database. I have then advocated that you look for industry standards, maintained outside your organization whenever possible. The few rare times that you actually need to invent your own encoding schemes. I've also had a good set of rules for how to design and encoding scheme.
The IDENTITY table property in T-SQL is an old UNIX left over. It counts the insertion attempts (not even successes) on one table, on one machine, and one particular release of one particular vendor's product (Microsoft) and has no universality at all. I could install the same schema on two separate machines, and the identity properties would get out of sync. What would force them to stay the same? This makes about as much sense as identifying an automobile by its parking space number in one particular garage assuming that for every space you park it again will get the same parking space number. This is obviously absurd and that's why the industry has a VIN number.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply