From: erik.lennart.jonsson@gmail.com   
      
   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   
      
   [...]   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)