By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

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

P: n/a

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
Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"Marc" <pa*******@free.fr> wrote in message
news:pk********************************@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
Jul 23 '05 #2

P: n/a
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)
Jul 23 '05 #3

P: n/a
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
Jul 23 '05 #4

P: n/a
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.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" <pa*******@free.fr> wrote in message
news:o6********************************@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

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.