Tricky Query

[bcoffin]

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]

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

[bcoffin]

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

My primary key (auto-incr).

[Wildbug]

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]

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]

.. 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]

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]

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]

running MySQL 4.0.24_Debian-10sarge1-log

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

[bcoffin]

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]

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]

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]

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]

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]

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]

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]

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]

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]

OH, btw, the zipData table looks like this:

 

zipcode varchar(5)

lon varchar(

lat varchar(

[bcoffin]

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]

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

 

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

 

 

(Using code tags should defeat the smilies.)