Formula or SQL query question

[toby77jo]

Hi,

I am creating a report that is listing Projects grouped by Sector.
A project can belong to many sectors. But I want only to take the first match between Project and Sector into account.

An example (how it works now, if Project1 relates to both public and private sector it would be listed twice in the report)

Puclic Sector (group header)

(In details) Project1
Project2
Project3

Private Sector (group header)

(In details) Project1
Project4
Project5

My goal would be to only list project1 in public sector and ignore any other sector matches. How would I do that.

Here is my current SQL query.

SELECT `T_Assigments`.`End Date`, `T_Assigments`.`FundingRaiser`, `T_Found_Raiser`.`Short description EN`, `T_Tenders`.`Number_Affaire`, `T_Expertise`.`Description`, `T_Partners`.`Society`, `T_Assigments`.`Descr courte-va`, `T_Assigments`.`Titre-va`
FROM (((((`T_Assigments` `T_Assigments` LEFT OUTER JOIN `Relations Affaires - Offres - Partners` `Relations_Affaires___Offres___Partners` ON `T_Assigments`.`O_ID`=`Relations_Affaires___Offres___Partners`.`O_ID`) INNER JOIN `Relations Affaires - Expertises` `Relations_Affaires___Expertises` ON `T_Assigments`.`A_ID`=`Relations_Affaires___Expertises`.`A_ID`) INNER JOIN `T_Found Raiser` `T_Found_Raiser` ON `T_Assigments`.`C_Id`=`T_Found_Raiser`.`C_Id`) INNER JOIN `T_Tenders` `T_Tenders` ON `T_Assigments`.`O_ID`=`T_Tenders`.`O_ID`) LEFT OUTER JOIN `T_Partners` `T_Partners` ON `Relations_Affaires___Offres___Partners`.`NumSoc`=`T_Partners`.`NumSoc`) INNER JOIN `T_Expertise` `T_Expertise` ON `Relations_Affaires___Expertises`.`E_ID`=`T_Expertise`.`E_ID`
WHERE `T_Tenders`.`Number_Affaire` LIKE 'A*'

 

[synapsevampire]

You might be able to use a date field to determine which is the latest.

What was the source of this SQL Query, you created it, or Crystal did?

And please always post your software version in any forum...

You might use a UNION query:

select <field list> from <tables>
where <conditions return ONLY the Public Sector>
UNION
select <field list> from <tables>
where <conditions return ONLY the Private Sector>

A Union only brings in distinct rows, so it will ignore the Private rows that are identical.

-k

 

[toby77jo]

Crystal did create the query, I am using Crystal XI.

If I am correct I can not manipulate the Crystal generated query, but I can create one command in the db expert, but I have no clue how to create this Union command and where to place it exactly in the report.

An export from the db,

A_ID E_ID
568 7
283 11
277 3
280 3
280 8
280 11
287 3
269 2
269 3
276 10
273 9
273 4

A_ID 280 for example, I only want it to take E_ID 3 into account and skip E_ID 8 and 11

E_ID is pointing to a lookup table to get the correct sector name based on its E_ID