Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail
From: "Tony Johansson" <johansson.andersson@telia.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Sql question
Date: Fri, 30 Jan 2015 12:29:37 +0100
Organization: A noiseless patient Spider
Lines: 55
Message-ID: <mafpue$d3c$1@dont-email.me>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
Injection-Date: Fri, 30 Jan 2015 11:29:18 +0000 (UTC)
Injection-Info: mx02.eternal-september.org; posting-host="8fdc2b5af81c338deb7045fc6c39b0e2"; logging-data="13420"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19cFjq8qD4SSGMLTgCcv7oR"
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
Cancel-Lock: sha1:QrGwCrX9bBC3v68gA4pNOmJuPKs=
X-Priority: 3
X-MSMail-Priority: Normal
Xref: csiph.com comp.databases.ms-sqlserver:1868

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