Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-05-04 : 09:09:02
|
| dan urban writes "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" |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-05-04 : 10:08:52
|
| You need to define exactly what you want the output to look like for someone to answer your question. Show some sample output without a distinct or group by, and then show the disired output for the same data.Here is the basic idea with t-sql GROUP BY:If you use group by you don't need DISTINCTAny non aggregated column displayed must be included in the group by clauseAny column not in the group by clause can only be (not shown or aggregated)Read about GROUP BY and aggregate functions in Books OnlineBe One with the OptimizerTG |
 |
|
|
|
|
|
|
|
sql select distinct/groupby