How modify Select statement to skip ad'l/duplicate recs

[HughFreiden]

Is there a way to have the select statement use only the first record of a left outer join. Perhaps a sequel command?

I know you can suppress detail lines and/or use running totals. However for various reasons this will not work in my complex report. Perhaps a sequel command?

PLEASE ADVISE?!

 

[IdoMillet]

A Left Outer Join DOES return only one record for each record on the left that doesn't have a matching record
on the right.

Perhaps what you are looking for is the SQL key word DISTINCT (as in SELECT DISTINCT...)

If that doesn't help, please clarify your question with a more concrete description of your query and what you are trying to achieve.

Cheers,
- Ido ixm7@psu.edu

 

[HughFreiden]

Thank u ido for ur help.

I am doing a left outer join, however it is the right side-file which I want only the first match records.

For Example

I have a piece of equipment (unit) tied to to a job. I read a file that has all the units and (left outer join) key by unit to another file that list the units with all the jobs that a unit worked on. However this file has dupliate Unit-job records. I just want to find each unique job for a unit. However, unfortunatley, the file that tells me all the units tied to jobs, could have duplicate jobs that each unit worked. Therefore I only need the first uniue job for that unit, then "skip" to the next unique job for the same unit. Is this clearer? I would simply like to this in a select statment if at all possible?

Thank u for ur kind help

Hugh

 

[MikeArg]

If you want the SELECT statement to return only the first record of "ALL" the SQL Query, you can use "SELECT TOP 1". This statement returns de number of records correponding to the number you write after the TOP word.

SELECT TOP "n" returns you "n" records.

I hope this could help you!!

 

[IdoMillet]

One approach is to group by Unit and within Unit by Job.
Then, instead of showing the data in the detail section,
drag the fields to the Job group header (or footer)
and suppress the detail band.

Your description is still not detailed enough to
suggest if a SELECT DISTINCT would be a better approach
(you didn't describe what you want to see on the report).

A GROUP BY SQL query approach may offer another alternative.

Cheers,
- Ido

p.s. The LEFT OUTER join has nothing to do with the problem. ixm7@psu.edu