From: johansson.andersson@telia.com   
      
   Yes when I used this answer   
   SELECT * FROM Employee WHERE EmpPartner = 3;   
      
   then Kihlman's SDQl was happy   
      
   //Tony   
      
    skrev i meddelandet   
   news:1f5aad6b-a99e-48c3-adde-5f72eea5793b@googlegroups.com...   
   > On Friday, 30 January 2015 19:14:38 UTC, Lennart Jonsson  wrote:   
   >> On 01/30/2015 12:29 PM, Tony Johansson wrote:   
   >> > I practiceing sql question by using Kihlman's SQL webbsite   
   >> > pk = primary key   
   >> > fk = forign key   
   >> >   
   >> > I have these tables   
   >> > Firm(FirKey, FirName) pk is FirKey   
   >> > Salary(SalWages, SalTaxYear, FirKey, EmpId) pk is (FirKey, EmpId,   
   >> > SalTaxYear)   fk is FirKey and EmpId   
   >> > Employee(EmpId, EmpFirstName, EmpLastName, EmpPartner,TaxId,   
   >> > EmpLastTaxRaid)   
   >> > pk is EmpId    fk is TaxId, EmpPartner   
   >> > TaxDistrict(TaxId, TaxName, TaxPercentage) pk is TaxId   
   >> >   
   >> > The relation is many to many between Firm and Employee so we get an   
   >> > extra   
   >> > relation table in between that we have named Salary   
   >> > We have relation one to many from TaxDistrict to Employee   
   >> > We have also a self join in table Employee for column EmpPartner so   
   >> > EmpPartner will become a forign key here I suppose   
   >> >   
   >> > Firm (1) ........(N)Salary(N) .............. (1) Employee(N)   
   >> > ................(1)TaxDistrict   
   >> >   
   >> > Here we have a self join   
   >> > Employee.EmpId (1) .................(N) Employee.EmpPartner   
   >> >   
   >> > I want to write a SQL question that will give a result that satisfy   
   >> > this   
   >> > question   
   >> > The tax authority suspect person with id number 3 to be a bigamist!   
   >> > Find all   
   >> > information in the Employee table about all his wifes.   
   >> >   
   >> > I tries with this   
   >> > SELECT * FROM Employee a1 Employee a2 WHERE a1.EmpId = a2.EmpPartner   
   >> > AND   
   >> > a2.EmpId = 3;   
   >> >   
   >>   
   >> I suspect you mean:   
   >>   
   >> SELECT * FROM Employee a1 , Employee a2 WHERE a1.EmpId = a2.EmpPartner   
   >>                           ^^^   
   >> AND a2.EmpId = 3;   
   >>   
   >> However I would suggest you use an explicit join:   
   >>   
   >> SELECT a1.*  -- I assume you are only interested in a1   
   >> FROM Employee a1   
   >> JOIN Employee a2   
   >>      ON a1.EmpId = a2.EmpPartner   
   >> WHERE a2.EmpId = 3;   
   >>   
   >> /Lennart   
   >   
   > I wonder if you need to write the statement as a JOIN   
   > given that we know we're interested in "wives" of   
   > employee id = 3.  Why not just:   
   >   
   > SELECT * FROM Employee WHERE EmpPartner = 3;   
   >   
   > This is a strange problem in many ways.  One database   
   > seems to contain multiple firms and their employees,   
   > and the tax authorities get to use it as well?   
   > And it's implied that any possible wife is recorded   
   > in the employee table?   
   >   
   > The names - except for Frim - are more suited to   
   > a single company's database of employees and,   
   > possibly, dependents.  But the use of it is   
   > more like a table of IncomeTaxPayer and another   
   > of Employer.   
   >   
   > Since my country doesn't rely on employment   
   > health insurance, and practises personal data   
   > protection, maybe I don't see how it is   
   > natural for a company to record all of an   
   > employee's family details and hand them to   
   > the government.   
   >   
   > Anyway, Employee 3 can only claim one EmpPartner -   
   > if that's the code that represents marriage,   
   > civil partnership, and other cohabitation for   
   > tax purpose, and if we now treat the Employee   
   > record as analogous to a tax form - and   
   > you'd expect the database to be designed   
   > to enforce integrity of the marriage relationship   
   > ...maybe.   
   >   
   > Enforcement would meant that only one Employee -   
   > one wife - can have EmpPartner = 3.   
   >   
   > I'm not sure how to write that rule.   
   >   
   > Anyway, maybe the database designer anticipated   
   > legal polygamy if Mitt Romney had won.   
   >   
   > Real life is more complicated; Employee 3 may   
   > have gotten divorced and then married in one   
   > tax year, I guess.  I don't know what that   
   > looks like on the form: we also have widely   
   > used tax-deducted-from-salary in my country.   
   >   
   > I also suspect it's obvious on the form   
   > anyway if you claim to have more than one   
   > wife.   
   >   
   > It's not a great fault if learn-simple-SQL   
   > teaching materials have the connection to   
   > reality of life events in Sims or Monopoly   
   > (when am I going to win a beauty contest??),   
   > but it makes it feel less real and less   
   > important - and, in this case, it's harder   
   > to translate the implausible question into   
   > an operation on the unrealistic database.   
   >   
   > If the question is: "Employee with id 3   
   > has been married more than once.  Find   
   > all information about his wife and his   
   > ex-wives", I think that may actually   
   > be clearer - and in fact it does require   
   > that you involve the record of employee 3   
   > to identify his current wife.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)