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

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

Re: Sql question

Show all headers | View raw

Yes when I used this answer
SELECT * FROM Employee WHERE EmpPartner = 3;

then Kihlman's SDQl was happy

//Tony

<rja.carnegie@gmail.com> skrev i meddelandet 
news:1f5aad6b-a99e-48c3-adde-5f72eea5793b@googlegroups.com...
> On Friday, 30 January 2015 19:14:38 UTC, Lennart Jonsson  wrote:
>> 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
>
> I wonder if you need to write the statement as a JOIN
> given that we know we're interested in "wives" of
> employee id = 3.  Why not just:
>
> SELECT * FROM Employee WHERE EmpPartner = 3;
>
> This is a strange problem in many ways.  One database
> seems to contain multiple firms and their employees,
> and the tax authorities get to use it as well?
> And it's implied that any possible wife is recorded
> in the employee table?
>
> The names - except for Frim - are more suited to
> a single company's database of employees and,
> possibly, dependents.  But the use of it is
> more like a table of IncomeTaxPayer and another
> of Employer.
>
> Since my country doesn't rely on employment
> health insurance, and practises personal data
> protection, maybe I don't see how it is
> natural for a company to record all of an
> employee's family details and hand them to
> the government.
>
> Anyway, Employee 3 can only claim one EmpPartner -
> if that's the code that represents marriage,
> civil partnership, and other cohabitation for
> tax purpose, and if we now treat the Employee
> record as analogous to a tax form - and
> you'd expect the database to be designed
> to enforce integrity of the marriage relationship
> ...maybe.
>
> Enforcement would meant that only one Employee -
> one wife - can have EmpPartner = 3.
>
> I'm not sure how to write that rule.
>
> Anyway, maybe the database designer anticipated
> legal polygamy if Mitt Romney had won.
>
> Real life is more complicated; Employee 3 may
> have gotten divorced and then married in one
> tax year, I guess.  I don't know what that
> looks like on the form: we also have widely
> used tax-deducted-from-salary in my country.
>
> I also suspect it's obvious on the form
> anyway if you claim to have more than one
> wife.
>
> It's not a great fault if learn-simple-SQL
> teaching materials have the connection to
> reality of life events in Sims or Monopoly
> (when am I going to win a beauty contest??),
> but it makes it feel less real and less
> important - and, in this case, it's harder
> to translate the implausible question into
> an operation on the unrealistic database.
>
> If the question is: "Employee with id 3
> has been married more than once.  Find
> all information about his wife and his
> ex-wives", I think that may actually
> be clearer - and in fact it does require
> that you involve the record of employee 3
> to identify his current wife. 

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