SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Too Many Records

    Code:
     
    SELECT pr.Model_Number, pr.Stock_Number, pr.pl_specs, p.filename
    FROM products pr, prodclasses pc, pdfs p, pdflookup pup
    WHERE Model_Number LIKE '%colname%' OR Stock_Number LIKE 
    '%colname%' OR pl_specs LIKE '%colname%' AND pr.class_code = pc.CODE
    AND pup.classID = pc.ID AND pup.pdfID = p.ID
    Hello all, I'm trying to build a statement that searches for all the products by %colname% and then joins them with their appropriate PDF. The thing is, I'm trying to use a class identifier (which is a two letter code) which is in a fourth table (prodclasses) as the binding agent.

    Here's the thing, I want to use this fourth table as a kind of lookup because I'm using a similar query elsewhere in the site, that takes prodclasses and finds the pdf for each product class through pdflookup.

    Each class has a PDF. Every product in a class will use the class' PDF. Since I already know which products are in which classes by the two-letter code and I DON'T know which products use which PDFs, I'm thinkin' that it's easiest to go through the prodclasses table, instead of adding entries to the PDF lookup table for each product.

    I've seen solutions to something like this before, but I can't remember what it is... do I need 'JOIN' or something?

    Thanks for any help...
    Chris

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i didn't understand your explanation, but your query has a logic error

    here's what you've got:
    Code:
      FROM products pr
         , prodclasses pc
         , pdfs p
         , pdflookup pup
     WHERE Model_Number LIKE '%colname%' 
        OR Stock_Number LIKE '%colname%' 
        OR pl_specs LIKE '%colname%' 
       AND pr.class_code = pc.CODE
       AND pup.classID = pc.ID 
       AND pup.pdfID = p.ID
    and here's the way it is interpreted based on the precedence of ANDS over ORs:
    Code:
      FROM products pr
         , prodclasses pc
         , pdfs p
         , pdflookup pup
     WHERE (
           Model_Number LIKE '%colname%' 
           )
        OR (
           Stock_Number LIKE '%colname%' 
           )
        OR (
           pl_specs LIKE '%colname%' 
       AND pr.class_code = pc.CODE
       AND pup.classID = pc.ID 
       AND pup.pdfID = p.ID
           )
    if you're gonna continue to use the "table list" syntax to join tables, then you need to parenthesize like this:
    Code:
      FROM products pr
         , prodclasses pc
         , pdfs p
         , pdflookup pup
     WHERE (
           Model_Number LIKE '%colname%' 
        OR Stock_Number LIKE '%colname%' 
        OR pl_specs LIKE '%colname%' 
           )
       AND pr.class_code = pc.CODE
       AND pup.classID = pc.ID 
       AND pup.pdfID = p.ID
    my advice is to switch to explicit JOIN syntax, and then logic problems have a much smaller chance of occurring:
    Code:
      FROM products pr
    inner
      join prodclasses pc
        on pr.class_code = pc.CODE
    inner
      join pdflookup pup
        on pc.ID  = pup.classID 
    inner
      join pdfs p
        on pup.pdfID = p.ID
     WHERE Model_Number LIKE '%colname%' 
        OR Stock_Number LIKE '%colname%' 
        OR pl_specs LIKE '%colname%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Holy Moses!.. Works perfectly! Thanks so much, man! You know, I use strings of "AND" statements a lot and never use joins, inner joins, etc. etc. It's like a bad habit...

    Can statements that use AND a lot generally be put together using the JOIN syntax? It seems like it might be somewhat of a paradigm shift, are there any good resources that teach how to move to this approach?


    Thanks again, bro..

    Chris

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chrispalle
    Can statements that use AND a lot generally be put together using the JOIN syntax?
    yes

    and of course you must use JOIN syntax if you want an outer join

    "table list" syntax (which you say "use AND a lot") can only do inner joins

    any good sql tutorial should cover JOIN syntax

    i don't recall offhand if there are any here at sitepoint (aside: i'd write one if they ask me)

    there are links to several good tutorials on the SQL Links page on my site which i am not allowed to link to in the body of this posting but which you can reach by going to the SQL section of my r937 site in my sig below

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •