Select Left Outter Join

[ToniS]

I have a report that I would like to print all exhibitors and their MAIN
address information. I would also
like to print on the same report all Exhbitors that do not have a main or
branch address. (No address exists for the exhibitor)

The ExhibitorAddresses.AddressType can have a value of 1(main address) or
2(Branch address).

Originally the select statement was as follows..

SELECT ES.ExhibitorShowID, ES.ShowId, E.ExhibitorShortName, E.ExhibitorName,
E.Notes, EA.Fax, EA.AddressType, ViewGetFirstBooth.FirstBooth
FROM ExhibitorsShows ES
INNER JOIN Exhibitors E ON ES.ExhibitorID = E.ExhibitorID
INNER JOIN ExhibitorAddresses EA ON E.ExhibitorID =
EA.ExhibitorID
LEFT OUTER JOIN ViewGetFirstBooth ON ES.ExhibitorShowID =
ViewGetFirstBooth.ExhibitorShowID
WHERE (EA.AddressType = 1 and Es.ShowID = '" & CurrentShowID &
"')
ORDER BY E.ExhibitorShortName

I have tried the following and this did not work, the ones that do not have
an address did not print.


SELECT ES.ExhibitorShowID, ES.ShowId, E.ExhibitorShortName, E.ExhibitorName,
E.Notes, EA.Fax, EA.AddressType, ViewGetFirstBooth.FirstBooth
FROM ExhibitorsShows ES INNER JOIN
Exhibitors E ON ES.ExhibitorID = E.ExhibitorID
LEFT OUTER JOIN ExhibitorsAddresses EA ON E.ExhibitorID = EA.ExhibitorID
LEFT OUTER JOIN ViewGetFirstBooth VFB ON ES.ExhibitorShowID =
VFB.ExhibitorShowID
WHERE (EA.AddressType <> 2 and Es.ShowID = '" & CurrentShowID & "')
ORDER BY E.ExhibitorShortName

Thanks
ToniS

 

[ToniS]

I think I figured it out, I did the following and this seemed to work....

FROM ExhibitorsShows ES INNER JOIN
Exhibitors E ON ES.ExhibitorID =
Exhibitors.ExhibitorID
LEFT OUTER JOIN ExhibitorAddresses EA ON E.ExhibitorID = EA.ExhibitorID
LEFT OUTER JOIN ViewGetFirstBooth VFB ON ES.ExhibitorShowID =
VFB.ExhibitorShowID
WHERE (ExhibitorAddresses.AddressType = 1) OR
(ExhibitorAddresses.AddressType IS NULL)
ORDER BY E.ExhibitorShortName

 

[Robert Morley]

Yes, that looks like that would be your problem. It might be easier to
create a sub-query for Exhibitor Addresses (i.e., SELECT * FROM
ExhibitorAddresses WHERE AddressType = 1) and then use a LEFT OUTER JOIN to
the sub-query. This will avoid the OR ... IS NULL situation altogether.

There are times when it's undesirable to do this, but for the most part, I
find it much easier to work with.


Rob