By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,606 Members | 3,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,606 IT Pros & Developers. It's quick & easy.

Outer join problem

P: n/a
PW

I've created an ASP application which uses an Access database.

I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should be aligned on
the ESPI common columns.

In this attached image, the first lines ESPI of 010471 "ADJUSTABILITY"
which should line up with the "Property_Def" column that says "An indication
of whether or not the item is adjustable".

I have tried adding Approved.ESPI = QTags.ESPI to the query, but I get an
error.

Heres the SQL of the query ...

SELECT QTags.ESCI, Approved.ESCI, QTags.attribseq, Approved.Seq, QTags.ESPI,
Approved.ESPI, Approved.Property_Def, QTags.ESPN
FROM Approved RIGHT JOIN QTags ON (Approved.ESCI = QTags.ESCI) AND
(Approved.Seq = QTags.attribseq)
WHERE (((QTags.ESCI)="005349"));

Any help appreciated.

TIA,
PW

May 31 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
PW

"PW" <pw***@SPAMbigpond.net.au> wrote in message
news:OE**************@TK2MSFTNGP03.phx.gbl...

I've created an ASP application which uses an Access database.

I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should be aligned on
the ESPI common columns.

In this attached image, the first lines ESPI of 010471 "ADJUSTABILITY"
which should line up with the "Property_Def" column that says "An
indication
of whether or not the item is adjustable".

I have tried adding Approved.ESPI = QTags.ESPI to the query, but I get an
error.

Heres the SQL of the query ...

SELECT QTags.ESCI, Approved.ESCI, QTags.attribseq, Approved.Seq,
QTags.ESPI,
Approved.ESPI, Approved.Property_Def, QTags.ESPN
FROM Approved RIGHT JOIN QTags ON (Approved.ESCI = QTags.ESCI) AND
(Approved.Seq = QTags.attribseq)
WHERE (((QTags.ESCI)="005349"));

Any help appreciated.

TIA,
PW

Sorry, I attached some small images as examples, but they did not get
through to the post.

May 31 '06 #2

P: n/a
The subject of your post has "Outer Join", but your example has "Right
Join".

Is that the problem?

Bob Lehmann

"PW" <pw***@SPAMbigpond.net.au> wrote in message
news:OE**************@TK2MSFTNGP03.phx.gbl...

I've created an ASP application which uses an Access database.

I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should be aligned on
the ESPI common columns.

In this attached image, the first lines ESPI of 010471 "ADJUSTABILITY"
which should line up with the "Property_Def" column that says "An indication of whether or not the item is adjustable".

I have tried adding Approved.ESPI = QTags.ESPI to the query, but I get an
error.

Heres the SQL of the query ...

SELECT QTags.ESCI, Approved.ESCI, QTags.attribseq, Approved.Seq, QTags.ESPI, Approved.ESPI, Approved.Property_Def, QTags.ESPN
FROM Approved RIGHT JOIN QTags ON (Approved.ESCI = QTags.ESCI) AND
(Approved.Seq = QTags.attribseq)
WHERE (((QTags.ESCI)="005349"));

Any help appreciated.

TIA,
PW

May 31 '06 #3

P: n/a

"PW" <pw***@SPAMbigpond.net.au> wrote in message
news:OE**************@TK2MSFTNGP03.phx.gbl...

I've created an ASP application which uses an Access database.

I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should be aligned on
the ESPI common columns.

In this attached image, the first lines ESPI of 010471 "ADJUSTABILITY"
which should line up with the "Property_Def" column that says "An
indication
of whether or not the item is adjustable".

I have tried adding Approved.ESPI = QTags.ESPI to the query, but I get an
error.

Heres the SQL of the query ...

SELECT QTags.ESCI, Approved.ESCI, QTags.attribseq, Approved.Seq,
QTags.ESPI,
Approved.ESPI, Approved.Property_Def, QTags.ESPN
FROM Approved RIGHT JOIN QTags ON (Approved.ESCI = QTags.ESCI) AND
(Approved.Seq = QTags.attribseq)
WHERE (((QTags.ESCI)="005349"));

Any help appreciated.

TIA,
PW


It might be that you are joining the two tables on two different sets of
columns. (Approved.ESCI = QTags.ESCI) AND (Approved.Seq = QTags.attribseq)
I have never done that, or tried to do that so I don't really have solidly
backed answer, but I would guess that Access doesn't support it. I would
take out one of those, and then see if it works.

--Jason
May 31 '06 #4

