Thread: better query?

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    46
    Rep Power
    14

    better query?


    a partial table from a photos database that stores the names of the people in the photo and its ID.

    id | Names | Photo ID
    412 | Fred, Bill | 8787
    413 | Terry, Alice, Sam | 1887
    414 | Bill, Sam | 42
    415 | Sam | 2
    416 | Sam T, Bill, Fred | 43
    417 | Terry, Bill, Erin | 353
    418 | Sam, Terry | 888
    419 | Sam T | 357
    420 | Sam T, Erin | 847
    421 | Terry, Fran, Erin | 353

    I tried using
    Code:
    SELECT * FROM 'photos'  WHERE 'Names' regexp '[[:<:]]Sam[[:>:]]';
    to select the records with "Sam" somewhere in the 'Names' field.

    I got back all the record with "Sam" and also all the records with "Sam T" as well.

    413 | Terry, Alice, Sam | 1887
    414 | Bill, Sam | 42
    415 | Sam | 2
    416 | Sam T, Bill, Fred | 43
    418 | Sam, Terry | 888
    419 | Sam T | 357
    420 | Sam T, Erin | 847

    I then take this array and pass it to a filtering function I wrote so that the array will just have "Sam" photos.

    MY QUESTION : Is there a better SELECT statement to use to return just the records with "Sam" somewhere in the 'Names' field
    and not have to call my filtering function as I am doing?
  2. #2
  3. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,073
    Rep Power
    4101
    Originally Posted by artoo
    MY QUESTION : Is there a better SELECT statement to use to return just the records with "Sam" somewhere in the 'Names' field
    and not have to call my filtering function as I am doing?
    Not really. Your table design is wrong. You should be using a joining table to map names to photos with one name per row. Such as:
    Code:
    id  | Name  | PhotoID
    ----+-------+---------
    412 | Fred  |   8787
    413 | Bill  |   8787
    414 | Sam   |   1887
    415 | Alice |   1887
    416 | Terry |   1887
    417 | Sam   |     42
    418 | Bill  |     42
    419 | Sam   |      2
    420 | Sam T |     43
    421 | Bill  |     43
    422 | Fred  |     43
    423 | Terry |    353
    424 | Bill  |    353
    425 | Erin  |    353
    426 | Sam   |    888
    427 | Terry |    888
    428 | Sam T |    357
    429 | Sam T |    847
    430 | Erin  |    847
    431 | Terry |    353
    432 | Fran  |    353
    433 | Erin  |    353
    Once you fix your table design, then your query becomes as simple as
    Code:
    SELECT
        *
    FROM photos
    INNER JOIN photo_names ON photos.id = photo_names.PhotoID
    WHERE
        photo_names.Name='Sam'
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

IMN logo majestic logo threadwatch logo seochat tools logo