Ignore the previous post it has an error. This is the corrected sql:
SELECT *
FROM Locations L, Brick B
WHERE (LEFT(L.'Post Code', LOCATE(' ', L.'Post Code')) = B.Brick)
This should do it.
----- Original Message -----
From: "Ricardo Lopes" <lopes@xxxxxxxxxxxxxxx>
To: "Shaun" <shaun@xxxxxxxxxxxxxx>
Cc: "PHP DB" <php-db@xxxxxxxxxxxxx>
Sent: Tuesday, February 17, 2004 10:32 AM
Subject: Re: Brick Codes
> SELECT *
> FROM Locations L, Brick B
> WHERE (LEFT(L.'Post Code', LOCATE(' ', L.'Post Code') = LEFT(B.Brick,
> LOCATE(' ', L.'Post Code')))
>
> The functions if locate gives you the position of the character in the
> string, if this is not what you whant you can search the mysql string
> functions documentation in:
>
>
http://www.mysql.com/documentation/mysql/bychapter/manual_Functions.html#Str
> ing_functions
>
>
> ----- Original Message -----
> From: "Shaun" <shaun@xxxxxxxxxxxxxx>
> To: <php-db@xxxxxxxxxxxxx>
> Sent: Tuesday, February 17, 2004 10:19 AM
> Subject: Re: Brick Codes
>
>
> > Thank you for your replies,
> >
> > The brick and postcode will never match so I need to compare the
Postcode
> in
> > Locations to the Postcode in Brick. Something like:
> >
> > SELECT B.Brick
> > FROM Locations L, Brick B
> > WHERE (LEFT(L.Postcode,3) = LEFT(B.Postcode,3))
> >
> > The problem is that some of the postcodes stored in Brick have three
> digits
> > and some have four, is there a way of capturing everything before the
> first
> > space in L.Postcode and compare that to B.Postcode?
> >
> > Thanks for your help
> >
> > "Ignatius Reilly" <ignatius.reilly@xxxxxxx> wrote in message
> > news:06b901c3f53e$9c371960$0100a8c0@xxxxxxxxx
> > > Hmmm...
> > >
> > > Better try:
> > > WHERE (LEFT( Brick,3) = LEFT(`Post Code`,3))
> > > ^^ no quotes ^^ back ticks (because of
> the
> > > space in the column name)
> > >
> > > HTH
> > > Ignatius
> > > _________________________
> > > ----- Original Message -----
> > > From: "Ricardo Lopes" <lopes@xxxxxxxxxxxxxxx>
> > > To: "Shaun" <shaun@xxxxxxxxxxxxxx>
> > > Cc: "PHP DB" <php-db@xxxxxxxxxxxxx>
> > > Sent: Tuesday, February 17, 2004 10:23
> > > Subject: Re: Brick Codes
> > >
> > >
> > > > That may depend of what database server you are using, for mysql you
> can
> > > > use:
> > > >
> > > > ........ WHERE (LEFT('Brick',3) = LEFT(Post Code,3))
> > > >
> > > > If you are using other dbserver, check your manual.
> > > >
> > > > ----- Original Message -----
> > > > From: "Shaun" <shaun@xxxxxxxxxxxxxx>
> > > > To: <php-db@xxxxxxxxxxxxx>
> > > > Sent: Monday, February 16, 2004 10:45 PM
> > > > Subject: Brick Codes
> > > >
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a table of Locations around the country. My system produces
> > > reports
> > > > > based on these Locations. I also have a table containing Brick
Codes
> > > e.g.
> > > > >
> > > > > Brick Post Code
> > > > > AB51 AB51
> > > > > AB52 AB52
> > > > > AB55 AB55
> > > > > AB56 AB56
> > > > > AL01 AL1
> > > > > AL02 AL2
> > > > > AL03 AL3
> > > > > AL04 AL4
> > > > >
> > > > >
> > > > > How can I compare the first 3 or 4 letters of the postcode in the
> > > > Locations
> > > > > table to the corresponding entry in the Brick Codes table so I can
> add
> > > it
> > > > to
> > > > > my report?
> > > > >
> > > > > Thanks for your help
> > > > >
> > > > > --
> > > > > PHP Database Mailing List (http://www.php.net/)
> > > > > To unsubscribe, visit: http://www.php.net/unsub.php
> > > > >
> > > > >
> > > >
> > > > --
> > > > PHP Database Mailing List (http://www.php.net/)
> > > > To unsubscribe, visit: http://www.php.net/unsub.php
> > > >
> > > >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
