April 28, 2008 at 3:10 am
Good morning all,
I am having a little difficulty with some SQL script with an ASP page.
Here is what I'd like to acheive:
Query a SQL 2005 DB, Publication Table, to pull out the publications postcode (zipcode). I then want to put only the first part of the postcode into a variable to use in a second query to find all matches in the Address Table.
So:
ThisPublication = Request.QueryString("Pub_PublicationId");
Query = "SELECT Pub_PostCode FROM Publication WHERE Pub_PublicationId="+ThisPublication;
Query.SelectSql();
//This gets me the current publications postcode and works fine
I then need to split the postcode into sections (e.g CT6 8OK = CT6 / / 8OK) or just return the CT6 portion for use later.
Query2 = "SELECT * FROM Address WHERE Addr_PostCode="+postCodeVariable;
//This should return all those addresses that match the first part of the postcode.
I have managed to split the postcode using javascript but javascript runs after execution of ASP and SQL, so when I put the variable in the SELECT statement it returns 0 (as the javascript hasn't run yet!)
What I need is a way of pulling the full postcode and splitting it, or pulling only the first part (the part before the space) to use in my second query.
I hope I've explained it well and that you understand.
April 28, 2008 at 4:24 am
You could do this in various ways. Assuming that the variable postCodeVariable just contains the first part of the post code you could do this:
select * from Address where Addr_PostCode like '" +postCodeVariable+ "%'" --note the use of single quotes
OR:
select * from Address where substring(Addr_PostCode,1,charindex(' ', Addr_PostCode) -1) = '" +postCodeVariable+ "'" --note the use of single quotes
The second method is better because the first method of doing a wildcard search could return rows that start with "CT6" (e.g. "CT67") that you might not want.
April 28, 2008 at 4:30 am
Your first query to get just frist part of posatal code(Before space) is
Query = "SELECT substring(Pub_PostCode,1,charindex(' ',Pub_PostCode)-1) pub_PostCode FROM Publication WHERE Pub_PublicationId="+ThisPublication;
Query.SelectSql();
In second query just add single quote in both sides of variable you pass for filtering a string.
Query2 = "SELECT * FROM Address WHERE Addr_PostCode='"+postCodeVariable + "'";
I hope above will solve your issue, give a try.
April 28, 2008 at 6:46 am
It is pulling the correct data from the first SELECT statement now, Thankyou both, but I have another question.
How do I use the Results of the First Query in the Second Select statement:
postCodeQuery = "SELECT............";
postCode.SelectSql();
addressQuery = "SELECT * FROM Address WHERE Addr_PostCode='"+postCodeQuery+'%"";
I've tried the above but it returns 0 value, when I know there is 1 that 100% matches for testing purposes.
Thanks again
April 28, 2008 at 7:00 am
You could combine the two, something like this:
SELECT
FROM Address a JOIN Publication p
ON LEFT(a.Addr_PostCode, CHARINDEX(' ',a.Addr_Postcode)-1)
= LEFT(p.Pub_PostCode, CHARINDEX(' ',p.Pub_Postcode)-1)
WHERE p.Pub_PublicationID =
John
April 28, 2008 at 7:28 am
Awesome, thank you all.
The Query returned the correct results and the Join enabled me to put it all together
Thank you once again.
April 28, 2008 at 10:13 am
Once again thanks for the help.
I have another issue,
I can get back the first line of address and postcode just fine as they are both held in the Address Table. However, the Company Name, phone etc is held in a Table called Company, which I have not referenced.
Here are my linked Tables:
Address (Addr_AddressId, Addr_Address1, Addr_Postcode)
AddressLink (Adli_AddressId, Adli_CompanyId)
Company (Comp_CompanyId, Comp_PhoneNumber)
Publication (Pub_c_PubId)
The Comp_CompanyId is the same as the Adli_CompanyId and is used to 'link' the address to it.
The Adli_AddressId is the same as the Addr_AddressId, thus completing the 'link'.
Now, With the help I've so far received, I've got the address and postcodes from the Address Table that match the first part of the postcode to that of the publications client.
I want to be able to see the company information also, but it's not a simple case of just adding the column headings to my query. I've got to somehow use the Addr_AddressId to Query the AddressLink Table to get the Addr_CompanyId, then once I have this I have to Query the Company Table to bring back the rest of the data I need using the Addr_CompanyId to match Comp_CompanyId.
I've read about nested queries, joins and unions but as I'm pretty new to difficult sql programming, I was wondering if anyone had any ideas on how I could achieve this.
April 29, 2008 at 1:43 am
You need two JOINS in your query - one to join Company to AddressLink, and one to join AddressLink to Address. In your select list, you only need to include the appropriate columns from Company and Address, so the AddressLink is transparent in the final result set. Have a go at that and let us know how it goes.
John
April 29, 2008 at 2:23 am
I see where you are coming from, Here is my code so far to get the first part of the postcode and return the addresses of those that match (Thanks for the help with this previously):
ThisPublication = Request.QueryString("Pub_c_PubId");
closeList.SelectSql =
"SELECT * FROM Address a
JOIN Pub p ON
LEFT (a.Addr_PostCode, CHARINDEX(' ', a.Addr_PostCode)-1) =
LEFT (p.Pub_c_PostCodeB1, CHARINDEX (' ', p.Pub_c_PostCodeB1)-1)
WHERE p.Pub_c_PubId="+ThisPublication;
Now you say I need two Joins which I think go like this:
JOIN AddressLink al ON al.Adli_AddrssId=a.Addr_AddressId
JOIN Company c ON c.Comp_CompanyId=al.Adli_CompanyId
I have tried placing this code before the Pub JOIN, before the WHERE Clause and after the whole thing before the Semi-Colon, but they all generate a SQL error.
I know this is probably something that is so easy to do and I'm being thick!
The concept makes absolute sense, Once I have my result from the first part, join the other tables to 'collect' all of the data together, I just can't get it to do it! lol
All of the SQL 'tutorials' I read have the basic stuff of how to join tables with no special elements like the postcode section we have.
Cheers for any assistance.
April 29, 2008 at 2:28 am
Please post the code you're running and the error message you get. By the way, if this is, or is going to be, production code, then it's not good practice to use SELECT * FROM. This is because you are probably returning more columns than you actually need, and also if the number or order of the columns in any of the tables changes, your application may break.
John
April 29, 2008 at 2:36 am
I appreciate that using * is overkill and uses up resources, but for testing purposes I just added it, the actual code will specify 7 Columns, 5 from the Company Table and 2 from the Address Table.
Here is my ASP Page (I'm developing on Sage CRM and it uses the eWare object, but that shouldn't effect the results of a SQL statement):
<%
ThisPublication = Request.QueryString("Pub_c_PubId");
var infoBlock = eWare.GetBlock('content');
var infoContentHTML = "";
infoContentHTML+= " ";
infoContentHTML+= "This screen shows you the companies in the same vacinity as the client
";
infoContentHTML+= " ";
infoBlock.contents = infoContentHTML;
eWare.AddContent(infoBlock.Execute());
closeList = eWare.GetBlock("List");
strPostCodeQuery = eWare.CreateQueryObj("SELECT substring(Pub_c_PostCodeB1, 1, charIndex(' ',Pub_c_PostCodeB1)-1) Pub_c_PostCodeB1 FROM Pub WHERE Pub_c_PubId="+ThisPublication, "");
strPostCodeQuery.SelectSql();
strPostCode = strPostCodeQuery("Pub_c_PostCodeB1");
strMessageToUser = 'POSTCODE VALIDATION - Listings Matching: ' + strPostCode;
//Query to return all companies that have the same inital postcode as the publication
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//AREA IM WORKING ON
closeList.SelectSql = "SELECT Addr_Address1, Addr_PostCode, Comp_Name, Comp_PhoneAreaCode, Comp_PhoneNumber, Comp_EmailAddress FROM Address a JOIN Pub p ON LEFT (a.Addr_PostCode, CHARINDEX(' ',a.Addr_PostCode)-1) = LEFT (p.Pub_c_PostCodeB1, CHARINDEX(' ', p.Pub_c_PostCodeB1)-1) WHERE p.Pub_c_PubId="+ThisPublication;
INNER JOIN AddressLink al ON al.Adli_AddressId=a.Addr_AddressId INNER JOIN Company c ON c.Comp_CompanyId=al.Adli_CompanyId
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/*
closeList.SelectSql = "SELECT Addr_Address1, Addr_PostCode, Comp_Name, Comp_PhoneAreaCode, Comp_PhoneNumber, Comp_EmailAddress FROM Address a JOIN Pub p ON LEFT (a.Addr_PostCode, CHARINDEX(' ',a.Addr_PostCode)-1) = LEFT (p.Pub_c_PostCodeB1, CHARINDEX(' ', p.Pub_c_PostCodeB1)-1) WHERE p.Pub_c_PubId="+ThisPublication;
Col1 = closeList.AddGridCol("Addr_Address1", 0, false);
Col2 = closeList.AddGridCol("Addr_PostCode", 1, false);
*/
Col1 = closeList.AddGridCol("Comp_Name", 0, false);
Col2 = closeList.AddGridCol("Addr_Address1", 1, false);
Col3 = closeList.AddGridCol("Addr_PostCode", 2, false);
Col4 = closeList.AddGridCol("Comp_PhoneAreaCode", 3, false);
Col5 = closeList.AddGridCol("Comp_PhoneNumber", 4, false);
Col6 = closeList.AddGridCol("Comp_EmailAddress", 5, false);
eWare.AddContent(closeList.Execute());
eWare.AddContent(strMessageToUser);
eWare.GetTabs("Pub");
eWare.GetCustomEntityTopFrame("Pub");
Response.Write(eWare.GetPage());
%>
April 29, 2008 at 2:48 am
I'm not an ASP expert so it's difficult for me to tell what SQL this is actually sending to the SQL Server. And you didn't post the error message. But it looks as if what you're doing wrong is putting some of the JOIN clauses after the WHERE clause. Look up SELECT in Books Online if you're in any doubt about how to structure your query. I'll sum it up in pseudo-code:
SELECT columnlist
FROM Table1 t1
JOIN Table2 t2 ON whatever
JOIN Table3 t3 ON whatever
WHERE whatever
When you post code, please make use of carriage returns so that we don't have to keep scrolling across the page.
John
April 29, 2008 at 2:55 am
Sorry about the code,
I had various error messages, ones saying I needed a semi-colon (when I placed the code after the WHERE clause) others just saying SQL Error on-screen (I didn't check the logs).
On my page of code I have not placed the JOINS where you see them, When I run the code I comment out that line. What I need to know is where to put it in the statement just above it.
The reason I wrote it there was to just keep it all together, if that makes any sense?
April 29, 2008 at 3:09 am
My sincere apologies, through wanting this done quickly I overlooked the fact that the AddressLink table is actually named Address_Link, when I changed it it worked fine as per your original instructions!
That you for your persistence with me!
One look at the SQL log revealed that it had an invalid object 'AddressLink'!
I'll use the logs more shall I!
Thank you so so much
April 29, 2008 at 12:26 pm
You do realize that by using "ThisPublication" directly from the Request and then concatenating it to the SQL, you're setting yourself up for SQL Injection?
Steve G.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply