Selecting a distinct check number

[esm]

I am trying to come up with a sql statement to pull distinct checks from a table.

the table has the obvious fields. unique transaction number, transaction date, check number, check date, check amount, customer number, invoice paid, invoice amount, amount applied to invoice, etc.

A single check could pay more than one invoice for a paricular customer. So there could be multiple rows for any single check.

It is possible that check information could be same for any two checks except for the customer number (and the unique transaction number).

What sql statement could I use to "list the checks?" with out "duplicates" and still list "all" of the checks?



.

[mattyblah]

do you just want distinct check numbers? or do you want distinct check numbers for customer number? please explain what you are trying to do and what the info will be used for. will probably be easier to figure out from that...

[esm]

I guess the easiest way to describe it would be just a listing of checks from your typical checkbook.

such a check listing would show the information for each check just once. but each check could have more than one row in the table.

Think about the times you have paid two electric bills with one check. How would your check show up in the sql table of the Electric Company? There would be two rows in the table (one for each invoice paid) but in a check listing, your check would only show up once.

As for its use, well, think about your bank statement. each check is listed once and is helpful in reconciling the bank statement. or maybe just to have a hard copy listing.

Hope this helps.



.

[esm]

i found this:

http://articles.techrepublic.com.com/5100-22-1050307.html
UNION
The UNION statement is another way to return information from multiple tables with a single query. The UNION statement allows you to perform queries against several tables and return the results in a consolidated set, as in the following example.

SELECT column1, column2, column3 FROM table1 UNION SELECT column1, column2, column3 FROM table2;


This will return a result set with three columns containing data from both queries. By default, the UNION statement will omit duplicates between the tables unless the UNION ALL keyword is used.




the following code seems to do the trick:
SELECT reference_number, check_amount, date_paid FROM payments UNION
SELECT reference_number, check_amount, date_paid from payments;




.