MultiTable Query Question

#1

I have what should be a simple question. (BTW I am not facile in SQL)

In the following query there are times when the last table
(SedationQiSub3) has no entry for a patient. Someone didn't type in a
memo. If that patient has no entry for the memo, the query will not
retrieve that patient at all.
Is there a way to get the patient and the rest of the data if there is
no record in the table which contains the memo?
Thanks,

Craig

Query
ANSWER: :Ktables:sedationQIPeer2.db

:Ktables:Bioinfo.DB | Status | Year |
Unique# |

Quote:

Check _join1 | Check _join2 | Check
_join3 |

Quote:

Check | Check |
Check | Check |

Quote:

Quote:

_join1 |
_join2 | _join3 | _join4 |

:Ktables:SedationQIsupplimental.DB | MajorAdverseOutcome | SedationDOC

Quote:

Check | Check |

:Ktables:SedationQIsupplimental.DB | SedationDocAlsoProcDoc |
ASAStatus | ASAeStatus |

Quote:

Check | Check | Check |

:Ktables:SedationQIProblems.db | Status | Year | Unique# | #ForToday

Quote:

Quote:

EndQuery

#2

Use an exclamation point (!) on the joins used to link it to the other
table(s).

But you can only use it once, so you have to use multiple join variables
(_join#) to link them all together.

You can use _join1, _join15! as a part of the linking process.

--
------------------------------
Tony McGuire

#3

The ! should only go on the _join# element in the table that *does* have
all the records you want, not in the table that's missing records. So:

CUSTOMER.DB | CustomerID | CustomerName |

Quote:

_join1! | Check |

ORDER.DB | OrderID | CustomerID |

Quote:

Check | _join1 |

....will get me all of the customers and for those which happen to have
orders, it will get me all their order ID values as well.

Liz

Tony McGuire wrote:

Quote:

Use an exclamation point (!) on the joins used to link it to the other
table(s).

But you can only use it once, so you have to use multiple join variables
(_join#) to link them all together.

You can use _join1, _join15! as a part of the linking process.

#4

Quote:

The ! should only go on the _join# element in the table that *does* have
all the records you want, not in the table that's missing records. So:

Yes, exactly what I meant you say.

--
------------------------------
Tony McGuire

#5

On May 22, 9:49 pm, "Tony McGuire" <png.paradoxcommunity@com> wrote:

Quote:

The ! should only go on the _join# element in the table that *does* have
all the records you want, not in the table that's missing records. So:

Yes, exactly what I meant you say.

--
------------------------------
Tony McGuire

I appreciate all the replies. I understand about using an inclusion
operator, "!". But in the above query, I can't figure out where to put
it.
Just to clarify, the query looks at 5 tables. It is the last table
which may or may not have records for that patient. I need help with
this specific query. Because of the nature of these documents, it is
hard to read the query when it is cut and pasted onto a message. If
anyone wants me to send them a .txt document with the query, I am
happy to do so.
Thanks again,
Craig
PS I changed the nickname from Jonathan to Craig. I really am Craig

#6

Quote:

I understand about using an inclusion operator, "!". But in the above
query, I can't figure out where to put it. Just to clarify, the query
looks at 5 tables. It is the last table which may or may not have records
for that patient.

then it's the join to the last table that MUST have the exclamation.. that
makes the link "whether there's a match, or not"..

--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards

"jonathan" <cafutter (AT) yahoo (DOT) com> wrote

Quote:

On May 22, 9:49 pm, "Tony McGuire" <png.paradoxcommunity@com> wrote:
The ! should only go on the _join# element in the table that *does*
have
all the records you want, not in the table that's missing records. So:

Yes, exactly what I meant you say.

--
------------------------------
Tony McGuire

I appreciate all the replies. I understand about using an inclusion
operator, "!". But in the above query, I can't figure out where to put
it.
Just to clarify, the query looks at 5 tables. It is the last table
which may or may not have records for that patient. I need help with
this specific query. Because of the nature of these documents, it is
hard to read the query when it is cut and pasted onto a message. If
anyone wants me to send them a .txt document with the query, I am
happy to do so.
Thanks again,
Craig
PS I changed the nickname from Jonathan to Craig. I really am Craig

#7

Quote:

then it's the join to the last table that MUST have the exclamation.. that
makes the link "whether there's a match, or not"..

If that last table uses the same join to link to the tables above it in the
query, you'll need to create new join variables since an inclusion join
variable can only be used in the main table and the table being joined.

I reiterate this, since I messed up on this a BUNCH before I realized the
issue.

------------------------------
Tony McGuire

#8

Quote:

If that last table uses the same join to link to the tables above it in
the query, you'll need to create new join variables since an inclusion
join variable can only be used in the main table and the table being
joined.

or, explained a different way.. an ordinary join can be used to link three
or four tables, if necessary and convenient.. but an outer join (the
exclamation) can only link two tables.. and, more than one example can be
used in a single field, if these rules mandate it.. for example:

table A | key field |

Quote:

_joinA!, _joinB |

table B | key field |

Quote:

_joinB |

table C | key field |

Quote:

_joinB |

table D | key field |

Quote:

_joinB |

table E | key field |

Quote:

_joinA |

--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards
"Tony McGuire" <png.paradoxcommunity@com> wrote

Quote:

then it's the join to the last table that MUST have the exclamation..
that
makes the link "whether there's a match, or not"..

If that last table uses the same join to link to the tables above it in
the query, you'll need to create new join variables since an inclusion
join variable can only be used in the main table and the table being
joined.

I reiterate this, since I messed up on this a BUNCH before I realized the
issue.

------------------------------
Tony McGuire

#9

Quote:

or, explained a different way..

Yep.

That's exactly what I said.

:-)

------------------------------
Tony McGuire

#10

Ah,
I didn't realize you could have 2 join variables in 1 field!
I'll try that tomorrow and see if it works for me.
As usual, it is great to have this community as support.

Thanks,

Craig (AKA Jonathan)

"Steven Green" <greens (AT) diamondsg (DOT) com> wrote

Quote:

If that last table uses the same join to link to the tables above it in
the query, you'll need to create new join variables since an inclusion
join variable can only be used in the main table and the table being
joined.

or, explained a different way.. an ordinary join can be used to link three
or four tables, if necessary and convenient.. but an outer join (the
exclamation) can only link two tables.. and, more than one example can be
used in a single field, if these rules mandate it.. for example:

table A | key field |
| _joinA!, _joinB |

table B | key field |
| _joinB |

table C | key field |
| _joinB |

table D | key field |
| _joinB |

table E | key field |
| _joinA |

--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards
"Tony McGuire" <png.paradoxcommunity@com> wrote in message
news:4654aac0$1 (AT) pnews (DOT) thedbcommunity.com...

then it's the join to the last table that MUST have the exclamation..
that
makes the link "whether there's a match, or not"..

If that last table uses the same join to link to the tables above it in
the query, you'll need to create new join variables since an inclusion
join variable can only be used in the main table and the table being
joined.

I reiterate this, since I messed up on this a BUNCH before I realized the
issue.

------------------------------
Tony McGuire