April 25, 2015 at 11:16 pm
Ok so SQL isn't my strong point and I'm literally going to loose my mind in a minute.
The application is an online dating platform whereby the user comes on chooses there gender and selects what they are seeking either a male or female
I have a stored proc which returns me profiles depending on Gender and Seeking.
Gender and Seeking have the same values i,e
Female = 1
Male = 2
I'm my where condition I'm trying to filter three clauses.
1) User is searching for Male for Male or Female for Female
2) Male searching for Female, Female search for a male
This is how my current statement looks.
I pass in to parameters @Gender int and @Seeking int
and (
p.Gender = @Gender and p.Seeking = @Gender -- Male Looking for Male, Female Looking for Female
or
p.Gender != @Gender and p.Seeking = @Gender -- Male looking for Female / Female looking for a male
)
The issue with this code is when I do a female seeking a male it also returns me females seeking females, if I do male seeking male I Just get females returns and it's driving me up the wall would someone be able to help me please.
If someone gave me a pen and paper and asked me to write the condition out it would look like this
// Female seeking Female, Male seeking Male
p.Gender = 1 and p.Seeking = 1 or p.Gender = 2 and p.Seeking = 2
// Female seeking Male, Male seeking Female
p.Gender = 1 and p.Seeking = 2 or p.Gender = 2 and p.Seeking = 1
Update
Below is working as expected, but the problem I now face is when the page loads for the first time If the user hasn't created a profile then we pass in null for @Gender and @Seeking otherwise we get the values from there profile, so I need to some how extend the below to cater for the nulls, can someone help me please
and (
(@Gender = 1 and @Seeking= 1 and p.Gender = @Gender and p.Seeking = @Seeking) -- Female seeking Female
or
(@Gender = 2 and @Seeking = 2 and p.Gender = @Gender and p.Seeking = @Seeking) -- Male seeking Male
or
(@Gender = 1 and @Seeking = 2 and p.Gender = 2 and p.Gender != @Gender and p.Seeking != @Seeking ) -- Female seeking Male
or
(@Gender = 2 and @Seeking = 1 and p.Gender = 1 and p.Gender != @Gender and p.Seeking != @Seeking) -- Male seeking Female
)
If they are both null then I would expect to see everything until the user starts to filter the results.
April 26, 2015 at 1:12 am
Quick thought, I think you are over complicating this, consider this code
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE INT = 1000;
IF OBJECT_ID(N'dbo.TBL_GENDER_SEEKING') IS NOT NULL DROP TABLE dbo.TBL_GENDER_SEEKING;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
ISNULL(NM.N,0) AS GS_ID
,(ABS(CHECKSUM(NEWID())) % 2) + 1 AS GENDER
,(ABS(CHECKSUM(NEWID())) % 2) + 1 AS SEEKING
INTO dbo.TBL_GENDER_SEEKING
FROM NUMS NM;
ALTER TABLE dbo.TBL_GENDER_SEEKING ADD CONSTRAINT PK_DBO_TBL_GENDER_SEEKING_GS_ID PRIMARY KEY CLUSTERED (GS_ID ASC);
/* Unknown - List all */
DECLARE @GENDER INT = NULL;
DECLARE @SEEKING INT = NULL;
IF @GENDER IS NULL AND @SEEKING IS NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS;
END
IF @GENDER IS NOT NULL AND @SEEKING IS NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.SEEKING = @GENDER;
END
IF @GENDER IS NULL AND @SEEKING IS NOT NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.GENDER = @SEEKING;
END
IF @GENDER IS NOT NULL AND @SEEKING IS NOT NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.GENDER = @SEEKING
AND GS.SEEKING = @GENDER;
END
/* User is Female - List all seeking a Female */
SET @GENDER = 2;
SET @SEEKING = NULL;
IF @GENDER IS NULL AND @SEEKING IS NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS;
END
IF @GENDER IS NOT NULL AND @SEEKING IS NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.SEEKING = @GENDER;
END
IF @GENDER IS NULL AND @SEEKING IS NOT NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.GENDER = @SEEKING;
END
IF @GENDER IS NOT NULL AND @SEEKING IS NOT NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.GENDER = @SEEKING
AND GS.SEEKING = @GENDER;
END
/* User seeks Male - List all Males */
SET @GENDER = NULL;
SET @SEEKING = 1;
IF @GENDER IS NULL AND @SEEKING IS NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS;
END
IF @GENDER IS NOT NULL AND @SEEKING IS NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.SEEKING = @GENDER;
END
IF @GENDER IS NULL AND @SEEKING IS NOT NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.GENDER = @SEEKING;
END
IF @GENDER IS NOT NULL AND @SEEKING IS NOT NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.GENDER = @SEEKING
AND GS.SEEKING = @GENDER;
END
/* User is Female seeking a Male - List all Males seeking a Femmale */
SET @GENDER = 2;
SET @SEEKING = 1;
IF @GENDER IS NULL AND @SEEKING IS NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS;
END
IF @GENDER IS NOT NULL AND @SEEKING IS NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.SEEKING = @GENDER;
END
IF @GENDER IS NULL AND @SEEKING IS NOT NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.GENDER = @SEEKING;
END
IF @GENDER IS NOT NULL AND @SEEKING IS NOT NULL
BEGIN
SELECT
GS.GS_ID
,GS.GENDER
,GS.SEEKING
FROM dbo.TBL_GENDER_SEEKING GS
WHERE GS.GENDER = @SEEKING
AND GS.SEEKING = @GENDER;
END
April 27, 2015 at 9:51 am
I think (hopefully) all the conditions except a NULL (incoming) @Gender can be handled by:
(@Seeking IS NULL OR p.Gender = @Seeking) AND p.Seeking = @Gender
What "matches" do you want to show for a NULL incoming @Gender?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply