Help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a query

Author

Topic

help_needed
Starting Member

12 Posts

Posted - 2009-01-08 : 09:01:08

Hi

I have a table that contains several million records which is called main_data1. I am trying extract all of the details of everyone that lives in 100 different postcode areas though.

I want to try and find everyone that has a postcode that starts with

MK12 3
TR1 4
SW15 6

I can do this when just searching for 1 postcode area but cannot get it to work when trying multiple ones.

The postcode column in the table is called postcode

Can anyone help?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-08 : 09:09:59

some thing like this

select md.* from main_data1 md
inner join
postcode pc on < join conditions>
where pc.postcode like 'MK12 3%' or pc.postcode like 'TR1 4%' or pc.postcode like 'SW15 6%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:11:46

SELECT *
FROM Table1
WHERE PostCode LIKE 'MK12 3%'
OR PostCode LIKE 'TR1 4%'
OR PostCode LIKE 'SW15 6%'

E 12°55'05.63"
N 56°04'39.26"

help_needed
Starting Member

12 Posts

Posted - 2009-01-08 : 09:27:57

Thanks for your help with this one. Is there any way I can do this query without having to type OR postcode LIKE 100 times and can copy the postcodes in from excel?

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-01-08 : 09:32:26

do you have a list of the beginning of the postcodes in an excel spreadsheet

help_needed
Starting Member

12 Posts

Posted - 2009-01-08 : 09:33:38

Yes I have

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-01-08 : 09:35:41

Say that you have a long list in column a you can use something like this as a quick solution

In the formula bar insert this

="OR columnName Like '"&A1&"'"

and then just copy the formula down from there copy and paste into you script.

:-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 09:38:26

quote:
Originally posted by help_needed

Thanks for your help with this one. Is there any way I can do this query without having to type OR postcode LIKE 100 times and can copy the postcodes in from excel?

Get the values from excel onto table using export/import task or OPENROWSET and use like below


SELECT *
FROM Table1 t
JOIN ExcelTable e
ON t.PostCode LIKE e.Value + '%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:38:35

Or you save all your wanted postcodes in a separate table and join to that table.

SELECT		md.*
from		mypostcodes AS pc -- table with 100 or so postcoces to pattern search with
inner join	main_data1 AS md on md.postcode like pc.postcode + '%'

E 12°55'05.63"
N 56°04'39.26"

help_needed
Starting Member

12 Posts

Posted - 2009-01-08 : 09:39:01

Brilliant, thanks a lot for everyones help on this one

help_needed
Starting Member

12 Posts

Posted - 2009-01-08 : 15:01:25

quote:
Originally posted by Peso

Or you save all your wanted postcodes in a separate table and join to that table.
SELECT md.*
from mypostcodes AS pc -- table with 100 or so postcoces to pattern search with
inner join main_data1 AS md on md.postcode like pc.postcode + '%'

E 12°55'05.63"
N 56°04'39.26"

This worked perfectly for what I needed earlier but I have just been asked to include the customers phone number on the report as well. This is stored in a different table called contactnumbers and the column it is stored in is phonenumber.

The primary key in main_data1 is called pkcustomerrecord which is linked to contactnumbers by a column call main_data1fk

Can anyone help with this as it's way over my head

Thanks for reading

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 01:48:07

[code]SELECT *,c.phonenumberfield
FROM main_data1 t
JOIN ExcelTable e
ON t.PostCode LIKE e.Value + '%'
JOIN contactnumbers c
ON c.main_data1fk=t.pkcustomerrecord
[/code]

help_needed
Starting Member

12 Posts

Posted - 2009-01-09 : 10:14:19

Thanks a lot that worked perfectly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 11:30:20

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources