SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Central Illinois
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What's wrong with this query?

    I'm not that experienced with MySQL, but I thought I knew enough to do what I need this time. But I'm getting completely unexpected results and can't figure out the problem.

    Here's the query I'm using:

    Code:
    SELECT channels.name, channels.description, categories.name, categories.description FROM channels, categories WHERE channels.title='$var' OR categories.title='$var'
    $var contains a string that will be the 'title' of either a channel or a category. The query is intended to check both tables and return the applicable row from whichever table it finds a match for $var in the 'title' field. There are no duplicate 'title' field values between the two tables.

    The actual results I'm getting are a full list of all rows in whichever table it *shouldn't* have found a match in at all. In other words, when $var should match a single row in the 'channels' table, the query actually returns ALL rows in the 'categories' table, and vice-versa.

    What do I need to modify to get the expected results?

    Thanks for any ideas,

    Matthew

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you forgot to join the matching rows

    change this --
    Code:
    FROM channels, categories 
    WHERE channels.title='$var' OR categories.title='$var'
    to this --
    Code:
      FROM channels
    INNER
      JOIN categories 
        ON categories.id = channels.category_id
     WHERE channels.title = '$var' 
        OR categories.title = '$var'
    see those two columns marked in blue? i guessed at the names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Central Illinois
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for such a quick reply. Unfortunately, I'm not sure JOIN is what I'm after because the two tables in question have no relationship, and really, no relevance to one another. I just need to know which table $var is found in.

    I did try the code you suggested, but it also yielded strange results by fetching data from both tables from rows where the ID's matched one another. I only want to retrieve data from one row from whichever table matches $var - the other fields I'm using for SELECT can remain null as they won't be used anyway.

    Is there any way to accomplish this?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    PHP Code:
    SELECT 
        channels
    .name
      
    channels.description
    FROM channels
    WHERE channels
    .title='$var'
    UNION
    SELECT
      
    categories.name
      
    categories.description 
    FROM categories 
    WHERE categories
    .title='$var' 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •