query...

[Endis]

hello there,
I have two tables with a one to many relationship. The advertisers table includes leads/potential advertisers so there are not adverts records for all advertisers records. I need to query the database to bring back a listing of most recent advert for an advertisers and all potential advertisers by cagegory using the following query

SELECT DISTINCT adverts.ref IS NULL AS isnull, adverts.ref, advertisers.name FROM adverts RIGHT JOIN advertisers ON adverts.busid = advertisers.id WHERE maincat=19 ORDER BY isnull ASC, MID(ref,4,2) DESC, name ASC;

The IS NULL ensures to order the records by advertisers with adverts first. Including the adverts.ref field I get the records in the right order but a record for each of the adverts an advertiser might have, however if I take this out I get all the records correctly but in an order that I cannot work out.

Any ideas would be helpful. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[ishac]

May be "adverts.ref" value may not be getting what you want. if there is a "ref" for each link to the advertisers table, you will get a distinct entry for each unique value i.e. one record for each advertiser/advert combination.
You need more refined and specific criteria to join between adverts and advertisers. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --