Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Outer join problem

Discussion in 'Information Technology' started by PW, Jul 28, 2006.

  1. PW

    PW
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Inner and Outer Storms Spiritual Articles Dec 16, 2005
    Pentagon Plans to Close 180 Sites, Shift Area Jobs to Outer Suburbs (washingtonpost.com) Interfaith Dialogues May 15, 2005
    Seema Munde Joins Sikh Philosophy Network! New SPN'ers Thursday at 8:59 AM
    Prem K Jo Joins Sikh Philosophy Network! New SPN'ers Tuesday at 3:35 PM
    Thalinder Singh Joins Sikh Philosophy Network! New SPN'ers Monday at 11:01 AM

  3. PW

    PW
    Expand Collapse
    Guest

    "PW" <pwaNO@SPAMbigpond.net.au> wrote in message
    news:OEDxC5EhGHA.3924@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.
     
  4. Bob Lehmann

    Bob Lehmann
    Expand Collapse
    Guest

    The subject of your post has "Outer Join", but your example has "Right
    Join".

    Is that the problem?

    Bob Lehmann

    "PW" <pwaNO@SPAMbigpond.net.au> wrote in message
    news:OEDxC5EhGHA.3924@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
    >
    >
    >
     
  5. Jason

    Jason
    Expand Collapse
    Guest

    "PW" <pwaNO@SPAMbigpond.net.au> wrote in message
    news:OEDxC5EhGHA.3924@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
     
  6. Jason

    Jason
    Expand Collapse
    Guest

    "Bob Lehmann" <nospam@dontbotherme.zzz> wrote in message
    news:uP0OYtFhGHA.1612@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
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    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.

    --
     
  8. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    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/paradox/beyond/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.

    --
     

Share This Page