select statement with Distinct and or GroupBy

Question

select statement with Distinct and or GroupBy

durban

SSC Journeyman

Points: 90
More actions
May 2, 2006 at 1:41 pm

#168118

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 5, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

If you want a single row per FILM_PROD_GUDE then you will have to reduce FILM_CATEGORY and FILM_PROD_GUIDE to single rows as well (using some form of aggregation MIN, MAX etc)

Can you explain what you are trying to achieve (table ddl, test data and expected results).

Far away is close at hand in the images of elsewhere.
Anon.

durban SSC Journeyman Points: 90 More actions · Answer 2

I want to retrieve a distinct Film_prod_guide.recno. I do not want multiple instances of the same recno. It can be possible to have and ACTIVE=1 for either the Guide record (master) or the Category record (child) or both, in which case I only want to display one instance of the master. Hope that helps.

dan

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Still not sure what the exact problem is but if you only want unique masters then

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

if you include

FILM_SUBCAT_REF.SUBNO

FILM_SUBCAT_REF.SUBCAT

FILM_CATEGORY.CREW_NO

FILM_CATEGORY.CREW_DESCRIPTION

you will get duplicated master data if relationship between FILM_SUBCAT_REF/FILM_CATEGORY and FILM_CATEGORY/FILM_PROD_GUIDE is not 1:1

Other than that it is difficult to see the problem without sample data and expected results

Far away is close at hand in the images of elsewhere.
Anon.