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

[Marc]

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, ValueOfParamCli ent, ValueOfParamSal e

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

[Simon Hayes]

"Marc" <pa*******@free .fr> wrote in message
news:pk******** *************** *********@4ax.c om...

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, ValueOfParamCli ent, ValueOfParamSal e

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

[Hugo Kornelis]

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, ValueOfParamCli ent, ValueOfParamSal e

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)

[Marc]

On Fri, 14 Jan 2005 12:10:39 +0100, Hugo Kornelis
<hugo@pe_NO_rFa ct.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, ValueOfParamCli ent, ValueOfParamSal e

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

[Louis Davidson]

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 - dr***@hotmail.c om
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" <pa*******@free .fr> wrote in message
news:o6******** *************** *********@4ax.c om...

On Fri, 14 Jan 2005 12:10:39 +0100, Hugo Kornelis
<hugo@pe_NO_rFa ct.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, ValueOfParamCli ent, ValueOfParamSal e

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