select statement and Distinct and or Group By

[durban]

I am trying to return a single referance to a each unique master record (FILM_PROD_GUDE .RECNO). Because I am returning fields with different values from related tables (FILM_SUBCAT_RE F.SUBNO), I am returning multiple instances of the master record RECNO. Can I use a GROUP BY function to get what I want or am I looking at this whole thing the wrong way.

Any help would be appreciated.... ............... .

thanks dan

"SELECT DISTINCT
FILM_PROD_GUIDE .RECNO, FILM_SUBCAT_REF .SUBNO, FILM_PROD_GUIDE .ORGANIZATION, FILM_PROD_GUIDE .CON_FIRST, FILM_PROD_GUIDE .CON_PFX, FILM_PROD_GUIDE .CON_LAST, FILM_PROD_GUIDE .STREET_ONE, FILM_PROD_GUIDE .CITY, FILM_PROD_GUIDE .STATE, FILM_PROD_GUIDE .ZIP, FILM_PROD_GUIDE .PHONE_DAY, FILM_SUBCAT_REF .SUBCAT, FILM_CATEGORY.C REW_NO, FILM_CATEGORY.C REW_DESCRIPTION

FROM
FILM_SUBCAT_REF

INNER JOIN FILM_CATEGORY ON FILM_SUBCAT_REF .SUBNO = FILM_CATEGORY.S UBCAT_KEY

INNER JOIN FILM_PROD_GUIDE ON FILM_CATEGORY.O RG_NO = FILM_PROD_GUIDE .RECNO

WHERE FILM_PROD_GUIDE .RECNO > 0

AND FILM_SUBCAT_REF .SUBNO= " + Int64.Parse(Sub Cat.Text.ToStri ng())

" AND LOWER(ORGANIZAT ION) LIKE '%" + myName.ToLower( ) + "%'"

" AND LOWER(CITY) LIKE '%" + myCity.ToLower( ) + "%'"

" AND LOWER(CON_LAST) LIKE '%" + myLast.ToLower( ) + "%'"

" AND ORG_ACTIVE = 1

AND CREW_CAT_ACTIVE = 1

ORDER BY SUBCAT,ORGANIZA TION

[Banfa]

Since you are only interested in the master record (the data from FILM_PROD_GUIDE ) don't select any columns from the other tables, for instance FILM_SUBCAT_REF .SUBNO as these are causing you to get multiple rows for the same master record.


"SELECT DISTINCT
FILM_PROD_GUIDE .RECNO, FILM_PROD_GUIDE .ORGANIZATION, FILM_PROD_GUIDE .CON_FIRST, FILM_PROD_GUIDE .CON_PFX, FILM_PROD_GUIDE .CON_LAST, FILM_PROD_GUIDE .STREET_ONE, FILM_PROD_GUIDE .CITY, FILM_PROD_GUIDE .STATE, FILM_PROD_GUIDE .ZIP, FILM_PROD_GUIDE .PHONE_DAY

FROM
FILM_SUBCAT_REF

INNER JOIN FILM_CATEGORY ON FILM_SUBCAT_REF .SUBNO = FILM_CATEGORY.S UBCAT_KEY

INNER JOIN FILM_PROD_GUIDE ON FILM_CATEGORY.O RG_NO = FILM_PROD_GUIDE .RECNO

WHERE FILM_PROD_GUIDE .RECNO > 0

AND FILM_SUBCAT_REF .SUBNO= " + Int64.Parse(Sub Cat.Text.ToStri ng())

" AND LOWER(ORGANIZAT ION) LIKE '%" + myName.ToLower( ) + "%'"

" AND LOWER(CITY) LIKE '%" + myCity.ToLower( ) + "%'"

" AND LOWER(CON_LAST) LIKE '%" + myLast.ToLower( ) + "%'"

" AND ORG_ACTIVE = 1

AND CREW_CAT_ACTIVE = 1

ORDER BY SUBCAT,ORGANIZA TION