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

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

Re: Sql question

Show all headers | 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