By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

select statement and Distinct and or Group By

P: 1
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_REF.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.CREW_NO, FILM_CATEGORY.CREW_DESCRIPTION

FROM
FILM_SUBCAT_REF

INNER JOIN FILM_CATEGORY ON FILM_SUBCAT_REF.SUBNO = FILM_CATEGORY.SUBCAT_KEY

INNER JOIN FILM_PROD_GUIDE ON FILM_CATEGORY.ORG_NO = FILM_PROD_GUIDE.RECNO

WHERE FILM_PROD_GUIDE.RECNO > 0

AND FILM_SUBCAT_REF.SUBNO= " + Int64.Parse(SubCat.Text.ToString())

" AND LOWER(ORGANIZATION) 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,ORGANIZATION
May 2 '06 #1
Share this Question
Share on Google+
1 Reply


Banfa
Expert Mod 5K+
P: 8,916
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.SUBCAT_KEY

INNER JOIN FILM_PROD_GUIDE ON FILM_CATEGORY.ORG_NO = FILM_PROD_GUIDE.RECNO

WHERE FILM_PROD_GUIDE.RECNO > 0

AND FILM_SUBCAT_REF.SUBNO= " + Int64.Parse(SubCat.Text.ToString())

" AND LOWER(ORGANIZATION) 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,ORGANIZATION
May 3 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.