SELECT LIKE Statment, but i want field value not name..

[scotchegg78]

Hi Guys

 

I have a table company with a postcode text field. eg "B37 6TG" etc

I have a postcode table containing all UK codes, B, BA, BB ... etc etc in Postcode field and their corresponding ZoneID..

So i also have a table zones with zoneID and the Zone, central , north etc etc.

 

So if i want to get the zone the companies text post code falls into i need something like..

SELECT postcode.ZoneID from postcode WHERE company.Postcode LIKE postcode.Postcode% ? - but i want it to match the value in this field, not its name?!

 

or

SELECT Zone.ZoneName from Zone WHERE company.Postcode LIKE postcode.postcode% AND Zone.ZoneID = postcode.ZoneID

?

 

I know i need to partially match the postcodes B, BA etc to company B37 etc, then get the zone from the postcode , just getting in a muddle with it all!

 

thanks for any help, really appreicaited.

[scotchegg78]

ok maybe if i simplify the problem someone can help!

 

 

I want to select all from a table of postcodes where the postcode is the same as the start of a sting.

 

eg

 

SELECT poscodeid from postcodes where postcode LIKE 'STRING'

but of course postcodes only goes upto B for exmaple, so a string of B12 YTS would fail, but i need it to pass!

[scotchegg78]

this works but is wrong for example..

"SELECT PostCodeID FROM postcodes WHERE PostCode LIKE '" . $qryCompanyDetails['CompanyPostCode'] . "'";

 

as I am comparing all PostCode fields to the full string, eg 'B' to 'B18 5TY' , which should pass as the string starts with B but the select will fail as it compares all of it.

I want to say something like this...

"SELECT PostCodeID FROM postcodes WHERE PostCode% LIKE '" . $qryCompanyDetails['CompanyPostCode'] . "'";

- note the wildcard after PostCode, but i cannot do this

 

[Illusion]

SELECT PostCodeID FROM postcodes WHERE PostCode like 'B%';

[Illusion]

OK,i modefied it to

$query="SELECT PostCodeID FROM postcodes WHERE PostCode like'".$qryCompanyDetails['CompanyPostCode']."%'";

[scotchegg78]

SELECT PostCodeID FROM postcodes WHERE PostCode like 'B%';

 

hi, sorry i dont think thats right, or you misunderstood, PostCode is the 'B' and the 2nd part of the where is the string 'B15 YGS'

Ofcourse the string may be Yhl 846 , or SW12 5TG etc etc, not always B something. so i need to be able to compare the string against the field values for the first few characters only. Its the Database Field value i need the wildcare on, not the variable string.

 

likein the example given, the vairble will then read Postcode like 'B18 5TY%' etc

but Postcode is just 'B', so it will find nothing like 'B18 5TY, i need to switch them around or something?

 

thanks for help btw

[Illusion]

now i think i understand ur problem

try this

Select postcode.ZoneID from postcode as a ,comapny as b having LOCATE(a.postcode,b.postcode)=1;

[scotchegg78]

now i think i understand ur problem

try this

Select postcode.ZoneID from postcode as a ,comapny as b having LOCATE(a.postcode,b.postcode)=1;

 

Right!

Firstly thanks again

 

I think i am grasping what thats doing, i think it would help if i ensured all the names are right and you could make sure the statment matches up?!

Could you check its right against the following names..

 

tables and fields..

 

company.CompanyPostCode- holds the string 'B15 T67' etc etc

postcodes.PostCode - holds the list of all postcode names, eg 'B' value

postcodes.ZoneID holds the Zones ID to use to link to the zone table..

zones.ZoneID holds the zone Ids that link to names..

zones.ZoneName, which is what i want to get to from a company postcode string!

 

 

 

[Illusion]

try this

Select c.ZoneName from from postcode as a ,comapny as b,zones as c where a.zoneid=c.zoneid having LOCATE(a.postcode,b.postcode)=1;

[scotchegg78]

try this

Select c.ZoneName from from postcode as a ,comapny as b,zones as c where a.zoneid=c.zoneid having LOCATE(a.postcode,b.postcode)=1;

 

sorry that makes no sense at all, and i dont think its even right? From From?

thanks anyway

[Illusion]

thats just a typing mistake? u can guess it, right?

[scotchegg78]

thats just a typing mistake? u can guess it, right?

 

sorry this having and locate are new to me, so i am still trying to figure it out, is this what it should be then matching my names...

 

$query_qryPostCodes = "SELECT c.ZoneName FROM postcodes as a ,company as b,zones as c where a.ZoneID=c.ZoneID HAVING LOCATE(a.PostCode,b.CompanyPostCode)=1";

 

as i get error Unknown column 'b.CompanyPostCode' in 'having clause'

 

thanks for you help, this is doing my head in, been on it all day, all i need to do is match a bloody string to a field value for the strings first chars as long as the field value isnt it?!

 

[Illusion]

if u used my query as it as that error may be due to misspelling of company, so try to change

 

comapny as b to company as b. Also make sure that column names match to actual names.

 

today it seems i am little bit gloomy........

[scotchegg78]

ok gone back to yours, removed the extra from and fixed the company.

 

still fails as $query_qryPostCodes = "Select c.ZoneName from postcode as a ,company as b,zones as c where a.zoneid=c.zoneid having LOCATE(a.postcode,b.postcode)=1";

 

Table 'tariff.postcode' doesn't exist

 

i can fix from postcode to postcodes, but what of teh lowercase zoneid ?should this not be ZoneID?

[Illusion]

database, table and column names are case insensitive.

 

u can also add one more where condition that joins  a to b or b to c so that we can eliminate some records

 

Did u got any error or no results.

[scotchegg78]

Using

 

  $query_qryPostCodes = "Select c.ZoneName from postcodes as a ,company as b,zones as c where a.ZoneID=c.ZoneID having LOCATE(a.PostCode,b.PostCode)=1";

 

i get Unknown column 'b.postcode' in 'having clause'.

 

again if it help these are my table names...

 

company(CompanyID,CompanyPostCode,..),

postcodes(PostCodeID,PostCode,ZoneID)

zones(ZoneID,ZoneName)

 

[scotchegg78]

Does anyone know whats wrong with this ...

 

$qryPostCodes = "SELECT ZoneID FROM postcodes WHERE PostCode LIKE SUBSTRING(" . $row_qryCompanyDetails['CompanyPostCode'] . ",,LENGTH(PostCode))";

 

 

thanks

[fenway]

Yes, two commas.

[Illusion]

Using

 

  $query_qryPostCodes = "Select c.ZoneName from postcodes as a ,company as b,zones as c where a.ZoneID=c.ZoneID having LOCATE(a.PostCode,b.PostCode)=1";

 

i get Unknown column 'b.postcode' in 'having clause'.

 

again if it help these are my table names...

 

company(CompanyID,CompanyPostCode,..),

postcodes(PostCodeID,PostCode,ZoneID)

zones(ZoneID,ZoneName)

 

change this part LOCATE(a.PostCode,b.PostCode) to  LOCATE(a.PostCode,b.CompanyPostCode).

I assumed that ur company table also has a postcode column, u could have changed the table columns in the query according to existing tables sturctures if you follow my logic in the query.