Query with 5 tinyint fields 2

[chrissparkle]

I have a table of members which has 5 tinyint fields labelled Love, Friendship, Marriage, Casual, Anything (these are options that the member might be looking for). A member can select one or all of them (if it's selected it's 1, if not it's 0).

I'm doing my search proc at the moment and am giving the user the option tick which of the 5 they would like to search on - they can tick any number of them.

How do I do this in the SQL query though? I thought it would simple until I actually came to write it and realised it wasn't so simple.

What I think I need to do is just search on what the user has wanted to search for. So if a user wants to search on Love and Friendship I think my SQL should look something like this:

SELECT * From Members
Where (Love = 1 OR Friendship = 1)

That's easy enough, but I don't know how to exclude what is not being searched on because I'm passing in all 5 variables into my proc, and they'll each either be 1 or 0 depending on if they've been ticked.

So I guess my question is how can I include only what I want to search on, and exclude those fields from the WHERE clause that are 0?

 

[gmmastros]

Maybe something like this...

Alter Proc GetMembers
    @Love TinyInt,
    @Friendship TinyInt
As
SET NOCOUNT ON

Select * 
From   Members
Where  (@Love = 0 or Love = 1)
       And (@Friendship = 0 or Friendship = 1)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Your design is not normalized at all. What if you want to add another value perhaps Co-worker? are you going to change all your procs and tables? No, you normalize

here is an example

create table [User](UserID int)
insert into [User]
select 1
union all
select 2


Create Table Selections (SelectionID int ,SelectionValue varchar(50))
insert Selections 
select 1, 'love' union all
select 2, 'marriage' union all
select 3, 'casual'

create table UserSelections(UserID int, SelectionID int)
insert  UserSelections (UserID,SelectionID)
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 3,1 union all
select 3,2 


select * from Selections s 
join UserSelections us on s.SelectionID = us.SelectionID
join [User] u on us.UserID = u.UserID
WHERE s.SelectionValue in ('love','marriage')

Of course you still need indexes etc

you probably can't redesign so to answer you question

SELECT * From Members
Where (Love = 1 OR Friendship = 1)

will return all users who have Love OR Friendship

SELECT * From Members
Where (Love = 1 AND Friendship = 1)

will return all users who have Love AND Friendship

So if I search for Love and friendship am I supposed to get users who have both or either?



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[chrissparkle]

The 5 options on the search form are listed as checkboxes. So you can choose to search for people who have listed either Love, Friendship, Business, Physical or Anything. If you select Love and Friendship, then I want the db to search for members who have put either Love or friendship in. It doesn't matter what else they have listed down - just as long as they have one of the identifiers that the user has chosen to search for.

Hence why this would be correct:

SELECT * From Members
Where (Love = 1 OR Friendship = 1)

That would bring back all members who have either love or friendship ticked, as that is what you have searched for.

My problem is that I need to construct that where clause inside the proc based on what combination of the 5 options you've ticked in the search form and passed through to the proc.

 

[ESquared]

IF @Anything = 1 BEGIN
   SET @Love = 1
   SET @Friendship = 1
   SET @Marriage = 1
   SET @Casual = 1
END

SELECT * From Members
WHERE
   Love + @Love = 2
   OR Friendship + @Friendship = 2
   OR Marriage + @Marriage = 2
   OR Casual + @Casual = 2
   OR Anything = 1

But I strongly suggest you normalize your data. Adding another type or renaming one would force you to change all your code... a very bad thing. Plus, querying this way is very difficult as you have discovered.

I'm not sure if the math method would be superior to ANDs and ORs... you'll have to try it. But in any case, I don't think this is the right way to do it.

 

[chrissparkle]

I don't quite follow what you have done there - can you explain it out? "Anything" is an option just like the other 4 - ie, it's doesn't mean "any of the options".

 

[ESquared]

oh, then in that case it's simpler.

SELECT * From Members
WHERE
   Love + @Love = 2
   OR Friendship + @Friendship = 2
   OR Marriage + @Marriage = 2
   OR Casual + @Casual = 2
   OR Anything + @Anything = 2

Look at it like this. The flags being passed in (@Love, @Friendship and so on) can be represented as flags like this:
[tt]
Desired flags: 1 1 0 0 1
Potential match: 0 1 1 0 1
+ ---------
1 2 1 0 2
[/tt]

You can see that by adding them together, matches are indicated by the sum of 2 where the desired flag and the potential match flag are both 1. A match is found!

Another way to do it with simple boolean logic is:

SELECT * From Members
WHERE
   (Love = 1 AND @Love = 1)
   OR (Friendship = 1 AND @Friendship = 1)
   OR (Marriage = 1 AND @Marriage = 1)
   OR (Casual = 1 AND @Casual = 1)
   OR (Anything = 1 AND @Anything = 1)

Yet another way might be to use bit operands:

SELECT * From Members
WHERE
   (
      (Love & @Love)
      + (Friendship & @Friendship)
      + (Marriage & @Marriage)
      + (Casual & @Casual)
      + (Anything & @Anything)
   ) >= 1

But I still urge you, as strongly as possible, to normalize your data and make these vertical in a one-to-many table instead of horizontal.

 

[chrissparkle]

Thanks Esquared - great simple stuff.

 

[chrissparkle]

Would you be kind enough to post an example scenario of how this would work if the data was normalized?

 

[ESquared]

SQLDenis deserves a star, he already gave you most of what you'd need:

create table [User](UserID int)
insert into [User]
select 1
union all
select 2


Create Table Selections (SelectionID int ,SelectionValue varchar(50))
insert Selections 
select 1, 'love' union all
select 2, 'marriage' union all
select 3, 'casual'

create table UserSelections(UserID int, SelectionID int)
insert  UserSelections (UserID,SelectionID)
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,3

So let's say you want to find any users who are looking for love or marriage:

SELECT
   U.* -- everything from the user table, including name perhaps
FROM
   User U
   INNER JOIN (
      SELECT DISTINCT UserID
      FROM UserSelections
      WHERE SelectionID IN (2, 3)
   ) S ON U.UserID

Or let's say you're working with UserID @UserID and want all users who match his preferences:

SELECT
   U.* -- everything from the user table, including name perhaps
FROM
   User U
   INNER JOIN (
      SELECT DISTINCT S2.UserID
      FROM
         UserSelections S1
         INNER JOIN UserSelections S2 ON S1.SelectionID = S2.SelectionID
      WHERE
         S1.UserID = @UserID
         AND S2.UserID <> @UserID
   ) S ON U.UserID

And this code doesn't have to change no matter how many selection criteria you add, 5, 50, or 5000.

 

[CosmoDynamics]

I've been following this post and it looks like 2 of you are real experts!

I have a social networking website where i have about 50000 members and at some points reach 600 concurrent users.

I am going to redo the whole database structure from scratch and would appreciate any comments and tips on what to do and what not to.

Your help is much appreciated.

 

[ESquared]

Tips:

• Normalize properly. At the same time, use the simplest possible design that could work.
• Use types and subtypes for things which can operate in different roles (a person can be a customer or an employee or both) or participate with different-type things in alternating ways (first you sell some parts to a vendor, then that vendor is your supplier of a finished product, and some are invididual people and some are businesses) instead of a different table for each one. (Have a Party table with a unique ID for every person, company, or community that you model. In other tables that can only refer to one type, you still only use the ID that was uniquely created for the party.) Search for "subtyping" and "universal data models" but don't end up with a single key/value pair table for every thing in the database.
• Test alternate query methods to find the ones with the lowest execution times.
• Always use primary/foreign key constraints. This will save you from really big headaches some day. Trust me.
• Use appropriate indexes, fill factors, file groups. Use small data types for primary keys. Avoid keys such as SSNs which not every person has and are not guaranteed to be unique (Joe Celko is sometimes stupidly wrong).
• Pre-process or denormalize strategically. (Advanced and experienced people only, data integrity is FAR more important than an unproven advantage in speed. A slow site is annoying and can at least have hardware thrown at it or pay for some query and index tuning help. Corrupted data or mistreated customers due to that corruption is expensive and can harm a business pronto.)

And that's just for starters...

 

[CosmoDynamics]

ESquared thanks for your prompt response.

I currently have an existing MS SQL 2000 database which has no contraints what so ever. I am worried though that it may be too much when i have 1000 people on the site at the same time.

I see your points, and let me start with a simple question. I have a members table which has 55000 rows in it. It also holds some of the members profile information and also relates it to some other profile tables.

Regarding logging in speed and such stuff, what are your recommendations, having in mind that this table will eventually reach about 200000 members.

Thanks,
Alexander

 

[ESquared]

With appropriate indexes you can have excellent speed no matter how big the table.

Hardware is also a consideration. I don't know where you're hosting, but if necessary maybe you can find better equipment.

Or maybe you could experiment with splitting your table vertically so the columns required for login are in their own table.

I do not believe that constraints would slow your database down appreciably. I am always surprised during development the number of times I would have violated foreign key constraints unknowingly if they hadn't been present.

At the very least, you should develop with constraints, and then if you must you can remove them for deployment. But you should regularly make a backup of your database and attempt to apply all the constraints to a separate restored copy to ensure you don't have a database integrity problem. Or if you really want to go to the trouble you could make queries which check for integrity problems but the constraints method is far simpler, much more reliable, and a lot easier to maintain and update.

In a many-user environment, when you're developing, always develop for lowest CPU usage. Total execution time, especially, is not a reliable marker of the impact on your server. Query cost is helpful, but CPU usage is king. It doesn't matter (below a certain point) how long each user takes to log in, what matters is that this number doesn't hcange appreciable when you double the number of users.