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

Re: Sql question

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	Lennart Jonsson <erik.lennart.jonsson@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Sql question
Date	Fri, 30 Jan 2015 20:14:35 +0100
Organization	A noiseless patient Spider
Lines	52
Message-ID	<magl5q$cfh$1@dont-email.me> (permalink)
References	<mafpue$d3c$1@dont-email.me>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding	7bit
Injection-Date	Fri, 30 Jan 2015 19:14:02 +0000 (UTC)
Injection-Info	mx02.eternal-september.org; posting-host="7e2da11d2f2fa04799963f04fc962890"; logging-data="12785"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/734zVQY+m7D4JlGNFtLtQ"
User-Agent	Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.1.0
In-Reply-To	<mafpue$d3c$1@dont-email.me>
Cancel-Lock	sha1:hqFUkAn2nUyZjHGnxxBJNUY5pew=
X-TagToolbar-Keys	D20150130201435607
Xref	csiph.com comp.databases.ms-sqlserver:1869

Show key headers only | View raw

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

[...]

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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