Link to home
Start Free TrialLog in
Avatar of David Glover
David GloverFlag for United Kingdom of Great Britain and Northern Ireland

asked on

select only where a result exists in all joins?

Hopefully this shows what I am trying to do... I need to join the list of groups in the Tblsearch to the groups in the tblData table but return a row only where the is matched on all other joins.
My approach to date has been to select for each row in the tblsearch the items from tblData with matching groupID and then inner join all the results but this somewhat messy to construct and not that good performance.   My real world data typically has only 5-10 rows (values in the search but up to about a million rows in the data table).
 User generated imageAllGroups.xls
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Glover

ASKER

Hi Paul, this is nearly exactly what I wanted, I think I misled you to go too far with the solution.
I provided 2 examples in my sheet but I was actually only interested in a one at a time scenario , they didn't need unionizing, I was just trying to give 2 different examples.
Would you be so kind as to simplify your solution so it would work with a given tblSearch and a tblData.

Thank you so much Paul.
I think solved this :
	select d.item from tbldata d 
	inner join(select Item, count(*) over(partition by 1) as tblcount from tblSearch2 s 
	on d.GroupID = s.Item
    group by
       d.item
    having    
       count(*) = max(s.tblcount)

Open in new window

That's about as efficient as I can get it isn't it Paul?
Yes, I believe it is.
More than I needed, thanks Paul!