• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

MS SQL Output producing duplicate data

I'm having difficulty with an MS SQL 2005 query in a classic ASP VBScript environment.  I'm trying to create an XML data feed for a real estate company.  There are 3 tables that are relevant.  A properties table, a staffbios table (the agents) and a table called "Staff_Property_XRef" that is used to reference property ids and agent ids and bring them together to display all on one page.

Below is my code.  I'm not used to vbscript, so I'm sure I'm doing this the long and hard way.  I'm only outputting a fraction of the data that will go into the final feed, because I'm still in testing mode for now.

The problem I'm having is when there are two or more agents assigned to a property, the query returns duplicate property output, but with the different agent.  I want just one block of XML per property, with one or both agents in the agent's tag.  
SQL="SELECT * FROM Properties, StaffBios, Staff_Property_XRef WHERE Staff_Property_XRef.PropertyID=Properties.PropertyID AND Staff_Property_XRef.StaffBioID=StaffBios.StaffBioID AND  Properties.IsVisible='True'"

set rstemp=dbConn.execute(SQL)

'detects if data is empty
If  rstemp.eof then
   response.write "No records matched"
   connection.close
   set connection=nothing
   response.end

end if

' puts fieldnames into column headings
for each whatever in rstemp.fields
next

' grabs all the records

DO  UNTIL rstemp.eof

' put fields into variables
pid=rstemp("PropertyID")
fname=rstemp("FirstName")
lname=rstemp("LastName")
price=rstemp("price")
headline=rstemp("Headline")
city=rstemp("City")

tfile.WriteLine("<listing>")
tfile.WriteLine("<id>" & cellstart & pid & "</id>")
tfile.WriteLine("<agent>" & cellstart & fname & lname & "</agent>")
tfile.WriteLine("<price>" & cellstart & price & "</price>")
tfile.WriteLine("<address>" & cellstart & headline & "</address>")
tfile.WriteLine("<city>" & cellstart & city & "</city>")
tfile.WriteLine("</listing>")

rstemp.movenext
LOOP

tfile.WriteLine("</nytfeed>")
tfile.close
set tfile=nothing
set fs=nothing

response.write "File Created!"

' Now close and dispose of resourcesrstemp.close

set rstemp=nothing
dbConn.close
set dbConn=nothing
%>

Here's an example of the problem xml output:

<listing>
<id>180</id>
<agent>Jane Doe</agent>
<price>350000</price>
<address>1234 Main St.</address>
<city>Small Town</city>
</listing>

<listing>
<id>180</id>
<agent>John Smith</agent>
<price>350000</price>
<address>1234 Main St.</address>
<city>Small Town</city>
</listing>

Open in new window

0
mevin
Asked:
mevin
  • 7
  • 6
  • 4
  • +2
2 Solutions
 
CCSOFlagCommented:
It's going to depend on the records in the database itself.  Is the property table a distinct list of properties?  or does it have multiple entries for the properties if it has multiple agents assigned?  If it is distinct, do a join on the properties table so it only shows each property once.

0
 
autosblindoCommented:
You have to put a second Loop inside the first, something like this:

Dim StrAgent
StrAgent = rstemp("TheIdOfYourAget"), or fname & lname if unique

DO  UNTIL rstemp.eof

' put fields into variables
pid=rstemp("PropertyID")
fname=rstemp("FirstName")
lname=rstemp("LastName")
price=rstemp("price")
headline=rstemp("Headline")
city=rstemp("City")

tfile.WriteLine("<listing>")
tfile.WriteLine("<id>" & cellstart & pid & "</id>")

Do UNTIL StrAgent = rstemp("TheIdOfYourAget"), or fname & lname if unique
  tfile.WriteLine("<agent>" & cellstart & fname & lname & "</agent>")
  StrAgent = rstemp("TheIdOfYourAget"), or fname & lname if unique
  rstemp.movenext
  If rstemp.eof Then exit loop
LOOP

tfile.WriteLine("<price>" & cellstart & price & "</price>")
tfile.WriteLine("<address>" & cellstart & headline & "</address>")
tfile.WriteLine("<city>" & cellstart & city & "</city>")
tfile.WriteLine("</listing>")
LOOP

or

Dim StrAgent
StrAgent = rstemp("TheIdOfYourAget"), or fname & lname if unique

DO  UNTIL rstemp.eof

' put fields into variables
pid=rstemp("PropertyID")
fname=rstemp("FirstName")
lname=rstemp("LastName")
price=rstemp("price")
headline=rstemp("Headline")
city=rstemp("City")

tfile.WriteLine("<listing>")
tfile.WriteLine("<id>" & cellstart & pid & "</id>")
tfile.Write("<agent>" & cellstart)
Do UNTIL StrAgent = rstemp("TheIdOfYourAget"), or fname & lname if unique
  tfile.Write(fname & lname)
  tfile.Write("; ") 'Or the separated value you want
  StrAgent = rstemp("TheIdOfYourAget"), or fname & lname if unique
  rstemp.movenext
  If rstemp.eof Then exit loop
LOOP

tfile.WriteLine("</agent>")
tfile.WriteLine("<price>" & cellstart & price & "</price>")
tfile.WriteLine("<address>" & cellstart & headline & "</address>")
tfile.WriteLine("<city>" & cellstart & city & "</city>")
tfile.WriteLine("</listing>")
LOOP


or you could use a var inside loop to create
<agent1>Name</agent>
<agent2>Name</agent>
<agentN>Name</agent>

I hope this will help.
Bye.
0
 
autosblindoCommented:
Importat: your query must be order by PropertyID and Agent Unique identification.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
8080_DiverCommented:
If you look closely, the Jane Doe and John Smith entries are not, in fact, duplicate entries.  What I suspect is happening is that Property Listing 180 is associated in the
Staff_Property_XRef table with each of those agents.  
This may be because one of them is the Buyer's agent and the other is the Seller's agent or it may be that it is a shared listing (if that is permitted) or it may simply be one is an older contract than the other one.  In any case, if you look in the Staff_Property_XRef table for Staff_Property_XRef.PropertyID = 180, I am willing to bet that you will find 2 entries.  
The real question then becomes, how do you determine which is the "correct" entry. ;-)
0
 
lofCommented:
why don't you try sql FOR XML feature?

Have a look at sample code where I created sample tables similar to your example. The query produces this output:

<listings>
  <listing>
    <ID>1</ID>
    <Agents>
      <Agent>Jane Doe</Agent>
      <Agent>John Smith</Agent>
    </Agents>
    <Price>350000.00</Price>
    <Address>1234 Main St.</Address>
    <City>Small Town</City>
  </listing>
</listings>

where all listings may but don't need to be enclosed in a root <listings> tag.
At the moment I've added <Agents> as top level to <Agent> but again it is optional. this solution is simple and does not need any VBS code. All things like empty values <Agent /> will be handled automatically.
create table Properties (
	 PropertyId			int identity
	,PropertyPrice		decimal(15,2)
	,PropertyAddress	varchar(16)
	,PropertyCity		varchar(16)
);

create table StaffBios (
	 StaffId			int identity
	,StaffName			varchar(16)
);

create table Staff_Property_XRef (
	 PropertyId		int
	,StaffBiosId		int
);

insert into Properties values (350000, '1234 Main St.', 'Small Town')
insert into StaffBios values ('Jane Doe')
insert into StaffBios values ('John Smith')

insert into Staff_property_XRef values (1,1)
insert into Staff_property_XRef values (1,2)


select 
	 PropertyId as 'ID'
	,(select StaffName as 'Agent'
		from StaffBios 
		where StaffId in (select StaffBiosId from Staff_property_XRef XRef where XRef.PropertyId = P.PropertyId)
		for xml path (''), type
	 ) as Agents
	,PropertyPrice as 'Price'
	,PropertyAddress as 'Address'
	,PropertyCity as 'City'
from Properties P
for xml path ('listing'), root ('listings')

Open in new window

0
 
mevinAuthor Commented:
Well it's not really a dup perse.  It appears to be a shared listing.  At least that's how it appears on their web site.  The propertyid appears only once in the properties table but twice in the Staff_Property_XRef, because of the two agents.  
0
 
CCSOFlagCommented:
Do you need to know all agents that are associated with that listing?
0
 
mevinAuthor Commented:
Yes I need to list all the agents.
0
 
8080_DiverCommented:
I rest my case. ;-)  I was under the impression that you were approaching the problem from a perspective of dealing with actual duplicate records.
In order to handle the shared listings issue, look at lof's reply.  Not only is it a clean solution in the sense that it gives you a single listing with both agents in it but it is also, as he points out, what I would consider a better issue because it avoids coding in VB (which means that the SQL version won't be impacted if there is a change of "language du jour." ;-)
 
0
 
CCSOFlagCommented:
Then I would do a loop.

Grab your record set of listings.  Then for each listing find the agents that are listed for that property and follow the design that lof suggested for the repeat agents:
<listings>
  <listing>
    <ID>1</ID>
    <Agents>
      <Agent>Jane Doe</Agent>
      <Agent>John Smith</Agent>
    </Agents>
    <Price>350000.00</Price>
    <Address>1234 Main St.</Address>
    <City>Small Town</City>
  </listing>
</listings>

There is not a way to have a distinct list of properties with multiple agents in SQL, unless you create a string field that has a delimited list of the agents.
0
 
lofCommented:
If you will take each listing (cursor access to the database) than for each property you will check agents (a database call per property) you will end up with lot's of calls and poor performance

If you follow my suggestion you will have single call and ready made XML with one simple query
0
 
mevinAuthor Commented:
8080_Diver,

I'm a little confused by the solution provided by lof, especially when you say It avoids having to code in VB.  Do I still use an ASP page?  And is the output file called "listings"?

Sorry I'm really new to ASP and MS SQL.  I'm used to PHP and MYSQL and even then I'm still kind of a beginner.
0
 
lofCommented:
Mevin,

In my solution you have one query only. In ASP you don't need to construct the XML as all you need to do is open a connection, execute the query as a scalar and that will return you a ready made XML object. You may than without further processing save to a file of your choice.

hope that explains a little bit
0
 
CCSOFlagCommented:
lof,

that's why I suggested both ways.  It will depend on if he plans to manipulate the data any more in VB.  If he doesn't plan to manipulate anything then yes creating the string list of agents in the select is the way to go.  
0
 
lofCommented:
Mavin,

One more thing, if as you say you are new to MS SQL I assume you have at least SQL Management Studio (if you don't get yourself an express edition from www.microsoft.com/express). Than try running the script I provided see what happens. Than I'd advise you reed something about FOR XML like http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx

0
 
mevinAuthor Commented:
It makes sense in theory.  I'm just a little lost when it comes to putting it all together.   I had a hell of a time getting this particular server to output files at all, so I'm just not really sure how to output xml any other way than how I'm currently doing it.

Also your code was creating tables and inserting data.  I don't need to do that at all, so I assume I can just bypass that code?  

Thanks and sorry for being such a PITA :)
0
 
lofCommented:
CCSOFlag,

FOR XML is a powerful feature and it is very unlikely that you will need to add something extra to it on VBS level. One another advantage of using FOR XML is that the whole logic of creating the xml may be on the database server so if you create a stored procedure it can be used on the website in ASP, it may be used in an winform application, exposed as webservice - yes, directly from SQL Server, a scheduled job could be created to do it periodically in an automated fashion, or windows script could be created to create the file using sqlcmd for example.
0
 
lofCommented:
My code is creating sample tables and sample data and then it works on this tables and data to produce sample results. If you want it to work with your tables and your data you will probably need to modify it slightly as I created column names out of top of my head.

Safest way to see how it works is to create a temporary test datbase

create database test
go

--switch to it
use test
go

run the whole script, play with it and when you are than just

use master
go
drop database test
go
0
 
mevinAuthor Commented:
OK,  you guys have all given me some great ideas, especially lof (thanks for the links)  I'm gonna rethink this and go back to the drawing board.   I'll assign points accordingly.

I have a boss who thinks I can solve any problem, even when I tell him I'm not qualified with Microsoft stuff.  Drives me crazy, but I guess it's better than being unemployed :)
0
 
lofCommented:
happy to help
0
 
8080_DiverCommented:
Mevin,
I have a boss who thinks I can solve any problem
Better than having a boss who thinks you can't solve anything!  ;-)
 
0
 
mevinAuthor Commented:
They were not so much solutions as ideas to point me in the right direction for solving a larger problem.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 7
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now