Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1868

Sql question

Show all headers | View raw

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 get this error when I try the SQL question above.
Tables are wrong: The number of tables is correct but at least one table is 
not correct
Conditions are wrong: Conditions are expected but too many have been written

I also mean that according to the relation straucture an Employee can't have 
two EmpPartner because that would mean
that we have two EmpId with the same primary key

So can somebody tell me how I should write the SQL question to satisfy this. 
"The tax authority suspect person with id number 3 to be a bigamist! Find 
all information in the Employee table about all his wifes".
Obviously my SQL question is not correct according to how Kihlman's SQL want 
to have it.


//Tony

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Sql question "Tony Johansson" <johansson.andersson@telia.com> - 2015-01-30 12:29 +0100
  Re: Sql question Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2015-01-30 20:14 +0100
    Re: Sql question rja.carnegie@gmail.com - 2015-02-01 22:16 -0800
      Re: Sql question Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2015-02-02 08:45 +0100
      Re: Sql question "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-03 01:06 +0100
        Re: Sql question rja.carnegie@gmail.com - 2015-02-03 09:06 -0800

csiph-web