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)