A query where two tables are linked to the same another table

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)

Thank for your help,

Marc"Marc" <pasdespam@.free.fr> wrote in message
news:pk2fu093c78fdmofrln1p3js488e7fkqn7@.4ax.com...
> 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)
> Thank for your help,
> Marc

It's not really clear - at least to me - what you're trying to do. It would
be best to post CREATE TABLE and INSERT statements to set up a test case,
along with the results you expect, as that will avoid confusion.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||On Fri, 14 Jan 2005 10:26:16 +0100, Marc wrote:

>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)

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 14 Jan 2005 12:10:39 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Fri, 14 Jan 2005 10:26:16 +0100, Marc wrote:
>>
>>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)
>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

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|||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...
> On Fri, 14 Jan 2005 12:10:39 +0100, Hugo Kornelis
> <hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
>>On Fri, 14 Jan 2005 10:26:16 +0100, Marc wrote:
>>
>>>
>>>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)
>>
>>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
> 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