SUBQUERIES in the FROM clause with Access query

[Guest]

Hi all,

I have this long query that extract datas from 12 tables, in that main query
there are 8 subqueries, it work fine with pass-thru, but now this query need
to do as access query. I need to have all info from table1, and only info
from table2 which matched table1, only info from table3 which matched
table2....

Below is example of 4 tables only since I need to understand the way to
right subqueries with access on a multi-outer join,
Relationships:
Table1--->Table2--->Table3
|
v
Table4

Table1 fields: ID(primary key), code,Tb1_field2, Tb1_field3, Tb1_field4... etc
Table2 fields: ID(foreign key), seq_id, TB2_ID(FK to table 3), flag,
tb2_field1, tb2_field2 ...etc
table3 fields: TB3_ID(PK), tb3_field1,tb3_field2...etc
Table4 fileds: code(pk), code_desc

my pass-thru like this:

SELECT DISTINCT TABLE1.ID,TABLE1.Tb1_field2, TABLE1.Tb1_field3,
TABLE1.Tb1_field4, MYSUB.tb3_field1, MYSUB.tb3_field2, table4.code_desc
FROM TABLE1, table4,
(SELECT TABLE2.ID, TABLE2.code, TABLE2.role, TABLE2.flag,
TABLE3.tb3_field1, TABLE3.tb3_field2 WHERE TABLE2.role ='2' AND
TABLE2.flag='T' and TABLE3.TB3_ID = TABLE2.TB2_ID) MYSUB
WHERE TABLE1.ID = MYSUB.ID(+) and
table1.code = table4.code(+)

Can anyone help me how to put this in ACCESS query?

Thanks in advance!
AQ

 

[Tom Ellison]

Dear AQ:

I'd like to start with this:

SELECT T1.ID, T1.Tb1_field2, T1.Tb1_field3,
T1.Tb1_field4, M.tb3_field1, M.tb3_field2, T4.code_desc
FROM TABLE1 T1, table4 T4,
(SELECT T2.ID, T2.code, T2.role, T2.flag,
T3.tb3_field1, T3.tb3_field2
FROM ??????
WHERE T2.role ='2'
AND T2.flag = 'T'
AND T3.TB3_ID = T2.TB2_ID)
M
WHERE T1.ID = M.ID(+)
AND T1.code = T4.code(+)

Above, I have studied your code and reformatted it for my personal reading
preferences. I have also added aliasing which improves readability for me.

Here's what I see. I have added FROM ?????? at a point in the subquery
where a FROM clause is mandatory. It seems to be missing. Also, what's the
(+) business. To what database was this a working "pass-thru"? Perhaps
that explains the syntax differences, but I've never seen a database where a
FROM clause can be omitted without causing serious problems. And, again,
I'm not familiar with the (+) thing. Neither is Access Jet I expect.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

 

[Guest]

Hi Tom,

I used ODBC to connect to Oracle server, so (+) business is work as outer
join (Include ALL records from 'TABLE1' and only those records from 'TABLE2'
where the joined fields are equal.) in oracle or sql server.

In the example I wrote in my question before, i mean that SQL was work
completely well with pass-thru, I must missed out the FROM. But now I need
to re-write it using ACCESS SQL. I haven't write SQL in access db to join
lots of table, and sub-queries for a complex report. So I'm stuggling to
translate my old oracle (work) SQL to access sql.

How do you join 4 or more table, and add sub-queies in there?

Thanks for your answer

AQ

 

[Tom Ellison]

Dear AQ:

OK, you see, I can learn something from the newsgroups, too. You say this
(+) syntax is for SQL Server, too?

Here's what I think may work, then:

SELECT T1.ID, T1.Tb1_field2, T1.Tb1_field3,
T1.Tb1_field4, M.tb3_field1, M.tb3_field2, T4.code_desc
FROM (TABLE1 T1
LEFT JOIN table4 T4
ON T4.code = T1.code)
LEFT JOIN (SELECT T2.ID, T2.code,
T2.role, T2.flag, T3.tb3_field1, T3.tb3_field2
FROM ??? T2
INNER JOIN ??? T3 ON T3.TB3_ID = T2.TB2_ID
WHERE T2.role ='2'
AND T2.flag = 'T') M ON M.ID = T1.ID

I'm not terribly confident of this, but I've tried to interpret what you
explained the best I could.

Access has this terrible query syntax where joins must be parenthesized.
The parens perform no function. So, just thow some in there and try to
ignore them.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

 

[Guest]

Hi Tom,
Thanks for your quick reply!. I tried but it doesn't work. I guess b/c of
my language that make the situation not clear
Here is what I'm trying to get out of this sample (be4 I move on to add more
tables.)

Table Property is the main table (Property_id, P_Name, Address,...) P
Table Contact (Property_id, Cont_seq_id(PK), person_id, Role_id,
cont_flag...) C
Table Person (person_id(PK), FirstName, Last_Name, Tittle...) S
Table Property_History (Property_id, His_seq_id(PK), Pro_Decision,
Pro_Dec_Date...) PH

Relationships:
- Include ALL records from 'Property' and only those records from 'Contact'
where the joined fields are equal. Property_id
- Include ALL records from 'Contact' and only those records from 'Person'
where the joined fields are equal. Person_id
-Include ALL records from 'Property' and only those records from
'Property_History' where the joined fields are equal. Property_id

