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 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
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 - 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 ...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |