1

Hey guys I am this close in being able to represent the data that I wish to display The column in question is a.state_Id. I understand from the research I have done that the a.State_Id is out of scope. What is the missing piece to my sql statement?

If I take out the a.State_ID = c.State_Id leaving only the b.Product_Id = c.Product_Id then the data is displayed but I need to match against the state and product.

I know i need to add in another join somewhere but im not sure how. If anyone could help me that would be awesome!

SELECT a.state_id, 
       a.state_name, 
       a.state_abbreviatedname, 
       b.product_id, 
       b.product_name, 
       c.stateproduct_price 
FROM   states a, 
       products b 
       LEFT OUTER JOIN stateproducts c 
ON a.state_id = c.state_id AND b.product_id = c.product_id

Update 1

The states table has been populated and contains the following fields:

State_Id
State_Name
State_AbbreviatedName

The Products table which has been populated to contain the base products. This table has the following fields:

Product_Id
Product_Name

Each state will have the same products however the price for each product changes with each state. This is why i have the StateProducts table. This table is empty and will be populated one by one by an interface I have created. The statesproducts table will have the following fields

State_Id //reference/relational field to be user for comparison
Product_Id //reference/relational field to be user for comparison
StateProduct_Price //new field

so i understand that i will receive NULL values in the price column.

I have been able to return a sort of cartesion product of the States and products table. However I now need to append the price for each combination on the right side of this cartesion table. bearing in mind that stateproducts table is empty how would i accomplish this?

Pavan
  • 17,840
  • 8
  • 59
  • 100
  • Please stop mixing explicit and implicit `JOIN`s. – Kermit Feb 11 '13 at 15:51
  • njk, I'm an ios programmer not really a database designer. Im currently trying to understand it all. If you could explain what you mean by not mixing explicit and implicit joins, and show us how theyre meant to be written that would be educational for me. Thanks – Pavan Feb 11 '13 at 15:54
  • 1
    @Pavan: See http://en.wikipedia.org/wiki/Join_%28SQL%29#Inner_join or http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins for discussions of the difference between implicit and explicit joins. –  Feb 11 '13 at 15:57
  • Thanks mark i'll read on this. Appreciate the reference guide. – Pavan Feb 11 '13 at 16:06

3 Answers3

2

I guess you have a Many-to-Many relationship in which States can have multiple Products and Products can be on different States. So you need to join States into the mapping table StateProducts so you can be able to get the products on table Products.

SELECT  a.State_ID, 
        a.State_Name, 
        a.State_AbbreviatedName, 
        b.Product_Id, 
        b.Product_Name, 
        c.StateProduct_Price
FROM    States a
        INNER JOIN StateProducts c
            ON a.State_ID = c.State_Id
        INNER  JOIN Products b 
            ON b.Product_Id = c.Product_Id

To further gain more knowledge about joins, kindly visit the link below:

UPDATE 1

use CROSS JOIN keyword,

SELECT a.state_id, a.state_name, a.state_abbreviatedname, b.product_id, b.product_name, c.stateproduct_price 
FROM   states a 
       CROSS JOIN products b 
       LEFT OUTER JOIN stateproducts c
          ON a.state_id = c.state_id AND 
             b.product_id = c.product_id
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Hey JW, your sql statement produced no errors but it produced an empty set. I was hoping to return a set which contained the cartesion product of tables States and Products, and for each combination produced to append a price from the StateProducts table. And if the price doesnt exist to just return NULL in the Price column for that particular row, and any rows that do exist to display the price for that current combination. Hopefully I have made it clear. If you could check my updated post it may help. Thanks JW – Pavan Feb 11 '13 at 16:35
  • how about using the keyword `CROSS JOIN` instead of comma, example `SELECT a.state_id, a.state_name, a.state_abbreviatedname, b.product_id, b.product_name, c.stateproduct_price FROM states a CROSS JOIN products b LEFT OUTER JOIN stateproducts c ON a.state_id = c.state_id AND b.product_id = c.product_id ` – John Woo Feb 11 '13 at 16:37
  • Hey JW, what you wrote in the comment above is the data set I have been trying to retrieve. If you could update your post so that I can mark this question as answered. HAH.. as if it was only letter that needed changing in this case the comma! Thank you for your help JW – Pavan Feb 11 '13 at 16:52
  • yayy thanks. As for the article you posted regarding visual representation of SQL joins, that will be very helpful. Cheers – Pavan Feb 11 '13 at 17:09
2

from A to C, C to B.

By doing LEFT JOIN for each, you'll still end up with all possible states regardless of having actual state products.

SELECT 
      a.State_ID, 
      a.State_Name, 
      a.State_AbbreviatedName, 
      b.Product_Id, 
      b.Product_Name, 
      c.Product_Price 
   FROM 
      States a
         LEFT  JOIN StateProducts c 
            ON a.State_ID = c.State_Id 
            LEFT JOIN Products b 
               ON  c.Product_Id = b.Product_Id
DRapp
  • 47,638
  • 12
  • 72
  • 142
  • DRapp thanks for posting, I am getting this error with your proposed solution: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a.State_ID = c.State_Id LEFT JOIN Products b ON c.' at line 11 If you could have a look at my updated post so you can get more information so you could then perhaps revise your sql statement – Pavan Feb 11 '13 at 16:30
  • @pavan, while looking at your revised structure, it appears you started with "StateProduct_Price", not "Product_Price". I revised the query, but that is probably what you and others were running into since they all used "StateProduct_Price" column. – DRapp Feb 11 '13 at 16:35
  • I see you've just changed the row name? the name can be anything, but for readability I chose StateProduct_Price, since it is a product price unique to each state. nevertheless have you looked at my first comment to your post? Your sql statement is producing an error. – Pavan Feb 11 '13 at 16:41
  • @Pavan, The JOIN conditions appear to match the column names you have provided... HOWEVER, there IS NO column named "StateProduct_Price" but instead "Product_Price". It may be associated to a specific state, but its actual column name is still "Product_Price" – DRapp Feb 11 '13 at 16:47
  • Thanks. I have updated my updated reference. There is a StateProduct_Price in the StateProducts table but i wrote down product_price by accident in the post. I have updated accordingly, but your error still persists. Have a look at JW's answer. Cheers for your help DRapp – Pavan Feb 11 '13 at 16:51
  • @Pavan,ok, and looked again.. I was missing the "ON" clause for the join.. should get it now. – DRapp Feb 11 '13 at 16:57
0

Try writing the from clause like this:

from StateProducts c join
     States a
     on c.state_id = c.state_id join
     Products b
     on c.product_id = b.product_id

For the Cartesian product (that is, to get all examples of states and products):

SELECT s.state_id, s.state_name, s.state_abbreviatedname, 
       p.product_id, p.product_name, 
       sp.stateproduct_price 
from States s cross join
     Products p left outer join
     StateProducts sp
     on sp.state_id = s.state_id and
        sp.product_id = p.product_id
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I appreciate you taking your time to answer. Gordon this returns an empty set, I'm trying to retrieve a cartesian set with appended fields from another table based on the combinations dependant on state_id and product_id. Have a look at my updated post – Pavan Feb 11 '13 at 16:15