Hi,
I have 3 tables commissions , XFR, Accounts.

Cust ID in commissions is the same as [Sol ID] in Accounts. the link
between XFR and Accounts is UID , which is present in both, but is not
consistent.

I mean there are 3811 records in commissions , 37567 records in XFR ,
64,567 in Accounts.

I need a result which has all the rows in commissions table ,
irrespective of whether there are matches in XFR.
but XFR and Accounts should match on the common column , UID.

So it will be a Left outer join with commissions and XFR and an Inner
join with XFR and Accounts.

The problem is I get the error JOIN EXPRESSION NOT SUPPORTED.

If I try to make the XFR-Accounts link a WHERE clause, it gives me a
syntax error.

This is my query

SELECT
commissions.ID, commissions.City, XFR.UID,
Accounts.[ParentAccountName]
FROM
commissions
LEFT JOIN XFR ON commissions.[Cust ID] = XFR.[Sol ID]
INNER JOIN Accounts ON XFR.UID = Accounts.UID
;

Please help me.
Thanks,
Aparna

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

So you have two names for the same data element in violation of
ISO-11179 and good database design ...


I do not know your industry, UID better not be a GUID or other exposed
physical locator; that would give you inconsistent data. Wait, you
say you already have inconsistent data! Did I guess right?


That is an OUTER JOIN, preserving Commissions ...

That is an INNER JOIN done before the OUTER JOIN -- use parens. Joins
are done in left to right order in SQL-92.

What SQL are you using? That might be more important than the count
of rows in tables to us..

The syntax looks fine, even if the data element names are a mess --
proprietary sqaure brackets, embedded spaces, etc.

SELECT
Commissions.id, Commissions.city, XFR.uid,
Accounts.parent_account_name
FROM (Commissions
LEFT OUTER JOIN
XFR
ON commissions.cust_id = XFR.sol_id)
INNER JOIN
Accounts
ON XFR.uid = Accounts.uid;

But did you mean:

SELECT
Commissions.id, Commissions.city, XFR.uid,
Accounts.parent_account_name
FROM Commissions
LEFT OUTER JOIN
(XFR
INNER JOIN
Accounts
ON XFR.uid = Accounts.uid)
ON commissions.cust_id = XFR.sol_id;

I'm not too hip on whether Access supports in-line tables, but try this one out...
SELECT commissions.ID
, commissions.City
, x.UID
, x.[ParentAccountName]
FROM commissions
LEFT JOIN
(
SELECT Accounts.[Sol ID] as [Cust ID]
, XFR.UID
, ACCOUNTS.[ParentAccountName]
FROM XFR
INNER JOIN Accounts
ON XFR.UID = Accounts.UID
) x
ON commissions.[Cust ID] = X.[Cust ID]
;


aparna126 (AT) rediffmail (DOT) com (Aparna) wrote in message news:<9265ae80.0402261102.5b62b17b (AT) posting (DOT) google.com>...

Hi,

Sorry to keep everyone guessing. I forgot to include what I was
working with.
This is with Access 97. I dont know what SQL I am using.(I dont know
how to find that out.) I guess whatever Access allows me.

I am not much experienced with databases, but I can write queries and
I did study computer engineering for 4 years. . Last 2 years I was
doing Application programming,with some SQL programming thrown in
occassionally.

I have some query to fix which somebody else wrote. I dont know where
the data came from. I only know that it is inconsistent because there
are as many databases being used here as there are departments.
Databases are abused here. Even commonsense is not followed. So I
guess adhering to Relational database design is last in their
priorities in this case.
no kidding. I am not complaining, but that is the situation.

Thanks D.McMunn for your help, it seems that inline tables are not
supported in Access.

And Celko, sorry to confuse you. I guess confusion is like "Cabin
Fever".
I tried with parantheses,but it gives me JOIN expression not supported
error.
I tried many different combinations and orders, but no luck.

Also, keys, constraints, referntial integrity etc will be irrelevant
in this case, because this person got this data from some spreadsheet.
he just imported some info and named them into tables. this is kind-of
a sample database which he has pulled up from different spreadsheets.
His end goal is to use this for reporting purposes.

Anyway the good news is, I dont have to do this. The person who wanted
me to fix his query said, I dont have to do it any more. It is much
easier for me if he gives me the original data sources and tells me
what all he needs for his final resultset. I have been doing that
successfully for last 2 weeks, and he was happy. Now I am in involved
in another task, and I guess he tried to do some SQL prgrming by
himself.

I have nothing against that guy, believe me, he is a nice person.But
yesterday, I just wished he hadnt tried SQL prgming.

Anyways, I am going to do some digging into SQL books to find out the
answer, at leisure.

Thanks a lot,
Aparna