473,605 Members | 2,213 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Outer join problem

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_D ef"
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 "ADJUSTABIL ITY"
which should line up with the "Property_D ef" 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.Proper ty_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
6 2179
PW

"PW" <pw***@SPAMbigp ond.net.au> wrote in message
news:OE******** ******@TK2MSFTN GP03.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_D ef"
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 "ADJUSTABIL ITY"
which should line up with the "Property_D ef" 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.Proper ty_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
The subject of your post has "Outer Join", but your example has "Right
Join".

Is that the problem?

Bob Lehmann

"PW" <pw***@SPAMbigp ond.net.au> wrote in message
news:OE******** ******@TK2MSFTN GP03.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_D ef"
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 "ADJUSTABIL ITY"
which should line up with the "Property_D ef" 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.Proper ty_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

"PW" <pw***@SPAMbigp ond.net.au> wrote in message
news:OE******** ******@TK2MSFTN GP03.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_D ef"
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 "ADJUSTABIL ITY"
which should line up with the "Property_D ef" 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.Proper ty_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

"Bob Lehmann" <no****@dontbot herme.zzz> wrote in message
news:uP******** ******@TK2MSFTN GP04.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

PW wrote:
I've created an outer join query, but for some reason the "Property_D ef"
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.Proper ty_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 indistinguishab le 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("A DOX.Catalog")
With cat
.Create _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\DropM e.mdb;"

With .ActiveConnecti on

' 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.attri bseq" & _
" IS NULL, -99, QTags.attribseq )" & _
" WHERE QTags.ESCI = '005349'")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnecti on = 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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
18108
by: Martin | last post by:
I am now working on SQL Server 2000 having had previous experience on a different database. Both of the OUTER JOIN syntaxes is different from what I am used to and I am finding it slightly confusing. For example, given two tables : wipm_tbl_mi wipm_tbl_wi (which may not have data in it for a specific record that exists in the first table.)
8
4965
by: Matt | last post by:
Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and also display their corresponding entries in arb, but if there is NO entry in arb I still want it to show up as NULL or something, so that I can get the attention that there IS no language associated with that article.
4
4856
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
3
10045
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def, serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur where def.TYPEDETABLEDECODES = 4
7
31548
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
6
9005
by: Thomas Beutin | last post by:
Hi, i've a speed problem withe the following statement: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p LEFT OUTER JOIN ot_kat_prod AS pz ON ( p.p_id = pz.p_id ) WHERE p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37'; This is terrible slow compared to the inner join: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
3
19464
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2 have the same type of operator, without using the OUTER JOIN syntax ?
4
8851
by: Anthony Robinson | last post by:
I was actually just wondering if someone could possibly take a look and tell me what I may be doing wrong in this query? I keep getting ambiguous column errors and have no idea why...? Thanks in advance!!! SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
3
7539
by: deko | last post by:
From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite... SELECT qryTxToQ1.Q1Total, qryTxToQ2.Q2Total, qryTxToQ3.Q3Total, qryTxToQ4.Q4Total FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN qryTxToQ2 ON qryTxToQ1.TxAcct_ID = qryTxToQ2.TxAcct_ID) ON qryTxToQ3.TxAcct_ID = qryTxToQ2.TxAcct_ID) ON qryTxToQ4.TxAcct_ID = qryTxToQ3.TxAcct_ID;
3
17830
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and LEFT OUTER JOIN that result with prospects. (I want to receive all the results of the inner join and any pertinent info from table 3 that is available.) The way it was written in MSSQL was basically..
0
8007
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7937
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8290
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6749
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3913
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
1546
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1274
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.