On Thu, 3 Jun 2004 13:06:54 -0700 "Chris Dietzler" <[EMAIL PROTECTED]> wrote:
> Running 4.0.18 > > I am trying to run a query where the query gets the offer_ID of a > certain customer from the offer table and displays in the results > the offer_Name associated with the offer_ID. Right now the way the > query is working it displays all the offers in the offer table > regardless of the offer_ID assigned to the customer. Any thoughts? > > > SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, > o.offer_Name FROM customers c, assets a , offers o WHERE > c.cst_SiteID = a.asset_SiteID AND cst_Name LIKE 'z%' <snip> Global rule for joins - if you have N tables (or table aliases) involed a JOIN, you need N-1 JOIN conditions in your WHERE clause, or using INNER JOIN clauses. In your query cst_Name LIKE 'z%' is NOT a join condition, it's just a filter. Nothing actually joins your offers table in the above query. Try: SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND c.cst_Offer = o.offer_ID AND cst_Name LIKE 'z%'; Or in preferable INNER JOIN syntax which makes it easier to forget a JOIN condition: SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c INNER JOIN assets a ON c.cst_SiteID = a.asset_SiteID INNER JOIN offers o ON c.cst_Offer = o.offer_ID WHERE cst_Name LIKE 'z%'; Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
