October 28, 2013 at 9:23 am
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?
October 28, 2013 at 9:26 am
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/
October 28, 2013 at 9:36 am
Thank you for pointing
oops sorry, just edited my post.
October 28, 2013 at 9:39 am
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/
October 28, 2013 at 9:58 am
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)
October 28, 2013 at 12:28 pm
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.
October 28, 2013 at 12:36 pm
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply