Tricky Query

bcoffin · April 25, 2007

Hi.. wondering if this is possible.. Here are my tables:

THEATERS has ID and ZIPCODE

PEOPLE has ZIPCODE

TRIPS has ID and THEATERLIST (looks like this: "THEATERS.ID1|THEATERS.ID2|THEATRES.ID10")

I would like to query a record set to get

ALL VALUES in THEATERS (including ID and ZIPCODE)

PLUS

COUNT of PEOPLE in that ZIPCODE

PLUS

COUNT of TRIPS where the THEATER ID appears in my TRIPS.THEATERLIST, pipe-separated string...

I'll eventually use that zip-code range finder, so that I can use a

COUNT of PEOPLE where their PEOPLE.ZIPCODE in (10001, 10002...) <-- meaning a 25mi radius of THEATERS.ZIPCODE.

Can somebody help me concoct this query?

Wildbug · April 25, 2007

Is TRIPS.ID the same as THEATERS.ID? If not, what does TRIPS represent?

bcoffin · April 25, 2007

No.. it's a separate unique identifier that I'm using for TRIPS.

My primary key (auto-incr).

Wildbug · April 25, 2007

This query works on a mock up I made of your db structure:

SELECT
theaters.id,
theaters.zipcode,
SUM(trips.theaterlist REGEXP CONCAT('theaters.id',theaters.id,'[[:>:]]')) AS zips
FROM
theaters LEFT JOIN
(SELECT DISTINCT zipcode FROM people) AS pz ON theaters.zipcode=pz.zipcode,
trips
GROUP BY theaters.id;

// ...yields:

+----+---------+------+
| id | zipcode | zips |
+----+---------+------+
|  1 | 12345   |    3 |
|  2 | 15551   |    2 |
|  3 | 67776   |    1 |
|  4 | 90990   |    2 |
|  5 | 43224   |    0 |
+----+---------+------+
5 rows in set (0.00 sec)

Where the data was as follows:

mysql> select * from theaters; select *,COUNT(*) from people GROUP BY zipcode; select * from trips;
+----+---------+
| id | zipcode |
+----+---------+
|  1 | 12345   |
|  2 | 15551   |
|  3 | 67776   |
|  4 | 90990   |
|  5 | 43224   |
+----+---------+
5 rows in set (0.00 sec)

+---------+----------+
| zipcode | COUNT(*) |
+---------+----------+
| 12345   |        6 |
| 15551   |        6 |
| 54321   |        1 |
| 67776   |        2 |
+---------+----------+
4 rows in set (0.00 sec)

+----+-----------------------------------------------------+
| id | theaterlist                                         |
+----+-----------------------------------------------------+
|  1 | theaters.id1|theaters.id2                           |
|  2 | theaters.id3|theaters.id4|theaters.id1|theaters.id4 |
|  3 | theaters.id1                                        |
|  4 | theaters.id2|theaters.id4                           |
+----+-----------------------------------------------------+
4 rows in set (0.00 sec)

bcoffin · April 25, 2007

SWEET.. gonna give it a try right now.

Thank you sooo much for your genius.

I'll post a reply when I run the query.

bcoffin · April 25, 2007

.. hmm.. well those individual queries

(mysql> select * from theaters; select *,COUNT(*) from people GROUP BY zipcode; select * from trips;)

works without problem.. but when I run the combined query

(SELECT

theaters.id,

theaters.zipcode,

SUM(trips.theaterlist REGEXP CONCAT('theaters.id',theaters.id,'[[:>:]]')) AS zips

FROM

theaters LEFT JOIN

(SELECT DISTINCT zipcode FROM people) AS pz ON theaters.zipcode=pz.zipcode,

trips

GROUP BY theaters.id;)

I get an error from mysql:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT zipcode FROM people) AS pz ON theaters.zipcode

Wildbug · April 25, 2007

The query uses subqueries which are not available on some older versions of MySQL (<4.1, I think). That could be the source of your error. What version are you using?

Wildbug · April 25, 2007

Oh, and those individual queries weren't really relevant to your problem; I was just printing out my mock data so you could verify the output from the desired query.

bcoffin · April 25, 2007

running MySQL 4.0.24_Debian-10sarge1-log

i suppose I oughtta start looking for documentation about upgrading MySQL.

bcoffin · April 26, 2007

k.. finally got the update figured out.

I'm running

MySQL 4.1.11-Debian_4sarge7-log

now and getting 0 for all zips...

bcoffin · April 26, 2007

Could be that I'm translating the tables, columns that I used for this post, wrong with the actual columns/table names.. Sorry, Wildbug, only did that cuz I didn't want to confuse ppl

here's the real stuff:

Table: meet_theatres

cols: id, zip

(id is a unique identifier, zip is the zipcode for that theater record)

Table: meet_profile

cols: id, theatres

(id is a unique identifier, theatres looks like this "3362|2878|2881|2883|2885", where 3362 is an id from meet_theatres)

Table: member

cols: id, zipcode

