Query help...

Question

Post reply

Query help...

VGish

SSChasing Mays

Points: 643
More actions
October 3, 2012 at 3:31 pm

#267575

I have a table like this:
Cnum D1 D2D3 D4
1213451112NULLNULL
125678NULL 34NULL67
3456755907856
Another table: Table2
Dvalues
11group1
34group1
67group1
55group1
78group1
90group1
56group1
I want to get Cnum only if D1 and D2 exist in table 2 – for Row1 – this is not, since 12 doesn’t exists in table2.
D2 and D4 exists in table 2
D1, d2, d3, d4 exists in table 2 likewise…
--This gets me only one output which has all the D codes, which exists in Table2.
I need 2 rows which actually qualify for this (2nd and 3rd)…
Select * from table1
Where D1 exists in (select D from table2)
And D2 exists in (select D from table2)
AND D3 exists in (select D from table2)
AND D4 exists in (select D from table2)
Thank you!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

try this...

First some set up - your data - next time please do this for us...

IF OBJECT_ID('tempdb..#cnums') IS NULL

BEGIN

CREATE TABLE #cnums(Cnum bigint,D1 int, D2 int,D3 int, D4 int)

INSERT #cnums

SELECT 121345,11,12,NULL,NULL UNION ALL

SELECT 125678,NULL,34,NULL,67 UNION ALL

SELECT 34567,55,90,78,56

END

IF OBJECT_ID('tempdb..#dnums') IS NULL

BEGIN

CREATE TABLE #dnums(D INT,[values] CHAR(6))

INSERT #dnums

SELECT 11,'group1' UNION ALL

SELECT 34,'group1' UNION ALL

SELECT 67,'group1' UNION ALL

SELECT 55,'group1' UNION ALL

SELECT 78,'group1' UNION ALL

SELECT 90,'group1' UNION ALL

SELECT 56,'group1'

END

Now the solution...

select *

from #cnums cnums

where (

select count(*)

from #dnums dnums

where dnums.D in (cnums.D1,cnums.D2,cnums.D3,cnums.D4)

) = SIGN(ISNULL(D1,0))+SIGN(ISNULL(D2,0))+SIGN(ISNULL(D3,0))+SIGN(ISNULL(D4,0))

This matches the count of "D" numbers found in table2 for any row with the count of non-null D values in the source row.

SIGN is being used to turn a positive number into 1, so it can be added to get a count.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

One more way to achieve the result:

SELECT c.Cnum,c.D1,c.D2,c.D3,c.D4

FROM #cnums c JOIN #dnums d

ON c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D

GROUP BY c.Cnum,c.D1,c.D2,c.D3,c.D4

HAVING Count(*) > 1

- As D1,D2 and D4 is used as the key to analyse data: they form the part of ON clause in inner join

- Need for group by + having clause: There are two conditions; 1) D1&D2 2) D2&D4

A row must satisfy either (1) or (2) or both. Furthermore there are two columns involved in each condition - to satisfy any of the above cirteria count should be greater than 1

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 3

Lokesh Vij (10/3/2012)
One more way to achieve the result:
SELECT c.Cnum,c.D1,c.D2,c.D3,c.D4
FROM #cnums c JOIN #dnums d
ON c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D
GROUP BY c.Cnum,c.D1,c.D2,c.D3,c.D4
HAVING Count(*) > 1
- As D1,D2 and D4 is used as the key to analyse data: they form the part of ON clause in inner join
- Need for group by + having clause: There are two conditions; 1) D1&D2 2) D2&D4
A row must satisfy either (1) or (2) or both. Furthermore there are two columns involved in each condition - to satisfy any of the above cirteria count should be greater than 1

yes , it will work , when no duplicate record is there...slight change ..

select c.Cnum,c.d1 ,c.d2,c.d3,c.d4 from

#dnums d,(SELECT distinct Cnum,D1,D2,D3,D4

FROM #cnums )c

where c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D

GROUP BY Cnum,D1,D2,D3,D4

HAVING Count(*) > 1

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

demonfox (10/4/2012)
Lokesh Vij (10/3/2012)
One more way to achieve the result:
SELECT c.Cnum,c.D1,c.D2,c.D3,c.D4
FROM #cnums c JOIN #dnums d
ON c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D
GROUP BY c.Cnum,c.D1,c.D2,c.D3,c.D4
HAVING Count(*) > 1
- As D1,D2 and D4 is used as the key to analyse data: they form the part of ON clause in inner join
- Need for group by + having clause: There are two conditions; 1) D1&D2 2) D2&D4
A row must satisfy either (1) or (2) or both. Furthermore there are two columns involved in each condition - to satisfy any of the above cirteria count should be greater than 1
yes , it will work , when no duplicate record is there...slight change ..
select c.Cnum,c.d1 ,c.d2,c.d3,c.d4 from
#dnums d,(SELECT distinct Cnum,D1,D2,D3,D4
FROM #cnums )c
where c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D
GROUP BY Cnum,D1,D2,D3,D4
HAVING Count(*) > 1

