SQL programming comparing list to list

Question

SQL programming comparing list to list

Viewing 15 posts - 31 through 44 (of 44 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Also, you now need to explain, usig the sample data, how each row in #Match statisfies the match requirements as I don't see it based n the provided data.

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 2

sorry again, offically this works, i tested it twice and will work:

insert Match2

select '1', '1','firstname' union all

select '1', '6', 'firstname' union all

select '4', '2', 'address' union all

select '5', '1', 'firstname';

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Siten0308 (5/21/2012)
sorry again, offically this works, i tested it twice and will work:
insert Match2
select '1', '1','firstname' union all
select '1', '6', 'firstname' union all
select '4', '2', 'address' union all
select '5', '1', 'firstname';

For those of us using all temp tables.

insert #Match

select '1', '1','firstname' union all

select '1', '6', 'firstname' union all

select '4', '2', 'address' union all

select '5', '1', 'firstname';

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lynn Pettis (5/21/2012)
Also, you now need to explain, usig the sample data, how each row in #Match statisfies the match requirements as I don't see it based n the provided data.

Again, please explain as I don't see how the expected results still match the sample data.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Lynn Pettis (5/21/2012)
Siten0308 (5/21/2012)
sorry again, offically this works, i tested it twice and will work:
insert Match2
select '1', '1','firstname' union all
select '1', '6', 'firstname' union all
select '4', '2', 'address' union all
select '5', '1', 'firstname';
For those of us using all temp tables.
insert #Match
select '1', '1','firstname' union all
select '1', '6', 'firstname' union all
select '4', '2', 'address' union all
select '5', '1', 'firstname';

Thanks Lynn.

This doesn't even come close to matching your explanation. You should have a LOT more in your match table based on your description. The one you have "matching" on address has an empty string for an address. There are others with the same address in both table but not showing up.

Lynn and I are taking aim but the target here keeps moving. Slow down, look closely at what you have posted and think about what your output should be. Then you can post back what our target is. I bet either of us can hit the target at 50 paces once we know where it is.

_______________________________________________________________

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/

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 6

ok now for the explanation

using the second row insert of match as an example:

Insert #Match

select '1', '6', 'firstname' union all

first number 1, comes from table Customer, second number 6, comes from Person table, and 'firstname' for this column purpose tells me how or what column specifically matched a specific row on customer table to person table, in this case, they have the same first name.

After this is inserted number 1 from customer table will move on to 7,8,9 etc. etc, to see if customer with first name matches anyone else with the same name on Person table. hope this helps in explaining, though I am only using 6 examples I have about 5k each for both customer and person table, but again the 6 examples will probably help.

thank you

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Siten0308 (5/21/2012)
ok now for the explanation
using the second row insert of match as an example:
Insert #Match
select '1', '6', 'firstname' union all
first number 1, comes from table Customer, second number 6, comes from Person table, and 'firstname' for this column purpose tells me how or what column specifically matched a specific row on customer table to person table, in this case, they have the same first name.
After this is inserted number 1 from customer table will move on to 7,8,9 etc. etc, to see if customer with first name matches anyone else with the same name on Person table. hope this helps in explaining, though I am only using 6 examples I have about 5k each for both customer and person table, but again the 6 examples will probably help.
thank you

Try again. Your examples just simply don't make sense. You are saying that a Customer with a name of 'bob' should match a customer with a first name of 'peter'.

Given your sample there is only 1 match for 'bob' That is Customer and Person ID 1. You have a lot of other matches that you did not identify in your output. What happened to billy??? What about the last names? There are no matches on address (unless '' counts as a match, and if so those are missing).

For those playing along at home and don't feel like scrolling for all the data here is it is once again all put together.

create table #Customers

(

id INT IDENTITY(1,1),

firstname nvarchar (200),

lastname nvarchar(200),

address nvarchar(200)

);

insert #Customers

select 'bob', 'sam', '39919 go figure rd.' union all

select 'billy', 'bob', '' union all

select 'billy', 'smith', '' union all

select 'billy', 'SomeLastName', '' union all

select 'jack', 'skeleton', '';

create table #Person

(

id int IDENTITY(1,1),

firstname nvarchar (200),

lastname nvarchar(200),

address nvarchar(200)

);

insert #Person

select 'bob', 'davis', '39 santa ana rd.' union all

select 'billy', 'bob', '' union all

select 'billy', 'smith', '' union all

select 'billy', 'SomeLastName', '' union all

select 'brandon', 'rex', '' union all

select 'peter', 'skeleton', '' ;

CREATE TABLE #Match

(

id int identity (1,1),

customerid int,

personid int,

type varchar (200)

)

insert #Match

select '1', '1','firstname' union all

select '1', '6', 'firstname' union all

select '4', '2', 'address' union all

select '5', '1', 'firstname';

select * from #Customers

select * from #Person

select * from #Match

drop table #Customers

drop table #Person

drop TABLE #Match

_______________________________________________________________

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/

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 8

Sorry all for any confusion here is a more acurate example,

Yes you are right those dont match however below the right examples of matching from the tables customer and person:

Insert #Match

select 1 , 1, 'firstname' union all

select 2 , 2, 'firstname' union all

select 2, 3, 'firstname' union all

select 2, 4, 'firstname' union all

select 3, 2, 'firstname' union all

select 3, 3, 'first name';

as you can see there are matches to the first name, of course this will continue to happen with all matches whether its first name, last name or address between the 2 tables and insert it into table called match.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

So you want the cartesian that I explained about 40 posts ago? Give me about 2 minutes and I can knock this out.

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Like this?

insert #Match

select c.id as CustomerId, p.id as PersonID, 'FirstName' as MatchType

from #Customers c

join #Person p on c.firstname = p.firstname

union

select c.id as CustomerId, p.id as PersonID, 'LastName' as MatchType

from #Customers c

join #Person p on c.lastname = p.lastname

union

select c.id as CustomerId, p.id as PersonID, 'Address' as MatchType

from #Customers c

join #Person p on c.address = p.address

_______________________________________________________________

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/

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 11

.... HOLY crap your good.... no seriously, thats exactly what i need thank you :):-D

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Siten0308 (5/21/2012)
.... HOLY crap your good.... no seriously, thats exactly what i need thank you :):-D

You are welcome and thanks for letting us know. Hopefully now you see the importance of posting everything!!! Come back again next time you need some help, or if you think you need some sort of looping...chances are you don't. 😉

Loops are fine with your c# background but they are absolutely awful in sql. There are a few times when looping is ok but they are few and far between.

_______________________________________________________________

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/

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 13

thanks again :).... where do i mark as answered to give points or whatever?

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Siten0308 (5/21/2012)
thanks again :).... where do i mark as answered to give points or whatever?

Thanks for the props but we don't roll like that around here. It could be that in the future somebody will find a better solution. All forum posts remain forever. Points around here are easy, 1 point per forum post. so just this response is 1 point. 😎

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 15

Siten0308 (5/21/2012)
thanks again :).... where do i mark as answered to give points or whatever?

I do however accept Guinness. 😛

_______________________________________________________________

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/