May 21, 2012 at 11:45 am
Hello,
This is probably simple but not sure why its not working, well its working but duplicating the results, what i am trying to do is take first name from table 1 called customers and compare it to all the first names in table 2 called persons, if there is a match, then insert match finding into a table called match, insert customer first name profile id from table 1 and first name from person table id into match table, this will later be going into a report but for now want to add it to a table. the problem is, i have 5k first names on table 1, and the query below shows Bob on match 5k times... why, i even wrote a query to remove duplicates but never seems to remove it... any one please help, pulling my hair and running out of hair 🙁
declare
@firstname nvarchar(150), @lastname nvarchar (200), @company nvarchar(200), @address nvarchar(250),
@city nvarchar (250), @state nvarchar (250), @country nvarchar (250), @zip nvarchar (50),
@website nvarchar (250), @emailaddress nvarchar (250), @customerid int, @counter int, @max-2 int,
@max2person int , @counter2 int
select @max-2 = COUNT(*) from Customers
set @counter = 1
while (@counter < @max-2)
BEGIN
select @firstname = firstname from Customers where customerID = @counter
select @maxperson = COUNT(*) from person
set @counter2 = 1
while(@counter2 < @maxperson)
BEGIN
if (exists(select FirstName from Person where ProfileID = @counter2 AND FirstName = @firstname))
BEGIN
IF(select COUNT(*) from match where customerid = @counter AND ofacid = @counter2 AND columnid = 1) >2
BEGIN
UPDATE Match
set customerid = @counter, personid = @counter2, columnid = 1
where customerid = @counter AND personid = @counter2 and columnid = 1
END
ELSE
BEGIN
insert into Match(customerid, personid, columnid)
select @counter, @counter2, 1
from Person a
set @counter2 = @counter2 + 1
END
END
ELSE
BEGIN
set @counter2 = @counter2 + 1
END
END
set @counter = @counter + 1
END
May 21, 2012 at 12:11 pm
Hi and welcome to SSC!!! I will be happy to help. The first and most obvious thing is that you do not need a loop for this. I can't give you much to go one because you didn't give us much to go on. What I am saying is that you need to post ddl, sample data, and desired output based on that sample data. Take a look at the first link in my signature for best practices when posting questions.
From your description it seems like you could use grouping and the HAVING clause to get your results but as I said above it is hard to tell exactly.
_______________________________________________________________
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/
May 21, 2012 at 12:16 pm
Is that what you are looking for:
select
a.customerid,
a.firstname,
count(distinct b.profileid)
from customers a
inner join persons b on
a.firstname = b.firstname
group by a.customerid,
a.firstname
May 21, 2012 at 12:30 pm
Thank you for the reply, sorry for the missing info, for the tables its below, let me know if you need anything else, for the last reply by SSC Rookie, will that query insert a new record into a table called match if there is a match etc.?
table details below:
table 1
called customers
columns, firstname nvarchar (200), lastname nvarchar(200), address nvarchar(200) etc. etc.
bob sam 39919 go figure rd.
billy bob
jack skeleton
(FYI just making up names)
Table 2
Person
columns, firstname nvarchar (200), lastname nvarchar(200), address nvarchar(200) etc. etc.
bob davis 39 santa ana rd.
brandon rex
peter skeleton
these tables are on the same database
May 21, 2012 at 12:37 pm
No, but it will give you a count of the matches. What are the columns in the match table? When they ask for ddl's they really want you to use the IFCode function(found at the top of the editor) and to put runable code in it. Hope this helps!
May 21, 2012 at 12:42 pm
oops sorry yes for the match table its below on how it is, very simple
Match table
Customerid Profile ID Type
1 22 first name
59 17 first name
102 79 last name
104 21 address
etc. etc. which is what i am hoping again, its comparing 2 tables, if there is a match, then insert into match the id's of both customer table and person table and what specifically did they match, was it there first name, last name etc. etc., which again is making it complicated for me. I come from a back ground of C# (though i am still a novice) however sql i know is powerful enough to do this, how exactly is my problem.
thank you all in advance for your help.
May 21, 2012 at 12:43 pm
The point of ddl and sample data is to make it easy for the people helping you. You didn't post ddl or sample data in a consumable format. Think create table and insert statements.
To demonstrate see below. I also added some more matches to make it a little more obvious what is happening.
create table #Customers
(
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
(
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
(
firstname nvarchar(200),
NumOfMatches int
)
insert #Match
select c.firstname, COUNT(*)
from #Customers c
where c.firstname in
(
select p.firstname from #Person p
)
group by c.firstname
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/
May 21, 2012 at 12:45 pm
Siten0308 (5/21/2012)
oops sorry yes for the match table its below on how it is, very simpleMatch table
Customerid Profile ID Type
1 22 first name
59 17 first name
102 79 last name
104 21 address
etc. etc. which is what i am hoping again, its comparing 2 tables, if there is a match, then insert into match the id's of both customer table and person table and what specifically did they match, was it there first name, last name etc. etc., which again is making it complicated for me. I come from a back ground of C# (though i am still a novice) however sql i know is powerful enough to do this, how exactly is my problem.
thank you all in advance for your help.
It seems that my shot in the dark missed the mark. This is another reason we like to see ddl. You really should read the article I suggested. It explains what to post and why it is important to the answer you receive. 😀
_______________________________________________________________
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/
May 21, 2012 at 12:48 pm
SQL and C# are a complete paradigm shift. You have to think in terms of sets. For your issue there isn't a great way to do it (that I can think of.)
The first thought would be to do the following:
insert into match
select customerid, profileid, 'first name'
from customer
inner join person on
customer.firstname = person.firstname;
go
insert into match
select customerid, profileid, 'last name'
from customer
inner join person on
customer.lastname = person.lastname;
etc...
May 21, 2012 at 12:51 pm
dkschill (5/21/2012)
SQL and C# are a complete paradigm shift. You have to think in terms of sets. For your issue there isn't a great way to do it (that I can think of.)The first thought would be to do the following:
insert into match
select customerid, profileid, 'first name'
from customer
inner join person on
customer.firstname = person.firstname;
go
insert into match
select customerid, profileid, 'last name'
from customer
inner join person on
customer.lastname = person.lastname;
etc...
The problem with the join approach is you are actually getting a cartesian product because the join condition is super sloppy (nothing wrong in your code, just the logic behind this makes it rather loose). In the sample data I posted above you would end up with 9 rows in the output table for Billy.
_______________________________________________________________
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/
May 21, 2012 at 12:58 pm
I was wondering the same thing, but since he is including the ids for both customer and person that seems appropriate. What do you think?
May 21, 2012 at 1:01 pm
Hard to say without ddl but if the ID is included in the join condition that should work and not get cartesians.
_______________________________________________________________
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/
May 21, 2012 at 1:05 pm
I was looking at this and got a bit confused. It appears that the word description of the problem doesn't quite match to the OPs code in the initial post. I may be missing something there.
It would really help if the OP would post his (or her) on DDL for the tables, sample data and expected results.
May 21, 2012 at 1:08 pm
Sorry everyone for that, now i understand and will make sure I did that in the future. Ya I pointed out about C# because i know it will help with many things that are similar like the loops etc.,
but if anyone can help that would be great. I am still testing myself, but again I get duplicates of the same match and i just want one match not 5000 times telling me bob is matching.
May 21, 2012 at 1:10 pm
Siten0308 (5/21/2012)
Sorry everyone for that, now i understand and will make sure I did that in the future. Ya I pointed out about C# because i know it will help with many things that are similar like the loops etc.,but if anyone can help that would be great. I am still testing myself, but again I get duplicates of the same match and i just want one match not 5000 times telling me bob is matching.
Again post something we can use. We can't help without knowing what you are doing. As I said originally, you do NOT need a loop for this. And unless you want your code to be super slow you shouldn't use it. Give me something to work with and I will give you tested and fast code.
_______________________________________________________________
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 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply