Results from same table twice in same query.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Results from same table twice in same query.

Post by Stryks »

Hi guys,

Basically, I have a table set up to hold postcodes, each of which are broken up into areas within that postcode. So each actual location represents one row, while the entry in the postcode column is identical for multiple entries.

My problem is wanting to break down each location into multiple sub-zones. While I could just add new entries, this becomes unweildly and gives rise to the addition of false or redundant zones. So what I wanted to do was to create a zone but link it back to a parent location.

Basically then, if I specified row 412 as my location, then the query would return row 412 as well as any new zones which depend upon that one. This I can do myself.

What if I then wanted to seach by postcode. I would need to return all zones in each postcode, as well as any zones based on the ones returned. This I cant seem to do.

Am I making sense? Can anyone help me out with a structure / query that might do the job?

Thanks.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

To clarify (I hope) I am trying to do something along the following lines.

Code: Select all

SELECT 
  `tbl_postcodes`.`Name`
FROM
  `tbl_postcodes`,
  `tbl_postcodes` AS `dupe`
WHERE
  (`tbl_postcodes`.`Postcode` = '3629') OR 
  (`tbl_postcodes`.`MPC_PK` = `dupe`.`MPC_Ref`)
This brings back a huge list that takes ages to process and doesn't include the result that I am after.

Thanks for any input
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Well, I have something that is sort of doing the job, though its very slow and I am not sure of how to get any kind of order out of it, as for display purposes, the split zones would need to be together, with the original zone last.

Code: Select all

SELECT 
  `tbl_postcodes`.`Name`
FROM
  `tbl_postcodes`,
  `tbl_postcodes` AS `dupe`
WHERE
 (`tbl_postcodes`.`Postcode` = '3629') OR (`tbl_postcodes`.`MPC_Ref` = `dupe`.`MPC_PK`)
GROUP BY
`tbl_postcodes`.`Name`
But having looked at it, and not knowing how to do it any other way, I think I might just be best off calling all zones with the correct postcode, and then running another query to sort through and find any sub-zones which match the current zone.

It's probrably harder to achieve, but unless any of you gurus have a solution, I guess I'm stuck with it.

Thanks.
Post Reply