SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding combinations of values


Finding combinations of values

Author
Message
Robin
Robin
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 302
My table structure

CREATE TABLE trefClientShares{
intLMid int NULL,
intMLid int NULL,
intISid int NULL,
intFRid int NULL
}

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23307 Visits: 9730
delete from MyTable
where Col1 is null and Col2 is null and Col3 is null
or Col1 is null and Col2 is null and Col4 is null
or Col1 is null and Col3 is null and Col4 is null
or Col2 is null and Col3 is null and Col4 is null;



Seems like that should do it, right?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Robin
Robin
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 302
That did it. I have no idea why I was unable to see the solution without asking. Brain fry. Everything starting to look the same!!! Thanks!
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23307 Visits: 9730
You're welcome. Glad I could help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7782 Visits: 7140
Here's another possible approach, in case you ever have to do (many) more columns :-) :



DELETE FROM dbo.trefClientShares
WHERE
CASE WHEN intLMid IS NULL THEN 1 ELSE 0 END +
CASE WHEN intMLid IS NULL THEN 1 ELSE 0 END +
CASE WHEN intISid IS NULL THEN 1 ELSE 0 END +
CASE WHEN intFRid IS NULL THEN 1 ELSE 0 END >= 3



SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7782 Visits: 7140
CELKO (12/13/2012)
My table structure


Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.

CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.


No, you need to figure out a lot more than that!

DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;



We don't need a key to solve this problem, so no need for irrelevancies just to satisfy ueber-pedants.

Please read one book on SQL Server because IFNULL() does not exist in SQL Server.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23307 Visits: 9730
CELKO (12/13/2012)
My table structure


Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.

CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.


No, you need to figure out a lot more than that!

DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;


IFNULL is a MySQL item. In SQL Server, you'd use IsNull or Coalesce.

Also, this solution (Joe's Where clause) is completely wrong.

Let's assume none of the columns are null, and all have positive integer values. For example, they all have 10 as their value. 10+10+10+10 = 40. 40 >= 2, so that row would be deleted. But it's not supposed to be. None of the columns are null in that row.

Total failure to understand the requirements, Joe.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7782 Visits: 7140
GSquared (12/14/2012)
CELKO (12/13/2012)
My table structure


Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.

CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.


No, you need to figure out a lot more than that!

DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;


IFNULL is a MySQL item. In SQL Server, you'd use IsNull or Coalesce.

Also, this solution (Joe's Where clause) is completely wrong.

Let's assume none of the columns are null, and all have positive integer values. For example, they all have 10 as their value. 10+10+10+10 = 40. 40 >= 2, so that row would be deleted. But it's not supposed to be. None of the columns are null in that row.

Total failure to understand the requirements, Joe.



Good points!

Besides which, I believe my earlier post was the same idea, except coded correctly :-) .

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search