Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    California
    Posts
    40

    Unanswered: SQL Statement question

    Hi everyone,
    I wasn't sure what exactly to search on this one, so hopefully this hasn't been answered a hundred times previously. Anyway, I have a statement:

    Code:
    Select Materials.*, Vendors.Name As VendorName, Vendors.Phone1 FROM Materials, Vendors WHERE Materials.Vendor = Vendors.ID ORDER BY Materials.Name, Thickness
    But I realized I had missing records when iterating through my recordset. For all the records in the db that had a 'NULL' value in the column "Vendor" in my "Materials" table, they wouldn't show up in my recordset. And I think I realize that it's because of this part:
    Code:
    WHERE Materials.Vendor = Vendors.ID
    Am I right? But the problem is, don't I need that to grab the vendor name (Vendors.Name or VendorName) from the "Vendors" table? Can someone please enlighten me? I can give exact table structure if need be, just let me know.
    Thanks.

  2. #2
    Join Date
    Oct 2003
    Posts
    84
    SELECT Materials.*, Vendor.VendorName, Vendors.Phone1
    FROM Materials
    LEFT JOIN Vendors ON Materials.Vendor = Vendors.ID
    ORDER BY Materials.Name;


    This is assuming that some materials do not have vendors, and you want to return ALL materials.

    Try to avoid using materials.* unless you really need it

  3. #3
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Awesome! Thank you so much! So does using wildcards like that create problems sometimes or something? I'll have to look more into how that JOIN stuff works. Thanks again, and for the quick response too!

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by domiflichi
    Awesome! Thank you so much! So does using wildcards like that create problems sometimes or something? I'll have to look more into how that JOIN stuff works. Thanks again, and for the quick response too!
    No, the wildcard wasn't the issue. You were doing an "inner" join using the old join syntax and afx2029 changed it to an "outer" join using the newer SQL-92 join syntax which is what you wanted. You need to use the SQL-92 syntax to do outer joins.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Wild-cards have other drawbacks. Check Brett's blog for a list of grievances he has against them.

    I dislike them for the basic reason that they are lazy and make code difficult to read and debug, but there are performance issues as well.

    Code like a pro!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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