You also need to alias your columns:
SELECT Client.IdClient, Sale.IdSale, PC.Value AS PC_Param_Value, PS.Value
as AS PS_Param_Value
FROM ((Client
INNER JOIN Sale
ON Sale.IdClient = Client.IdClient)
INNER JOIN Param AS PC
ON PC.IdParam = Client.Param)
INNER JOIN Param AS PS
ON PS.IdParam = Sale.Param
This gives you the ability to differentiate between the vaues when you are
using the result set.
--
----------------------------------------------------------------------------
Louis Davidson -
drsql@hotmail.com
SQL Server MVP
Compass Technology Management -
www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Marc" <pasdespam@free.fr> wrote in message
news:o6cfu010dk470erscujk550m2n43jmsdk8@4ax.com...[color=blue]
> On Fri, 14 Jan 2005 12:10:39 +0100, Hugo Kornelis
> <hugo@pe_NO_rFact.in_SPAM_fo> wrote:
>[color=green]
>>On Fri, 14 Jan 2005 10:26:16 +0100, Marc wrote:
>>[color=darkred]
>>>
>>>Hello,
>>>
>>>I'm not an expert in SQL, if you could help me for that little
>>>problem:
>>>
>>>I had tree simple tables with their fields:
>>>[Client] IdClient, Param
>>>[Sale] IdSale, IdClient, Param
>>>[Param] IdParam, Value
>>>
>>>How can I retrieve a recordset with this columns ?
>>>IdClient, IdSale, ValueOfParamClient, ValueOfParamSale
>>>
>>>The problem is that I can retrieve a Param for one table (Client or
>>>Sale) like this request :
>>>
>>>SELECT Client.IdClient, Sale.IdSale, Param.Value
>>>FROM
>>>(Client
>>>INNER JOIN Sale
>>>ON Sale.IdClient = Client.IdClient)
>>>LEFT JOIN Param
>>>ON Param.IdParam = Sale.Param
>>>
>>>But how can I retrieve the Param of the another table in a simple
>>>query ? (because I would also like that it works for access)[/color]
>>
>>Hi Marc,
>>
>>Something like this maybe?
>>
>>SELECT C.IdClient, S.IdSale, PC.Value, PS.Value
>>FROM Client AS C
>>INNER JOIN Sale AS S
>> ON S.IdClient = C.IdClient
>>INNER JOIN Param AS PC
>> ON PC.IdParam = C.Param
>>INNER JOIN Param AS PS
>> ON PS.IdParam = S.Param
>>
>>(untested)
>>
>>Best, Hugo[/color]
>
> Thanks very much Hugo !
> I can only test on access today and it works with a few changes (I'll
> test later on SqlServer)
>
> For information, the code that works with access :
> SELECT Client.IdClient, Sale.IdSale, PC.Value, PS.Value
> FROM ((Client
> INNER JOIN Sale
> ON Sale.IdClient = Client.IdClient)
> INNER JOIN Param AS PC
> ON PC.IdParam = Client.Param)
> INNER JOIN Param AS PS
> ON PS.IdParam = Sale.Param
>
> Marc[/color]
