SHAPED SQL: Need help!

[Tomazas]

Hi All,
I have very complex database and I am trying to build an Shaped SQL string with no success. Could anyone help me to answer how this would look like.
TABLE LIST:
Asset, Structure, Element, History

SQL should look like:
Lets make shorter Table Names:
Asset as A
Structure as S
Element as E
History as H

Then,
[TABLE: A]
At.A_ID
H.History (WHERE A.A_ID=H.A_ID AND H.S_ID=0 AND H.Element_ID=0 AND H.Type=0) ' Multiple records

[TABLE: S] 'Multiple Records
S.A_ID (WHERE A.At_ID=S.A_ID) 'Multiple Records
H.H (WHERE S.A_ID=H.A_ID AND S.S_ID=H.S_ID AND H.E_ID=0 AND H.Type=1) ' Multiple Records
H.H (WHERE S.A_ID=H.A_ID AND S.S_ID=H.S_ID AND H.E_ID=0 AND H.Type=2) ' Multiple Records
H.H (WHERE S.A_ID=H.A_ID AND S.S_ID=H.S_ID AND H.E_ID=0 AND H.Type=3) ' Multiple Records

[TABLE: E] 'Multiple Records
E.A_ID (WHERE S.At_ID=E.A_ID AND S.S_ID=E.S_ID) 'Multiple Records
H.H (WHERE E.A_ID=H.A_ID AND E.S_ID=H.S_ID AND H.E_ID=E.E_ID AND H.Type=1) ' Multiple Records
H.H (WHERE E.A_ID=H.A_ID AND E.S_ID=H.S_ID AND H.E_ID=E.E_ID AND H.Type=2) ' Multiple Records
H.H (WHERE E.A_ID=H.A_ID AND E.S_ID=H.S_ID AND H.E_ID=E.E_ID AND H.Type=3) ' Multiple Records


DATA EXAMPLE:

TABLE A
[A.A_ID] [A.REF]
10 REF1
20 REF2


TABLE S
[S.A_ID] [S.S_ID]
10 100
10 200
20 100


TABLE E
[E.A_ID] [E.S_ID] [E.S_ID]
10 100 1
10 100 2
10 200 1
10 200 2
20 100 1

TABLE H
[H.A_ID] [H.S_ID] [H.E_ID] [H.TYPE] [H.HISTORY]
10 0 0 0 "1 THIS IS A HISTORY 10-0-0 T0" 'History of A=10, Type=0
10 0 0 0 "2 THIS IS A HISTORY 10-0-0 T0" 'History of A=10, Type=0
20 0 0 0 "1 THIS IS A HISTORY 20-0-0 T0" 'History of A=20, Type=0

10 100 0 1 "1 THIS IS S HISTORY 10-100-0 T1" 'History of S=100, Type=1
10 100 0 1 "2 THIS IS S HISTORY 10-100-0 T1" 'History of S=100, Type=1
10 100 0 2 "1 THIS IS S HISTORY 10-100-0 T2" 'History of S=100, Type=2
10 100 0 3 "1 THIS IS S HISTORY 10-100-0 T3" 'History of S=100, Type=3

10 200 0 1 "1 THIS IS S HISTORY 10-200-0 T1" 'History of S=200, Type=1
10 200 0 2 "1 THIS IS S HISTORY 10-200-0 T2" 'History of S=200, Type=2
10 200 0 3 "1 THIS IS S HISTORY 10-200-0 T3" 'History of S=200, Type=3

10 100 1 0 "1 THIS IS E HISTORY 10-100-1 T0" 'History of E=1, Type=0
10 100 1 1 "2 THIS IS E HISTORY 10-100-1 T1" 'History of E=1, Type=1
10 200 1 1 "1 THIS IS E HISTORY 10-200-1 T1" 'History of E=1, Type=1
10 100 1 2 "1 THIS IS E HISTORY 10-100-2 T2" 'History of E=1, Type=2
10 100 1 3 "1 THIS IS E HISTORY 10-100-3 T3" 'History of E=1, Type=3

And so on...

OUTPUT SHOULD LOOK LIKE:

A_ID=10
H.H="1 THIS IS A HISTORY 10-0-0 T0"
H.H="2 THIS IS A HISTORY 10-0-0 T0"
<...>
S.S_ID=100
'TYPE=0
H.H="1 THIS IS S HISTORY 10-100-0 T0"
H.H="2 THIS IS S HISTORY 10-100-0 T0"
<...>
'Type=2
H.H="1 THIS IS S HISTORY 10-100-0 T2"
<...>
'Type=3
H.H="1 THIS IS S HISTORY 10-100-0 T3"
<...>

E.E_ID=1
'Type=0
H.H="1 THIS IS E HISTORY 10-100-1 T0"
<...>
'Type=1
H.H=
<...>
'Type=2
H.H=
<...>

E.E_ID=2
'Type=0
H.H=
<...>
'Type=1
H.H=
<...>
'Type=2
H.H="1 THIS IS E HISTORY 10-100-2 T2"
<...>

<...>

S.S_ID=200
'TYPE=0
H.H="1 THIS IS S HISTORY 10-200-0 T0"
<...>

<...>

<...>

A_ID=20
H.H="1 THIS IS A HISTORY 20-0-0 T0"

Currently mys SQL looks like:

SQLCond = "IN (110154023, 123123228)"
sql = "SHAPE {SELECT * FROM [Asset] WHERE [ASSET_ID] " & SQLCond & "} As Asset " & _
"APPEND ((SHAPE {SELECT Asset_ID,Struct ure_ID,Element_ ID,hType,Histor y,Added,UserNam e FROM [History]" & _
"WHERE htype=" & hAsset & " AND [ASSET_ID] " & SQLCond & "} As History " & _
"APPEND {SELECT Asset_ID,Struct ure_ID,Element_ ID,hType,Histor y,Added,UserNam e FROM [History]" & _
"WHERE htype=" & hAssetMaintenan ce & " AND [ASSET_ID] " & SQLCond & "} As Structures)"

and gives me a syntax error at the very end: "...IN (110154023, 123123228)} As Structures)"

Thank for any kind of help!