SQL Query

15 Sep 2006 11:37 AM

JP SIngh

I have two tables like

Contracts Table

Id LawyersName InitiatedBy
1 SING              PETE
2 SING              HAWT
3 HAWT            PETE
4 HAWT            PETE

ContractDependents Table

Id EMPNAME
1 SING
3 SING
2 HAWT
2 PETE

Contract Table is related to ContractDependents by ID field.

Contract tables hold contract details and contract dependents hold the
usernames of the people who have access to each record.

How would I write a SQL query to get all contracts that SING has access to
i.e.

Contact ID
1
2
3

For username HAWT it shold be
ContractID 2,3,4

I.e. how to pull all the unique records that the user has access.

thanks for your help

15 Sep 2006 12:05 PM

Karthik

Hi,

I couldn't understand your design all that well.... but based on your output
my query would be

Select Id as 'Id' from ContractDependents where EmpName = 'Sing'
UNION
Select Id as 'Id' from Contracts where LName = 'Sing'

Please note that this is not the most effective way to retrieve the data.

Regards,
Karthik

Show quoteHide quote

"JP SIngh" wrote:

> I have two tables like
>
> Contracts Table
>
> Id LawyersName InitiatedBy
> 1 SING              PETE
> 2 SING              HAWT
> 3 HAWT            PETE
> 4 HAWT            PETE
>
> ContractDependents Table
>
> Id EMPNAME
> 1 SING
> 3 SING
> 2 HAWT
> 2 PETE
>
> Contract Table is related to ContractDependents by ID field.
>
> Contract tables hold contract details and contract dependents hold the
> usernames of the people who have access to each record.
>
> How would I write a SQL query to get all contracts that SING has access to
> i.e.
>
> Contact ID
> 1
> 2
> 3
>
> For username HAWT it shold be
> ContractID 2,3,4
>
> I.e. how to pull all the unique records that the user has access.
>
> thanks for your help
>
>
>