(id, again unique identifier, zipcode is the member's personal zipcode, like your people table)

this is the translation of your query:

SELECT
meet_theatres.id,
meet_theatres.zip,
SUM(meet_profile.theatres REGEXP CONCAT('meet_theatres.id',meet_theatres.id,'[[:>:]]')) AS zips
FROM
meet_theatres LEFT JOIN(SELECT DISTINCT zipcode FROM member) AS pz ON meet_theatres.zip=pz.zipcode,
meet_profile
GROUP BY meet_theatres.id;

Wildbug · April 26, 2007

Ah! Okay. I was working with your original information. The first thing wrong now that I see is that the REGEXP/CONCAT bit is different than what you're actually working with. It's trying to match 'meet_theaters.id3362' instead of '3362'...so change that part to:

SUM(meet_profile.theaters REGEXP CONCAT(meet_theaters.id,[[:>:]]')) AS zips

// ...or...

SUM(meet_profile.theaters LIKE CONCAT('%',meet_theaters.id,'%')) AS zips

If all of your meet_theater.id's are the same length (four digits), then you shouldn't need the word boundary portion of the regular expression ([[:>:]]) -- that's just to avoid matching shorter numbers in longers ones (like '86' in '3864').

Did my original mock up seem to capture the data in the way you were expecting?

(Incidently, it's generally considered a bad idea to keep data in delimited strings like that. A "proper" database would have them in yet another, seperate table. Or, if member ID and theater ID are the only columns, then they could be seperated such that each is its own row.)

bcoffin · April 26, 2007

hi wildbug.. thanks for the afterschool help with this one.

would 3362

be found in 3362|2878|2881|2883|2885

be found in 2878|2881|3362|2883|2885

be found in 2878|2881|2885|2883|3362

likewise, what happens if we search 62, would it

be found in 3362|2878|2881|2883|2885

be found in 2878|2881|62|2883|2885

.. I know I know.. i'm seriously regretting my choice to pipe-separate like that.

it was just really easy at the time. I'm sure I wouldn't be in this boat if i had done the table design like you recommend.

bcoffin · April 26, 2007

Oh.. sorry.. meant to say YES, the mockup worked great.

I just tried your new query and it's awesome!

Thanks again, Wildbug.

.. can I contact you for freelance work sometime?

bcoffin · April 26, 2007

Another question for you..

The phpZipLocator uses this php function to grab all zipcodes within x mile radius of a provided zipcode:

function inradius($zip,$radius)
    {
    	$i = "";
        global $db_zip;
        $query="SELECT * FROM zipData WHERE zipcode='$zip'";
        $db_zip->query($query);

        if($db_zip->affected_rows()<>0) {
            $db_zip->next_record();
            $lat=$db_zip->f("lat");
            $lon=$db_zip->f("lon");
            $query="SELECT zipcode FROM zipData WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ";
            $db_zip->query($query);
            if($db_zip->affected_rows()<>0) {
                while($db_zip->next_record()) {
                    $zipArray[$i]=$db_zip->f("zipcode");
                    $i++;
                }
            }
        }else{
            return "Zip Code not found";
        }
     return $zipArray;
    } // end func

Since we're starting with meet_theatres.zip,

can we join the zipData table (to get the lat/lon),

and return a count of members (member.zipcode) that are in that result?

fenway · April 26, 2007

hi wildbug.. thanks for the afterschool help with this one.

would 3362

be found in 3362|2878|2881|2883|2885

be found in 2878|2881|3362|2883|2885

be found in 2878|2881|2885|2883|3362

likewise, what happens if we search 62, would it

be found in 3362|2878|2881|2883|2885

be found in 2878|2881|62|2883|2885

.. I know I know.. i'm seriously regretting my choice to pipe-separate like that.

it was just really easy at the time. I'm sure I wouldn't be in this boat if i had done the table design like you recommend.

Search for it with pipes on either side... and yes, you should never do this again.

Wildbug · April 26, 2007

Search for it with pipes on either side... and yes, you should never do this again.

That wouldn't return what he expects in the case of a lone number or one at either terminus of the string. Searching with word boundries works in all cases. Of course, if they are and will always be four digit numbers (or however many, but all of the same length) then the inclusion of neither word boundries nor pipes would be necessary because there would not be the chance of a shorter numeric string matching within a longer one.

fenway · April 26, 2007

That wouldn't return what he expects in the case of a lone number or one at either terminus of the string.

Well, you need to add pipes to the actual field value as well to deal with these boundary cases.

bcoffin · April 26, 2007

OH, btw, the zipData table looks like this:

zipcode varchar(5)

lon varchar(

lat varchar(

bcoffin · April 26, 2007

The ids can be any length from 1 digit to 4 digits, currently.

I didn't mean to put those sunglassed-smilies, it's supposed to be ( 8 )...

Wildbug · April 26, 2007

Okay, so you do need word boundaries in the regexp.

...REGEXP CONCAT("[[:<:]]",id,"[[:>:]]")...

(Using code tags should defeat the smilies.)

Sign In

Tricky Query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information