Re: Equilevant of Oracle for optional records

All,

I really appreciate the responses but this appears to be trickier to explain
than I thought. Bear with me while I elaborate.

The sample data structure is
T1 with Pk Id.
T2 with Pk Id,type so we can be sure than 0 or 1 records exist in T2 for
each id,Type.

What I am looking for is a solution that returns:
1) One row per Id
2) A set of columns from tables TA,TB,...,Tx in the one row.
3) Each table in TA,TB,...,Tx is identified with a specific record type in
the physical T2 table
4) If there is a correct type for the Id it will return the values in T2 as
Tx where type='Tx'
5) If there is no record with the correct type will return nulls in the
column set

The solutions offered so far are creative but the ones that work:
1) Produce multiple rows per Id
2) Become quickly unmanagable when joining using more than one Type, i.e.
TA,TB,TC, etc.

For the relational database people I am denormalizing the table structure
and turning multiple records in T2 into a repeating group in the result set.
You don't want to store information in repeating groups but they are
sometimes perfect for humans who need to do something with the data.

I am asking because the structure of T2 is a great tool to let users create
custom attributes for something represented by T1 rows without re-coding the
UI but I need to be able to write queries that treat the rows in T2 as if
they were columns in T1.

Oracle handles this data structure perfectly but I suspect it might be
ported to Sql Server someday and I already know cases where it would be nice
to query this structure in MS Access. Right now I flatten it prior to moving
to Access.

Maurice

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:u8FrSzBVGHA.2704@xxxxxxxxxxxxxxxxxxxxxxx
Hi,



SELECT x.*, y.*
FROM ( SELECT * FROM t1 WHERE type="A") AS x
LEFT JOIN
( SELECT * FROM t2 WHERE type="B") AS y
ON x.id = y.id


is what I guess you want. NO record with t1.type <> "A" will appear, but
those with t1.type="A" would either be associated with their counter part
in t2 (sharing a common id) where type="B", either will be associated with
NULLs.


Sure, you can replace the * with explicit lists.

Hoping it may help,
Vanderghast, Access MVP


"Maurice W. Darr" <mdarr@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uAFaj94UGHA.5828@xxxxxxxxxxxxxxxxxxxxxxx
Any or all,

The Oracle syntax for outer joins makes it possible to join to a table by
record type but I cannot find an equilivent in Access. Here's what I
mean.

I have two tables
T1 (id)
T2 (id,type,data)
I want a join that will return several records from T2 joined to T1 but
with different Type values
In Oracle this is straigthforward.

Select t1.id,ta.data a_data,tb.data b_data
from t1, t2 ta,t2 tb
where ta.id+=t1.id
and ta.type+="A"
and tb.id+=t1.id
and tb.type+="B"

This query will return all the records in T1. If there is a corresponding
record in T2 with a type of 'A' it will return a record otherwise it will
return null in a_data. The same is true of b_data. Either a null if there
are no records with a 'B' or a value.

In Access I cannot find a way to do this in Access.
It would be nice to use
T1 LEFT JOIN T2 AS Ta ON T1.ID = Ta.CAPID and ta.type='A'

This does not work.
A straight left join with a filter by type will not return any records if
there is at least one alternate type in T2 for the join key.

Any ideas?

TIA, maurice








.

Relevant Pages

  • Re: Equilevant of Oracle for optional records
    ... this query is done, then flatten the result? ... example, the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • Re: Equilevant of Oracle for optional records
    ... and a normalized t2 could be more useful, to build the query, and once this ... the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • Re: Equilevant of Oracle for optional records
    ... If I'm right that will give me multiple rows, ... Of course that could be handled with a crosstab query and that's a possible ... The Oracle syntax for outer joins makes it possible to join to a table by ... In Oracle this is straigthforward. ...
    (microsoft.public.access.queries)
  • Re: Dynamic Query with SQL Server
    ... Is your front-end an ADP or MDB? ... might be easier to troubleshoot inefficiency with the data structure, ... the query execution that is slowing things down. ... SQL via ODBC doesn't handle that structure as quickly as the native Jet ...
    (microsoft.public.access.queries)
  • Re: [PHP] A no brainer...
    ... any data structure that is of interesting size, ... representing a view of your database exists, the more of a problem it ... PHP stores session data in files by default. ... you're still making a DB query. ...
    (php.general)