473,320 Members | 2,161 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Jul 23 '05 #1
4 1285

"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Bernie | last post by:
Greetings, I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call them parent, child1, and child 2. On parent, I create a view called item as follows: CREATE view Item as...
2
by: JMCN | last post by:
hi i need some advice on whether if it would be better to use an append query or an update query. here is the situation, i have linked another database table to my current database. then i...
3
by: G rumpy O ld D uffer | last post by:
This is probably a 'Low-Level' question to all the ACCESS experts but I've only been using ACCESS for a couple of weeks. I've been given 30+ (and counting) separate 'Weekly' Databases which all...
3
by: dk | last post by:
Hi all, Would appreciate some advice on the following: I am trying to speed up an Access database connected to a SQL Server back-end. I know I can use a pass-through query to pass the sql...
5
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this...
10
by: Marizel | last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I often find myself with a query which I'd like to reuse, but with a different datasource. These datasources generally have...
2
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code ...
6
by: lesperancer | last post by:
SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMonth) Between . And .)); tblMonthlyBooking is a sql server...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
9
by: Sinner | last post by:
Hi, I have a field name 'USER' in tableMAIN. How do I replace the user names with corresponding user names. I can do that in xl using vlookup but now I'm trying to find a way to do that in...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.