P: n/a

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:uP**************@TK2MSFTNGP04.phx.gbl...
The subject of your post has "Outer Join", but your example has "Right
Join".

Is that the problem?

Bob Lehmann


A RIGHT JOIN is equivalent to a RIGHT OUTER JOIN.

--Jason
May 31 '06 #5

P: n/a

PW wrote:
I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should be aligned on
the ESPI common columns.

Heres the SQL of the query ...

SELECT QTags.ESCI, Approved.ESCI, QTags.attribseq, Approved.Seq, QTags.ESPI,
Approved.ESPI, Approved.Property_Def, QTags.ESPN
FROM Approved RIGHT JOIN QTags ON (Approved.ESCI = QTags.ESCI) AND
(Approved.Seq = QTags.attribseq)
WHERE (((QTags.ESCI)="005349"));


Is one of the columns you are using in the JOIN (ESCI or Seq/attribseq)
nullable?

It could be that for your query a null value in the table on the left
(the 'unpreserved table') is indistinguishable from a null resulting
from the outer join.

I'd suggest you use a derived table or VIEW ('stored query') to parse
out the null values for join purposes.

Here's an example which uses a derived table:

Sub TestApproved()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

With .ActiveConnection

' Create stripped-down versions of OP's tables
.Execute _
"CREATE TABLE QTags ( ESCI CHAR(6) NOT NULL," & _
" CHECK (ESCI LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')," & _
" attribseq INTEGER, CHECK (attribseq > 0)," & _
" UNIQUE (ESCI, attribseq) ) ;"
.Execute _
"CREATE TABLE Approved (ESCI CHAR(6) NOT" & _
" NULL, Seq INTEGER, UNIQUE (ESCI, Seq)," & _
" FOREIGN KEY (ESCI, Seq) REFERENCES QTags" & _
" (ESCI, attribseq) ON DELETE NO ACTION ON" & _
" UPDATE NO ACTION, always_1 INTEGER DEFAULT" & _
" 1 NOT NULL, CHECK (always_1 = 1));"

' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

' Create test data
.Execute _
"INSERT INTO QTags (ESCI, attribseq) SELECT" & _
" DT1.ESCI, DT1.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe )" & _
" AS DT1;"

.Execute _
"INSERT INTO Approved (ESCI, Seq) SELECT" & _
" DT1.ESCI, DT1.Seq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS Seq FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe )" & _
" AS DT1 ;"

Dim rs As Object

' OP's query (watch the 'always_1' column,
' being third from the left)
Set rs = .Execute( _
"SELECT * FROM Approved RIGHT JOIN QTags" & _
" ON (Approved.ESCI = QTags.ESCI) AND (Approved.Seq" & _
" = QTags.attribseq) WHERE (((QTags.ESCI)='005349'));")
MsgBox rs.GetString
rs.Close

' Suggested revised query (watch the 'always_1' column,
' being forth from the left)
Set rs = .Execute( _
"SELECT * FROM ( SELECT IIF(Seq IS NULL," & _
" -99, Seq) AS seq_amended, * FROM Approved" & _
" ) AS DT1 RIGHT JOIN QTags ON DT1.ESCI =" & _
" QTags.ESCI AND DT1.seq_amended = IIF(QTags.attribseq" & _
" IS NULL, -99, QTags.attribseq)" & _
" WHERE QTags.ESCI = '005349'")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

You may be better off opening the db created above in the the Access
GUI and running the queries therein.

However, if the columns in the JOIN are NOT NULL then ignore this post.

Jamie.

--

May 31 '06 #6

P: n/a

Jason wrote:
It might be that you are joining the two tables on two different sets of
columns. (Approved.ESCI = QTags.ESCI) AND (Approved.Seq = QTags.attribseq)
I have never done that, or tried to do that so I don't really have solidly
backed answer, but I would guess that Access doesn't support it.


You guessed wrong <g>. Broadly speaking, creating a left/right outer
join between two tables on two different sets of columns is supported
by Access/Jet SQL.

However, the syntax has bugs (e.g.
http://support.microsoft.com/?id=208880); also the syntax is not full
ANSI SQL-92 standard (see
http://web.comhem.se/~u82608896/para...yond/bid72.htm) and you will
often see the dreaded 'Join expression not supported' for even the most
simple of constructs e.g.

SELECT * FROM
A LEFT JOIN B ON
A.UniqueId = B.A_UniqueId
AND B.PeriodID < 3;

Jamie.

--

May 31 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.