I need to select: all field of table Property, a.FirstName & ' ' &
a.LastName, b.FirstName & ' ' & b.LastName, H.Pro_Decision, H.Pro_Dec_Date

- a : when c.cont_flag = 't' and c.role_id = '2'
- b: when c.cont_flag = 't' and c.role_id = '4'
- Property_History: I need get the MAX(hIS_SEQ_ID)

Below is the original oracle code.

SELECT DISTINCT Property_id,
P_Name,
Address,
O.FIRSTNAME || ' ' || O.LASTNAME "O_FIRST_LAST",
R.FIRSTNAME || ' ' || R.LASTNAME "R_FIRST_LAST",
Decode(H. Pro_Decision, 'Y','Yes','N','No',' ') "HISTORY",
H.Pro_Decision_DATE,
FROM Property,
(SELECT CONTACT.Property_id, P.FIRSTNAME, P.LASTNAME
FROM CONTACT, PERSON
WHERE
c.cont_flag = 't' and c.role_id = '2' and
P.PERSON_ID= CONTACT.PERSON_ID) O,
(SELECT CONTACT.SITE_ID, P.FIRSTNAME, P.LASTNAME
FROM CONTACT, PERSON
WHERE
c.cont_flag = 't' and c.role_id = '4' and
P.PERSON_ID= CONTACT.PERSON_ID) R,
(SELECT Property_History.HIS_SEQ_ID,
Property_History.property_id,
Property_History.Pro_Decision,
Property_History.Pro_Dec_Date
FROM Property_History , (select site_id, max(His_seq_id) H_ID
from Property_History group by
site_id) F
WHERE
F.property_id = FAD_STATUS_HISTORY.property_id AND
F.H_ID = Property_History.HIS_SEQ_ID) H

WHERE
( O.Property_id(+) = PROPERTY.Property_id) AND
( R.Property_id(+) = PROPERTY.Property_id) AND
( PROPERTY.Property_id= H.Property_id(+) )
This Code work perpectly well.
How do I convert it to Access SQL? I have been tried to work on subqueries,
but it seems terrible HARD!!! dOESN'T WORK ...

Thanks for take you time to my problem.

AQ

 

[Tom Ellison]

SELECT DISTINCT Property_id, P_Name, Address,
O.FIRSTNAME & " " & O.LASTNAME O_FIRST_LAST,
R.FIRSTNAME & " " & R.LASTNAME R_FIRST_LAST,
IIf(H.Pro_Decision = "Y", "Yes". "No") HISTORY,
H.Pro_Decision_DATE
FROM Property
LEFT JOIN
(SELECT CONTACT.Property_id, P.FIRSTNAME, P.LASTNAME
FROM CONTACT
INNER JOIN PERSON P
note: I have added the alias P here. I don't know how it could have worked
without this, as the alias was in use in the "original query" you posted,
but not defined
ON P.PERSON_ID = CONTACT.PERSON_ID
WHERE c.cont_flag = 't'
AND c.role_id = '2') O
ON O.Property_id = PROPERTY.Property_id
LEFT JOIN
(SELECT CONTACT.SITE_ID, P.FIRSTNAME, P.LASTNAME
FROM CONTACT
INNER JOIN PERSON P
note: similar to above
ON P.PERSON_ID = CONTACT.PERSON_ID
WHERE c.cont_flag = 't' and c.role_id = '4') R
ON R.Property_id = PROPERTY.Property_id
INNER JOIN
(SELECT Property_History.HIS_SEQ_ID,
Property_History.property_id,
Property_History.Pro_Decision,
Property_History.Pro_Dec_Date
FROM Property_History
INNER JOIN
(select site_id, max(His_seq_id) H_ID
from Property_History
group by site_id) F
ON F.property_id = FAD_STATUS_HISTORY.property_id
AND F.H_ID = Property_History.HIS_SEQ_ID) H
ON PROPERTY.Property_id = H.Property_id

Here there is a table reference to a table or query FAD_STATUS_HISTORY for
which I find no precedent. This, plus the two missing aliases I have
inserted and noted, give me serious pause to think this was a working query
in the form posted.

I would have serious concerns about a query of this complexity working in
JET. With MSDE I would not hesitate in making this a single query, but JET
has limited capabilities for such complexities. You may need to make
separate working queries of the various subqueries and test them, then
reference those queries instead of inserting the multiple subqueries. The
whole thing should then work quite well.

In responding, please include something more descriptive of what you
experience than just "it doesn't work". Diagnostic messages or a
description of the error messages generated would be useful.

Tom Ellison

 

[Guest]

Hi Tom,
Thanks!!!
I changed the table names in the posted but i must mistake not completely
change all the table names with the original working sql. instead id
FAD_STATUS_HISTORY is Property_History. And missed the P for Person table.
Anyway. I will try the way you insert the subqueries in join statement and
learn more about it.

Good news that our group leader just decided not to translate that old
oracle sql to access sql cuz we spent too much time on it and none of us got
the sql work!. Phewwwww...

I will take more closer look to this matter when I have time. Thanks so
much for your help. I learn alot with access sql after this. And I'm really
appreciated your time and help!

Thank you
AQ

 

[Tom Ellison]

Dear AQ:

Next time your group leader wants you to work in Access or SQL Server, why
not give me a buzz. After what I learned from you, I'm feeling frisky about
doing some of this.

Tom Ellison