Select query help

Does changing to SELECT DISTINCT help?

Actually testing it against our environment and it looks like you have multiple keywords on the perfs producing the duplicates. Try:

SELECT p.perf_dt,p.perf_no,
CASE 
WHEN tkw.tkw =8 THEN 'cart.mnhs.org/.../SelectSeating.aspx as varchar(10))

END

FROM t_perf p
LEFT JOIN tx_inv_tkw tkw on p.perf_no = tkw.inv_no AND tkw.tkw = 8

WHERE p.prod_season_no=7246

ORDER BY p.perf_dt

Actually testing it against our environment and it looks like you have multiple keywords on the perfs producing the duplicates. Try:

SELECT p.perf_dt,p.perf_no,
CASE 
WHEN tkw.tkw =8 THEN 'cart.mnhs.org/.../SelectSeating.aspx as varchar(10))

END

FROM t_perf p
LEFT JOIN tx_inv_tkw tkw on p.perf_no = tkw.inv_no AND tkw.tkw = 8

WHERE p.prod_season_no=7246

ORDER BY p.perf_dt

I was just going to chime in and suggest that as the likely issue.  Using a "select distinct" might clear that up for you the easiest, but I'm going to offer some other advice:

1) Avoid using LEFT (OUTER) JOIN by default: only use it when you know for certain that what you need will not always find a match.   INNER JOIN is, of course, faster, as well, but in the long run you're going to wind up with a lot of problems with unthinking LEFT JOINs because you're returning dubious rows or missing values unexpectedly.  T_INVENTORY will always have a match in T_PERF, and if it doesn't, Tessitura is broken and you need to know.

2) In theory, this might give you a ton of rows you don't want (rows where the perf does not have the keyword in question, where you'll just get perf_dt, perf_no and a blank.  Instead you want to use INNER JOIN to your advantage:

inner join TX_INV_TKW as tkw on i.inv_no = tkw.inv_no and tkw.tkw = 8

Now each row will be sure to be a performance with the desired keyword attached, you should only get one row per perf, and you don't need a time-consuming CASE in your select set.

3) This all works well if you have a rule that the keyword in question is always applied at the performance level.  However, keywords are expected to be applied at any production element level, and then be "inherited" by all descendant production elements, so if the keyword is applied at the production level, Tessitura considers the keyword to be applied by default to all production seasons under that production, and all performances under each production season.  If you need to be able to be able to match that functionality, I'm afraid you'll need a more complicated query.  Weirdly, I don't think there's a useful built-in view for this, but I think I've made one in the past I could dig up if you needed it.

Just changing the SQL for the however part of 3 would look like this

SELECT
     p.perf_dt,
     p.perf_no,
     url='cart.mnhs.org/.../SelectSeating.aspx?p=' + convert(varchar(10), p.perf_no)

FROM t_perf p
join t_prod_season ps on p.prod_season_no = ps.prod_season_no
JOIN t_inventory i on (p.perf_no = i.inv_no or p.prod_season_no = i.inv_no or ps.prod_no = i.inv_no)
JOIN tx_inv_tkw tkw on i.inv_no = tkw.inv_no and tkw.tkw =8

WHERE p.prod_season_no=7246
ORDER BY p.perf_dt

adding t_prod_season in to get production number and then changing the (inner) join on t_inv to be an or statement matching each of the 3 possible values to inv_no

Or you can use the following sql as the basis for a View matching perf_no to all tkw that are associated with it

SELECT distinct p.perf_no,
               tkw.tkw
FROM t_perf p
join t_prod_season ps on p.prod_season_no = ps.prod_season_no
join T_PRODUCTION pr on pr.prod_no = ps.prod_no
JOIN t_inventory i on (p.perf_no = i.inv_no or p.prod_season_no = i.inv_no or ps.prod_no = i.inv_no or pr.title_no = i.inv_no)
JOIN tx_inv_tkw tkw on i.inv_no = tkw.inv_no

Mark

Here's my standby: I'm doing a lot of extra work here to screen out out-dated performances and also using secured views so that the view can be used by a procedure in a report or utility that is user-facing.

select
	perf.perf_no, perf.perf_code,
	prod_season.prod_season_no,
	prod.prod_no,
	title.title_no,
	case
		when itkw.inv_no = perf.perf_no then 'R'
		when itkw.inv_no = prod_season.prod_season_no then 'S'
		when itkw.inv_no = prod.prod_no then 'P'
		when itkw.inv_no = title.title_no then 'T'
		else 'X' end as production_element, --same codes used by web content
	tkw.category as keyword_category_id,
	tkwc.description as keyword_category,
	tkw.id as keyword_id,
	tkw.description as keyword
from
	vs_perf as perf
	inner join vs_prod_season as prod_season on prod_season.prod_season_no = perf.prod_season_no
	inner join t_production as prod on prod.prod_no = prod_season.prod_no
	inner join t_title as title on title.title_no = prod.title_no
	inner join vrs_season as season on (season.inactive <> 'Y' and season.id = prod_season.season)
	inner join vs_campaign as campaign on (campaign.inactive <> 'Y' and campaign.campaign_no = perf.campaign_no)

	inner join tx_inv_tkw as itkw on (itkw.inv_no = perf.perf_no or
		itkw.inv_no = prod_season.prod_season_no or
		itkw.inv_no = prod.prod_no or
		itkw.inv_no = title.title_no)
	inner join vrs_tkw as tkw on tkw.id = itkw.tkw
	inner join tr_tkw_category as tkwc on tkwc.id = tkw.category

Bear in mind that this view will return multiple rows if a keyword is applied to more than one element in the production elements hierarchy, but you can control for that by specifying which level you are interested in matching (i.e. R, S, P or T).

Sara,

Thank you so much, that did the trick.  Appreciate the response.