Select and compare doesn't bring correct results.

Question

Post reply

Select and compare doesn't bring correct results.

itsjustme

SSC Veteran

Points: 244
More actions
October 28, 2013 at 9:23 am

#300473

Hi,

I am trying to select rows on Table A to get correct records that doesn't have any records on Table B.

Table A has a column called IP1 data in these columns are like (10.1.1.20, 10.2.00.21, ...etc)

Table B has column called IP1A (10.1.1.20, 10.22.392.2, ...etc.)

records in both IP1 and IP1A are same.

I need to select rows that are in IP1A but not in IP1

I wrote query like

Select * from TableB

where IP1A not in (Select IP1 from TableA)

but for somereason The query is brining all records that are common.

Anything wrong that I am doing?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

itsjustme (10/28/2013)

Hi,

I am trying to select rows on Table A to get correct records that doesn't have any records on Table B.

Table A has a column called IP1 (10.1.1.20)

Table B has column called IP1A (10.1.1.20)

records in both IP1 and IP1A are same.

I need to select rows that are in IP1A but not in IP1

I wrote query like

Select * from TableB

where IP1A not in (Select IP1 from TableB)

but for somereason The query is brining all records that are common.

Anything wrong that I am doing?

Ummm....you said you need to get rows from TableA but that table is not in your query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

itsjustme SSC Veteran Points: 244 More actions · Answer 2

itsjustme

SSC Veteran

Points: 244

October 28, 2013 at 9:36 am

#1662047

Thank you for pointing

oops sorry, just edited my post.

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

Your query as posted now should do what you are saying it needs to do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

Maybe you're facing some problems with NULL values.

For more information: http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Example:

WITH TableA(IP1) AS ( SELECT '10.1.1.20' UNION ALL SELECT '10.2.00.21' UNION ALL SELECT NULL),



TableB(IP1A) AS (SELECT '10.1.1.20' UNION ALL SELECT '10.22.392.2' UNION ALL SELECT NULL)

Select * from TableB

where NOT EXISTS(Select 1 from TableA WHERE IP1 = IP1A)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

itsjustme SSC Veteran Points: 244 More actions · Answer 5

Thank you all for the reply,

My problem is that

1) tableA.IP1 has Nulls

2) TableA has more data that's hard for me to compare based on just one column value...

3) I need to get data that's in TableB but not in TableA like

Get me all rows from TableB where TableB.IP1A doesn't exists in TableA.IP1

I should be able to get all rows who's IP1A columns are null too...

Can I search something like

Select * from TableB, TableA

where TableB.IP1A not in ( Select IPA from TableA)

and TableB.IP1A is null

and TableB.IP1A not like '10%'

Any guidance is much appreciated.

Sean Lange SSC Guru Points: 286562 More actions · Answer 6

itsjustme (10/28/2013)

Thank you all for the reply,

My problem is that

1) tableA.IP1 has Nulls

2) TableA has more data that's hard for me to compare based on just one column value...

3) I need to get data that's in TableB but not in TableA like

Get me all rows from TableB where TableB.IP1A doesn't exists in TableA.IP1

I should be able to get all rows who's IP1A columns are null too...

Can I search something like

Select * from TableB, TableA

where TableB.IP1A not in ( Select IPA from TableA)

and TableB.IP1A is null

and TableB.IP1A not like '10%'

Any guidance is much appreciated.

Not a lot of detail here to work with but using Luis's excellent sample data you could do something like this.



WITH TableA(IP1) AS ( SELECT '10.1.1.20' UNION ALL SELECT '10.2.00.21' UNION ALL SELECT NULL),

TableB(IP1A) AS (SELECT '10.1.1.20' UNION ALL SELECT '10.22.392.2' UNION ALL SELECT NULL)

--Select * from TableB

--where NOT EXISTS(Select 1 from TableA WHERE IP1 = IP1A)

select *

from TableB b

left join TableA a on b.IP1A = a.IP1

where a.IP1 is null

--and b.IP1A is not null

If you wanted to exclude rows where IP1A is not null just uncomment that line.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/