where clause that matches multiple rows

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

where clause that matches multiple rows

Author

Topic

trondaron
Starting Member

13 Posts

Posted - 2009-07-09 : 15:07:28

My table is like this
profileID int
sourceID int
min int
max int
isFlag bit

profileID is a foreign key
profileID + sourceID is a unique key

sample data would look like this

profileID   sourceID   min    max   isFlag
---------------------------------------------
   100          1        5     10     0
   100          2        5     10     0
   100          3        8     10     0

   101          2        5     10     0
   101          3        0     10     0

   102          1        0     20     1
   102          4        0     20     1

   103          1        5     10     0
   103          2        5     10     0
   103          3        8     10     0
   103          4        0     20     1

I would like to match a specific set of rows and retrieve the profileID for them. Examples follow using above data layout:
----

Send            2        0     15    1

Returns: No match/null/empty whatever
----

Send            1        0     20     1
                4        0     20     1

Returns: profileID 102
----

Send            1        5     10     0
                2        5     10     0
                3        8     10     0

Returns profileID 100 (and does not return profileID 103 even though this is a subset of it's data)
----
Alternative table structures are also welcome

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-09 : 15:12:20

quote:
I would like to match a specific set of rows and retrieve the profileID for them.

The specific set of rows is in another table or a file? The "Send" in your sample ..what does it mean?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 15:19:52

Create a temp table (called Send in my example) and load it with your Send data then join it:

SELECT 
	MyTable.ProfileID
FROM
	MyTable
INNER JOIN
	Send
	ON MyTable.SourceID = Send.SourceID
	AND MyTable.Min = Send.Min
	AND MyTable.Max = Send.Max
	AND MyTable.IsFlag = Send.IsFlag
GROUP BY
	MyTable.ProfileID
HAVING 
	COUNT(*) = (SELECT COUNT(*) FROM Send)

trondaron
Starting Member

13 Posts

Posted - 2009-07-09 : 15:20:08

The specific set of rows would be supplied, say for instance a 2008 stored procedure that takes datatable.

The examples are meant to work as a sort of black box from my perspective. The rows next to "Send" are the rows that are being supplied to whatever stored procedure/udf/query will do the matching. And "Return" is the answer I am expecting/would like to receive.

trondaron
Starting Member

13 Posts

Posted - 2009-07-09 : 15:22:01

quote:
Originally posted by Lamprey

Create a temp table (called Send in my example) and load it with your Send data then join it:

Going to give this a shot, Thanks for the direction!
I'm still open to other options too. I expect this kind of thing to happen "semi" often and I'd like the processing to not take a long time.

trondaron
Starting Member

13 Posts

Posted - 2009-07-09 : 16:08:49

I'm using a table value parameter in a stored procedure, so far it seems to be behaving properly.

Just to confirm;

quote:
Originally posted by Lamprey
HAVING
COUNT(*) = (SELECT COUNT(*) FROM Send

Is the line that prevent matching a profile that contains the Send table as a subset? So if Send has 3 rows I only get back matches that also have 3 rows.

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-09 : 16:09:03

>> I expect this kind of thing to happen "semi" often

Not that I can recall

and Lampy, the code doesn't quite do it...seems to grab 103 as well



SET NOCOUNT ON
GO

CREATE TABLE myTable99(
	  profileID		int
	, sourceID		int
	, [min]			int
	, [max]			int
	, isFlag		bit
)
GO

INSERT INTO myTable99(profileID, sourceID, [min], [max], [isFlag])
SELECT    100, 1,        5,     10,     0 UNION ALL
SELECT    100, 2,        5,     10,     0 UNION ALL
SELECT    100, 3,        8,     10,     0 UNION ALL
SELECT    101, 2,        5,     10,     0 UNION ALL
SELECT    101, 3,        0,     10,     0 UNION ALL
SELECT    102, 1,        0,     20,     1 UNION ALL
SELECT    102, 4,        0,     20,     1 UNION ALL
SELECT    103, 1,        5,     10,     0 UNION ALL
SELECT    103, 2,        5,     10,     0 UNION ALL
SELECT    103, 3,        8,     10,     0 UNION ALL
SELECT    103, 4,        0,     20,     1
GO

DECLARE @Send table (
	  sourceID		int
	, [min]			int
	, [max]			int
	, isFlag		bit
)


INSERT INTO @Send (sourceID, [min], [max], isFlag)
SELECT 1,        5,     10,     0 UNION ALL
SELECT 2,        5,     10,     0 UNION ALL
SELECT 3,        8,     10,     0

	SELECT t.ProfileID, COUNT(*)
	  FROM MyTable99	t
INNER JOIN @Send		s	
		ON t.SourceID	= s.SourceID
	   AND t.[Min]		= s.[Min]
	   AND t.[Max]		= s.[Max]
	   AND t.IsFlag		= s.IsFlag
  GROUP BY t.ProfileID
	HAVING COUNT(*)		= (SELECT COUNT(*) FROM @Send)
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

trondaron
Starting Member

13 Posts

Posted - 2009-07-09 : 16:13:38

X002548, Thanks for putting it into sql code. In hindsight I should have done this for the examples to promote people's answers. Next time I will.

happening "semi" often is a reference to the query being called often in my program. Basically anytime someone changes their profile it should check if they match someone else's profile.

Thanks again to everyone that is helping!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 16:15:34

Good call, this is one of those things that you run into using the dimensional model in data warehousing, thus why I advocate not using a incomplete/flawed model. I know I have some code floating around that does this, let me take a look-see and get back to you..

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-09 : 16:15:41

We need to merge this into the SQL somehow

SELECT ProfileID, COUNT(*) FROM myTable99 GROUP BY ProfileID

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 16:17:50

Just change the INNER JOIN to LEFT OUTER JOIN.. bah.. slow today.. :)

EDIT: scratch that.. my brain is fried.. let me look for my code again..

trondaron
Starting Member

13 Posts

Posted - 2009-07-09 : 16:19:57

In Lampy's answer
Changing the INNER JOIN to LEFT JOIN produces the desired result. It allows the
HAVING COUNT(*) = (SELECT COUNT(*) FROM @Send)
to return COUNT(*) = 4 for profileID 103 instead of COUNT(*) = 3 being returned when using INNER JOIN.

Guess I was a few seconds slower than Lampy :)

Lampy,
Please help me understand/find resources as to why this model is flawed/incomplete. This came from not wanting to store this profile data for each individual user. I am certainly open to information.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 16:22:48

quote:
Originally posted by trondaron

In Lampy's answer
Changing the INNER JOIN to LEFT JOIN produces the desired result. It allows the
HAVING COUNT(*) = (SELECT COUNT(*) FROM @Send)
to return COUNT(*) = 4 for profileID 103 instead of COUNT(*) = 3 being returned when using INNER JOIN.

That is just an artifact of the data. try this:

SELECT
*
FROM
	(
		SELECT ProfileID, COUNT(*) AS RowCnt FROM myTable99 GROUP BY ProfileID
	) AS A
INNER JOIN
	(
		SELECT t.ProfileID, COUNT(*) AS RowCnt
		  FROM MyTable99	t
		INNER JOIN @Send		s	
			ON t.SourceID	= s.SourceID
		   AND t.[Min]		= s.[Min]
		   AND t.[Max]		= s.[Max]
		   AND t.IsFlag		= s.IsFlag
	  GROUP BY t.ProfileID
		HAVING COUNT(*)		= (SELECT COUNT(*) FROM @Send)
	) AS B
	ON A.ProfileID = B.ProfileID
	AND A.RowCnt = B.RowCnt

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-09 : 16:27:16

OK...so you live in Costa Rica....

I wouldn't worry about it too much

However, when the "model" is being discussed

You should have a Parent table that describes the user
Then a child table you join to, to find the properties about that user

so what is it you are trying to do

You have some kind of profile and you want to find who fits those profiles?

Not sure what you are trying to do?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

trondaron
Starting Member

13 Posts

Posted - 2009-07-09 : 16:51:37

quote:
Originally posted by X002548
You should have a Parent table that describes the user
Then a child table you join to, to find the properties about that user
so what is it you are trying to do
You have some kind of profile and you want to find who fits those profiles?
Not sure what you are trying to do?

User table; userID, name, password, viewProfileID, limitProfileID, and some other user specific data that will often be unique per User

limitProfileID and viewProfileID are FK to a limitProfile and viewProfile table respectively which hold semi unique data. I expect every 100-200 users to have matching profiles.

Source table; sourceID, webpageName, shortName
This table gives an ID to webpage and defines some abbreviations that can be used to refer to the page.

limitProfile table; limitProfileID

limitProfileSettings table; limitProfileID, sourceID, min, max
A user with this limitProfileID will be able to enter values between min and max on the specified "source" or webpage

viewProfile table; viewProfileID

viewProfileSettings table; viewProfileID, sourceID, showFooter, showHeader
A user with this viewProfileID will be shown or now shown header/footers on the specified "source" or webpage

There are additional variables and user tables, but this is a basic representation of what I am creating. I realize some of this may not be clear, I'll do my best to expand on whatever raises questions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources