Advanced SQL statement... 1

[lidds]

The need to get some data from my SQL table, the data I need to get returned depends on the follow condition:

1) Return all data from table where commCommited column = 'Yes' unless commCommited column = 'No' and userCreatedID = @userID then return this data as well.

@userID is the uniqueID value of the user within the userTbl

e.g. Using image of table provided

If @userID = 2 then this will return all data EXCEPT rows 34 and 40 because commCommited = 'No' and userCreatedID = 3 (which is NOT the value of @userID)
If @userID = 3 then this will return all data INCLUDING rows 34 and 40 because commCommited = 'No' and userCreatedID = 3 (which IS the value of @userID)

Is there a way that I can do this within SQL??



Thanks for all your help guys

Simon

 

[gmmastros]

I assume the commCommited column is a bit data type. As such...

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   commentsTbl
[COLOR=blue]Where[/color]  (commCommited = 1
       Or userCreatedId = @userId)

If it's really a varchar type column...

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   commentsTbl
[COLOR=blue]Where[/color]  (commCommited = [COLOR=red]'Yes'[/color]
       Or userCreatedId = @userId)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lidds]

George,

The problem with that SQL statement is that it will only return all data with commCreated = 'Yes' or data where the userCreatedID = @userID (e.g. only data that the user has created). However what I want is:

1) All data returned that has commCommited = 'Yes' and additionally data where commCommited = 'No' and the userCreatedID = @userID (e.g. the @userID matches fields where commCommited = 'No')

I hope that makes sense?

Thanks

Simon

 

[RiverGuy]

George's query should work for you if your only possible values in commCommited are 'Yes' and 'No'. If not, try this:

SELECT * FROM YourTable
WHERE (commCommited = 'Yes')
OR (commCommited = 'No' AND userCreatedID = @UserID)

 

[gmmastros]

Did you run the query? If you didn't, I would encourage you to do so.

I think that I accurately understand your requirements. As such, I took the liberty to test the code before replying. In the code I show below, I create a table variable and then hard code the values that you show in your table. This means that you can copy/paste the code to a SQL Server Management Studio Query window and run it. As you can see, it returns the results that you stated (in your original question).

[COLOR=blue]Declare[/color] @commentsTbl [COLOR=blue]Table[/color](Id [COLOR=blue]Int[/color], commId [COLOR=blue]Int[/color], userCreatedId [COLOR=blue]int[/color], commCommited [COLOR=blue]bit[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](31,1,2,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](32,1,2,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](33,2,2,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](35,3,3,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](36,4,3,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](37,5,3,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](34,1,3,0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](38,3,2,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](39,4,2,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](40,5,3,0)

[COLOR=blue]Declare[/color] @userId [COLOR=blue]Int[/color]
[COLOR=blue]Set[/color] [!]@UserId = 3[/!]

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   commentsTbl
[COLOR=blue]Where[/color]  (commCommited = [COLOR=red]'Yes'[/color]
       Or userCreatedId = @userId)

I absolutely agree that you could make this more complicated if you wanted to. In my opinion, making this more complicated will only slow down the query, while returning the same results.

If @userID = 2 then this will return all data EXCEPT rows 34 and 40 because commCommited = 'No' and userCreatedID = 3 (which is NOT the value of @userID)

Anyway, let's take a closer look at the where clause.
Assume @userId = 2 and row 34 data.

[tt]
Where (commCommited = 'Yes' Or userCreatedId = @userId)
Where ('No' = 'Yes' Or 3 = @UserId)
Where (False Or 3 = 2)
Where (False Or False)
Where (False)
[/tt]

This row will not be returned.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrissie1]

Normally we would give George a star for his exert explanation
, but he already has plenty of those, so we don't ;-).

Christiaan Baes
Belgium

My Blog

 

[gmmastros]

The example I posted was bad. Sorry. I had the commCommited column as bit, but treated it as a varchar in the query. Here's a corrected version.

[COLOR=blue]Declare[/color] @commentsTbl [COLOR=blue]Table[/color](Id [COLOR=blue]Int[/color], commId [COLOR=blue]Int[/color], userCreatedId [COLOR=blue]int[/color], commCommited [COLOR=blue]VarChar[/color](3))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](31,1,2,[COLOR=red]'Yes'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](32,1,2,[COLOR=red]'Yes'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](33,2,2,[COLOR=red]'Yes'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](35,3,3,[COLOR=red]'Yes'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](36,4,3,[COLOR=red]'Yes'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](37,5,3,[COLOR=red]'Yes'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](34,1,3,[COLOR=red]'No'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](38,3,2,[COLOR=red]'Yes'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](39,4,2,[COLOR=red]'Yes'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @commentsTbl [COLOR=blue]Values[/color](40,5,3,[COLOR=red]'No'[/color])

[COLOR=blue]Declare[/color] @userId [COLOR=blue]Int[/color]
[COLOR=blue]Set[/color] @UserId = 2

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @commentsTbl
[COLOR=blue]Where[/color]  (commCommited = [COLOR=red]'Yes'[/color]
       Or userCreatedId = @userId)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrissie1]

I want my star back

Christiaan Baes
Belgium

My Blog

 

[lidds]

George,

My mistake, I was adding your SQL onto another SQL statement and I missed the backets P

Works like a charm, Thank you so much for your help

Simon

 

[dswitzer]

LOL