CODEWITHSUNDEEP
crystal-reports
David Q
David Q

Reputation: 1

Crystal Reports record selection

Is there a way in Crystal Reports where I can select the correct record based on multiple rows of a dataset?

I have a contacts table that links to an address table. In the address table there is a field that defines the address type. The address table may contain multiple addresses for the same contact that might be the type of business, home or mailing addresses.

When selecting my dataset I pull multiple records but my report only needs one. How can I select the appropriate address record depending on what records are there? For example if business address is there I want that, if not I want mailing, if not there I want home.

I need to filter these records within Crystal Reports unless I can modify the sql select.

The output of the sql looks like this:
ABC Company, Main Street, Location
ABC Company, First Street, Mailing
ABC Company, Second Street, Business

Upvotes: 0

Views: 266

Answers (1)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

Your question should include some relevant data, tables schema and desired output. The following should get you started.

SELECT C.ContactId, C.ContactName
    COALESCE(BA.Address, MA.Address, HA.Address) AS Address
FROM Contact C
    LEFT JOIN Address BA ON BA.ContactId = BA.ContactId AND BA.AddressTypeId = 1 -- your business address type
    LEFT JOIN Address MA ON MA.ContactId = MA.ContactId AND MA.AddressTypeId = 2 -- your mail address type
    LEFT JOIN Address HA ON HA.ContactId = HA .ContactId AND HA .AddressTypeId = 3 -- your home address type

Upvotes: 0

Related Questions