Lists: | pgsql-general |
---|
From: | Efrain Lopez <tecnomaya(at)cabsagt(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Hint for a query |
Date: | 2011-11-04 04:37:35 |
Message-ID: | 322305A5-94D7-443E-8955-9784261333EF@cabsagt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I have this tables
Table: Contact
IdContact
First Name
Second Name
… other columns
Table: Employee
IdEmployee
IdContact, related to Contact table
… other columns
Table: Salesman
IdSaleman
IdEmployee, if salesman is employee, related to Employee table
IdContact, if salesman is not an employee, related to Contact table
I need a query
Id Salesman - Second name - First name
But I can't figure how to do it, can someone can give advise?
Thanks
From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | Efrain Lopez <tecnomaya(at)cabsagt(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Hint for a query |
Date: | 2011-11-04 05:07:27 |
Message-ID: | BB292D59-CF88-4301-AC61-CCD66E2C9129@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Nov 4, 2011, at 0:37, Efrain Lopez <tecnomaya(at)cabsagt(dot)com> wrote:
> I have this tables
>
>
> Table: Contact
> IdContact
> First Name
> Second Name
> … other columns
>
> Table: Employee
> IdEmployee
> IdContact, related to Contact table
> … other columns
>
> Table: Salesman
> IdSaleman
> IdEmployee, if salesman is employee, related to Employee table
> IdContact, if salesman is not an employee, related to Contact table
>
>
> I need a query
>
> Id Salesman - Second name - First name
>
> But I can't figure how to do it, can someone can give advise?
>
> Thanks
>
>
SELECT + JOIN
The query you seek seems too simple that either you have not learned how to do simple SELECTS with multiple tables OR you are not clear regarding what is confusing you.
The documentation and other books/articles on basic SQL will help in the first case. Showing what work you have tried up to this point will help with the second.
Your tables also will be cumbersome to use as designed, but your immediate question is easily answered with the existing schema. In short the link to contact from salesperson is questionable since employee already links there. There is not enough context to provide alternatives but it is worth looking into.
David J.
From: | Uwe Schroeder <uwe(at)oss4u(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Efrain Lopez <tecnomaya(at)cabsagt(dot)com> |
Subject: | Re: Hint for a query |
Date: | 2011-11-04 06:52:39 |
Message-ID: | 201111032352.40630.uwe@oss4u.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> I have this tables
>
>
> Table: Contact
> IdContact
> First Name
> Second Name
> … other columns
>
> Table: Employee
> IdEmployee
> IdContact, related to Contact table
> … other columns
>
> Table: Salesman
> IdSaleman
> IdEmployee, if salesman is employee, related to Employee table
> IdContact, if salesman is not an employee, related to Contact table
>
>
> I need a query
>
> Id Salesman - Second name - First name
>
> But I can't figure how to do it, can someone can give advise?
>
> Thanks
Needless to say, this is bit of an odd table layout. You always end up at the
contact table, but the layout makes it harder to query. Personally I'd have a
foreign key from the contact table to the employee table, rather than the
employee table to the contact table - that would also eliminate the employee
foreign key in the salesman table. It would also allow you to just join the
salesman table to the contact table and then figure out if the contact is an
employee.
well, that said. Here's a quick one without a lot of deep thought...
select a.idsalesman, b.firstname, b.secondname from salesman a join contact b
on b.idcontact=a.idcontact union select c.idsalesman, d.firstname,
d.secondname from salesman c join employee e on e.idemployee=c.idemployee join
contact d on d.idcontact=e.idcontact
No guarantees though. It's midnight here and I had a long day...