left join to a filtered table

[George Brink]

I'd like to do a report in InfoMaker 11.5 which will show all rows from
first table and only rows which satisfy some pre-join filtering from
second table. In a normal SQL I'll write this query like this:

select *
from Customers cust
left join Addresses addr on
cust.PersonID=addr.PersonID and
addr.AddressType=2
where cust.PersonType=1

How can I tell InfoMaker that the condition addr.AddressType=2 should go
into JOIN clause not into WHERE?

[unknown]

Hi George,

wouldn't that be

select *
from Customers cust
left outer join Addresses addr on
cust.PersonID=addr.PersonID and
addr.AddressType=2
where cust.PersonType=1

(see the keyword "outer" in the join clause)?

HTH

Chris Werner
f+s software gmbh

Post by George Brink
I'd like to do a report in InfoMaker 11.5 which will show all rows from
first table and only rows which satisfy some pre-join filtering from
select *
from Customers cust
left join Addresses addr on
cust.PersonID=addr.PersonID and
addr.AddressType=2
where cust.PersonType=1
How can I tell InfoMaker that the condition addr.AddressType=2 should go
into JOIN clause not into WHERE?

[George Brink]

Yes, it would. Word "outer" is optional, read the SQL-92.

Post by unknown
Hi George,
wouldn't that be
select *
from Customers cust
left outer join Addresses addr on
cust.PersonID=addr.PersonID and
addr.AddressType=2
where cust.PersonType=1
(see the keyword "outer" in the join clause)?
HTH
Chris Werner
f+s software gmbh

Post by George Brink
I'd like to do a report in InfoMaker 11.5 which will show all rows from
first table and only rows which satisfy some pre-join filtering from
select *
from Customers cust
left join Addresses addr on
cust.PersonID=addr.PersonID and
addr.AddressType=2
where cust.PersonType=1
How can I tell InfoMaker that the condition addr.AddressType=2 should go
into JOIN clause not into WHERE?

[Terry Dykstra [TeamSybase]]

By writing the SQL in syntax mode.
--
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

Post by George Brink
I'd like to do a report in InfoMaker 11.5 which will show all rows from
first table and only rows which satisfy some pre-join filtering from
select *
from Customers cust
left join Addresses addr on
cust.PersonID=addr.PersonID and
addr.AddressType=2
where cust.PersonType=1
How can I tell InfoMaker that the condition addr.AddressType=2 should go
into JOIN clause not into WHERE?

[George Brink]

And how can I write the SQL in a query?
From main menu, File->Database->Query, Cancel in the list of tables.
Now I have a new empty query. What should I click next, to be able to
write SQL statements?

I can open existing report and do Design->Convert To Syntax, but there
is no such menu in queries.

Post by Terry Dykstra [TeamSybase]
By writing the SQL in syntax mode.

[Terry Dykstra [TeamSybase]]

Write the SQL in a report. When you have the SQL painter open you can use a
File Save As Query option to create a standalone query object.
--
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

Post by George Brink
And how can I write the SQL in a query?
From main menu, File->Database->Query, Cancel in the list of tables. Now I
have a new empty query. What should I click next, to be able to write SQL
statements?
I can open existing report and do Design->Convert To Syntax, but there is
no such menu in queries.

Post by Terry Dykstra [TeamSybase]
By writing the SQL in syntax mode.