Joins headache. Please help!

Discussion in 'MySQL' started by alphacooler, Jul 5, 2006.

0
  1. #1
    So I have two tables I want to join.

    table 1(cars): carID, userID, entryTitle, entryDesc, price, tags
    tabe 2(users): userID, username, password

    From a search field, I want to pull up all the car details as well as show the username of the person who posted that entry for any entry that has a title or tag that matches the searched keyword.

    Here is my query:

    "SELECT * FROM cars, users WHERE cars.userID = users.userID AND entryTitle LIKE '%".$searchTerm."%' OR tags LIKE '%".$searchTerm."%'"

    ** I am not sure if it makes any difference, but the TAGS column stores multiple tags (usually less than 3) delimited by spaces. **

    I get the typical join problem of there being WAY too many results due to table multiplication. When I run this query without the Join, it works fine of course.

    I've used this join before and it works fine, but my guess is that the LIKE part of the query is causing some problems.
     
    alphacooler, Jul 5, 2006 IP
  2. dc dalton

    dc dalton Active Member

    Messages:
    521
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    58
    #2
    What db are you using? If you are using Postgresql or a more powerful (not MySQL) you could create a view and just call that passing the proper variables. A lot faster and a lot less strain on the server.
     
    dc dalton, Jul 8, 2006 IP
  3. TommyD

    TommyD Peon

    Messages:
    1,397
    Likes Received:
    76
    Best Answers:
    0
    Trophy Points:
    0
    #3
    "SELECT *
    FROM cars
    INNER JOIN users ON (cars.userID = users.userID)
    WHERE cars.entryTitle LIKE '%".$searchTerm."%';"


    Close?

    tom
     
    TommyD, Jul 8, 2006 IP