Inclusion operator in SQL??? 2

[SonOfTed]

Below is some SQL that I created and modified from a standard .QBE query by using the "View", "Show SQL" commands from the query window.

SELECT d.Child_ID, D1."Date", d2.Goal_ID, d2."Hour", d2.Score, d."Last_Name"+", "+d."First_Name"+" - "+d.Child_ID as SName
FROM "Children.db" d, "Goals.DB" D1, "Results.db" d2
WHERE
(D1.Child_ID = d.Child_ID)
AND (D1."Date" >= '11/01/2005')
AND (D1."Date" <= '11/30/2005')
AND (d2.Goal_ID = D1.Goal_ID)
ORDER BY d.Child_ID, D1."Date", d2.Goal_ID, d2."Hour", d2.Score

My question involves the "!" inclusion operator used to turn an inner join into an outer join. Usually when I link two tables in a query I can just put the exclamation point next to the key element name that links two tables.

How do I do that in SQL? For example, if I want to include ALL rows from the "Goals.DB" (D1) table above, where do I put the inclusion operator in the actual SQL?

I've tried everything I could think of and would appreciate any help you might have.

SoT

 

[vecjjk]

I believe it is a plus sign within parenthesis (+)

 

[SonOfTed]

Thank you very much! I shall try that!

 

[SonOfTed]

How would I place that in the "WHERE" clause of my sample SQL above?

..
..
..

(D1.Child_ID = d.Child_ID)

changed to

(D1.Child_ID (+) = d.Child_ID (+))

or

((+) D1.Child_ID = (+) d.Child_ID)

will not work.

I need the "outer join" for both linked tables (Goals and Children).

 

[TonyMcGuire]

Basics for a outer join is:

select columnanmes
from tableA a left outer join tableB b
on a.id=b.id
where conditions

Left can be replaced by Right or Full



Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[SonOfTed]

Tony's example:

<< Basics for a outer join is:

<< select columnanmes
<< from tableA a left outer join tableB b
<< on a.id=b.id
<< where conditions

<< Left can be replaced by Right or Full

1. What is the significance of the left, right, or Full parameter?

2. In the past I have only been able to use an outer join when building visual queries (.QBE) objects.

a. Next to one or both of the words used to link two tables (Join1), etc. all I have to do is follow it with the "!". Example: JOIN1!

b. The exclamation point can be used after the "JOIN1" text in one or both tables to include unmatched rows from each table respectively.

3. On occasion however I need to do more with the query than the visual developer allows.

a. In those cases I choose "View", then "Show SQL" to convert the .QBE object to its native SQL format and save it as a .SQL object which I can modify further.

b. That's where the outer join has me befuddled, because it will not let me use the "View", "Show SQL" option if the "!" has been specified anywhere in the query.

c. I have tried using the "!" character at various places in the SQL (hoping it would be recognized there the same as in the visual query but to no avail).

d. I have an old "Client Server for Dummies" book that suggests using "A.Key1 =* B.Key1" to specify an outer join (the asterisk being the designator when linking two tables).

e. Regardless of whether I try the exclamation point or the asterisk, I have been unsuccessful getting a functional example working for SQL objects.

Still puzzled...

 

[TonyMcGuire]

I just copied from another post, and can't help explain it.

You might come over to the groups on thedbcommunity.com, and ask there.


Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[vecjjk]

Regarding the plus sign way, you just put the plus sign on the key from the table that might be missing the record. In your example, it would be:

SELECT d.Child_ID, D1."Date", d2.Goal_ID, d2."Hour", d2.Score, d."Last_Name"+", "+d."First_Name"+" - "+d.Child_ID as SName
FROM "Children.db" d, "Goals.DB" D1, "Results.db" d2
WHERE
(D1.Child_ID = d.Child_ID (+))
AND (D1."Date" >= '11/01/2005')
AND (D1."Date" <= '11/30/2005')
AND ((+) d2.Goal_ID = D1.Goal_ID)
ORDER BY d.Child_ID, D1."Date", d2.Goal_ID, d2."Hour", d2.Score