😀

I intentionally removed that. Here was my original version of the query:

SELECT c.Cnum,c.D1,c.D2,c.D3,c.D4

FROM #cnums c JOIN #dnums d

ON c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D

WHERE ( c.D1 IS NOT NULL OR c.D2 IS NOT NULL )

AND ( c.D2 IS NOT NULL OR c.D4 IS NOT NULL )

GROUP BY c.Cnum,c.D1,c.D2,c.D3,c.D4

HAVING Count(*) > 1

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

VGish SSChasing Mays Points: 643 More actions · Answer 5

Thank you, I liked the mister.magoo's idea, as it is faster. But, I didn't realize that, some of the vlaues in the #dnums are float like 11.20, 34.55 etc...

So, I Am getting Error converting data type varchar to float. tried using converts, but didn't help ???

Thanks!

VGish SSChasing Mays Points: 643 More actions · Answer 6

select c.Cnum,c.d1 ,c.d2,c.d3,c.d4 from

#dnums d,(SELECT distinct Cnum,D1,D2,D3,D4

FROM #cnums )c

where c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D

GROUP BY Cnum,D1,D2,D3,D4

HAVING Count(*) > 1

This doesn't work since c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D

any one of them is true, then get listed.

IF OBJECT_ID('tempdb..#cnums') IS NULL

BEGIN

CREATE TABLE #cnums(Cnum bigint,D1 int, D2 int,D3 int, D4 int, D5 int, D6 int, D7 int)

INSERT #cnums

SELECT 121345,11,12,45,78, NULL, NULL, 33 UNION ALL

SELECT 125678,NULL,34,NULL,67, 78, 55, NULL UNION ALL

SELECT 34567,55,90,78,56, 66,43, 67

END

IF OBJECT_ID('tempdb..#dnums') IS NULL

BEGIN

CREATE TABLE #dnums(D INT,[values] CHAR(6))

INSERT #dnums

SELECT 11,'group1' UNION ALL

SELECT 34,'group1' UNION ALL

SELECT 67,'group1' UNION ALL

SELECT 55,'group1' UNION ALL

SELECT 78,'group1' UNION ALL

SELECT 90,'group1' UNION ALL

SELECT 56,'group1' UNION ALL

SELECT 45,'group1' UNION ALL

SELECT 78,'group1' UNION ALL

SELECT 33,'group1' UNION ALL

SELECT 55,'group1' UNION ALL

SELECT 90,'group1' UNION ALL

SELECT 78,'group1' UNION ALL

SELECT 66,'group1' UNION ALL

SELECT 43,'group1' UNION ALL

SELECT 67,'group1'

END

--select * from #cnums

--select * from #dnums

SELECT c.Cnum,c.D1,c.D2,c.D3,c.D4

FROM #cnums c JOIN #dnums d

ON c.D1 = d.D OR c.D2 = d.D OR c.D4 = d.D

GROUP BY c.Cnum,c.D1,c.D2,c.D3,c.D4

HAVING Count(*) > 1

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

VGish (10/5/2012)
Thank you, I liked the mister.magoo's idea, as it is faster. But, I didn't realize that, some of the vlaues in the #dnums are float like 11.20, 34.55 etc...
So, I Am getting Error converting data type varchar to float. tried using converts, but didn't help ???
Thanks!

Hi, you are going to have to start helping us to help you - what exactly have you tried? post some sample data (like I did for you), post your code...

Without that, we can't help.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

VGish SSChasing Mays Points: 643 More actions · Answer 8

Here what it looks like...and getting Error converting data type varchar to float.

--drop table tempdb..#cnums

--drop table tempdb..#dnums

IF OBJECT_ID('tempdb..#cnums') IS NULL

BEGIN

CREATE TABLE #cnums(Cnum bigint,D1 varchar(6), D2 varchar(6), D3 varchar(6), D4 varchar(6), D5 varchar(6), D6 varchar(6), D7 varchar(6))

INSERT #cnums

SELECT 121345,'11.2','12.4','45','78.9', NULL, NULL, '33' UNION ALL

SELECT 125678,NULL,'34.22',NULL,'67.89', '78', '55', NULL UNION ALL

SELECT 34567,'55.10','90,78','56', '66','43.45', '67','45'

END

IF OBJECT_ID('tempdb..#dnums') IS NULL

BEGIN

CREATE TABLE #dnums(D varchar(6),[values] varchar(6))

INSERT #dnums

SELECT '11.2','group1' UNION ALL

SELECT '34.22','group1' UNION ALL

SELECT '67.89','group1' UNION ALL

SELECT '55','group1' UNION ALL

SELECT '78.9','group1' UNION ALL

SELECT '90','group1' UNION ALL

SELECT '56','group1' UNION ALL

SELECT '45','group1' UNION ALL

SELECT '78','group1' UNION ALL

SELECT '33','group1' UNION ALL

SELECT '55.10','group1' UNION ALL

SELECT '90','group1' UNION ALL

SELECT '78','group1' UNION ALL

SELECT '66','group1' UNION ALL

SELECT '43.45','group1' UNION ALL

SELECT '67','group1'

END

--select * from #cnums

--select * from #dnums

select *

from #cnums cnums

where (

select count(*)

from #dnums dnums

where dnums.D in (cnums.D1,cnums.D2,cnums.D3,cnums.D4,cnums.D5,cnums.D6,cnums.D7)

) = SIGN(ISNULL(D1,0))+SIGN(ISNULL(D2,0))+SIGN(ISNULL(D3,0))+SIGN(ISNULL(D4,0))+SIGN(ISNULL(D5,0))+SIGN(ISNULL(D6,0))+SIGN(ISNULL(D7,0))

THanks!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

you have a comma in 90,78 instead if a decimal point.!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

VGish SSChasing Mays Points: 643 More actions · Answer 10

VGish

SSChasing Mays

Points: 643

October 5, 2012 at 6:01 pm

#1546355

It's a varchar right ?

VGish SSChasing Mays Points: 643 More actions · Answer 11

This is returning nothing 🙁

--drop table tempdb..#cnums

--drop table tempdb..#dnums

IF OBJECT_ID('tempdb..#cnums') IS NULL

BEGIN

CREATE TABLE #cnums(Cnum bigint,D1 varchar(6), D2 varchar(6), D3 varchar(6), D4 varchar(6), D5 varchar(6), D6 varchar(6), D7 varchar(6))

INSERT #cnums

SELECT 121345,'11.2','12.4','45','78.9', NULL, NULL, '33' UNION ALL

SELECT 125678,NULL,'34.22',NULL,'67.89', '78', '55', NULL UNION ALL

SELECT 34567,'55.10','90','56', '66','43.45', '67','45'

END

IF OBJECT_ID('tempdb..#dnums') IS NULL

BEGIN

CREATE TABLE #dnums(D varchar(6),[values] varchar(6))

INSERT #dnums

SELECT '11.2','group1' UNION ALL

SELECT '34.22','group1' UNION ALL

SELECT '67.89','group1' UNION ALL

SELECT '55','group1' UNION ALL

SELECT '78.9','group1' UNION ALL

SELECT '90','group1' UNION ALL

SELECT '56','group1' UNION ALL

SELECT '45','group1' UNION ALL

SELECT '78','group1' UNION ALL

SELECT '33','group1' UNION ALL

SELECT '55.10','group1' UNION ALL

SELECT '90','group1' UNION ALL

SELECT '78','group1' UNION ALL

SELECT '66','group1' UNION ALL

SELECT '43.45','group1' UNION ALL

SELECT '67','group1'

END

--select * from #cnums

--select * from #dnums

select *

from #cnums cnums

where (

select count(*)

from #dnums dnums

where dnums.D in (cnums.D1,cnums.D2,cnums.D3,cnums.D4,cnums.D5,cnums.D6,cnums.D7)

) = SIGN(ISNULL(D1,0))+SIGN(ISNULL(D2,0))+SIGN(ISNULL(D3,0))+SIGN(ISNULL(D4,0))+SIGN(ISNULL(D5,0))+SIGN(ISNULL(D6,0))+SIGN(ISNULL(D7,0))

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

You have duplicate numbers in the "Dnums" table, so you need a distinct, like this:

select *

from #cnums cnums

where (

select count(distinct dnums.D)

from #dnums dnums

where dnums.D in (cnums.D1,cnums.D2,cnums.D3,cnums.D4,cnums.D5,cnums.D6,cnums.D7)

) = SIGN(ISNULL(D1,0))+SIGN(ISNULL(D2,0))+SIGN(ISNULL(D3,0))+SIGN(ISNULL(D4,0))+SIGN(ISNULL(D5,0))+SIGN(ISNULL(D6,0))+SIGN(ISNULL(D7,0))

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);