#1
  1. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,601
    Rep Power
    595

    Complex Query (for me)


    I have what is a complex query and, although I know how to do a basic select, this one is beyond me and I need some help. Here are the relevant tables and columns based on what I think I need to do:

    product.product_id
    product.model

    vendor.vproduct_id
    vendor.vendor

    I need to determine the vproduct_id for given values of vendor.vendor and product.model. The product.product_id corresponds to the vendor.vproduct_id. The problem is that there are duplicate model values but each has a different product_id/vproduct_id per vendor.vendor. For example:
    Code:
    product
       product_id   model
       54                product1
       53                product1
       55                product1
       56                product2
       57                product2
       58                product2
    
    vendor
       vendor   vproduct_id
       6             54
       7             53
       6             57
       7             58
       8             55
       8             56
    I need a query that gives me 58 if vendor=7 and model=product2. TIA.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    sql Code:
    SELECT
    	vendor.vproduct_id
    FROM
    	product
    	JOIN
    		vendor
    	ON
    		product.product_id = vendor.vproduct_id
    WHERE
    	product.model = 'product2'
    	AND vendor.vendor = 7
    ;

    Note, however, that your current data model does not guarantee a unique result. That's only implied by your data.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,601
    Rep Power
    595
    Thanks, it works perfect. I thought it would require a JOIN or something like that but I don't yet understand that concept. It will take some reading and head scratching to assimilate it.
    Last edited by gw1500se; July 27th, 2013 at 02:02 AM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,601
    Rep Power
    595
    I may have spoken too soon. I thought this worked but now I am not getting the right return. When I use the proposed query I get 2 rows rather than 1:

    53
    58

    I don't understand why it seems to ignore the 'model=' clause. Yet if I put in an invalid model, I get the empty set which I would expect.

    I may have been getting the same thing before too but it happened that the 58 was first so I did not notice there were 2 rows.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    How about:

    Code:
    SELECT v.vproduct_id
      FROM product p
         INNER JOIN vendor v
            ON p.product_id = v.vproduct_id
                                 AND
                 v.vendor = 7
      WHERE p.model = 'product2'
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,601
    Rep Power
    595
    Thanks for the reply but no joy. I still get 2 rows.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    Oh?
    Code:
    mysql> select * from product;
    +------------+----------+
    | product_id | model    |
    +------------+----------+
    |         54 | product1 | 
    |         53 | product1 | 
    |         55 | product1 | 
    |         56 | product2 | 
    |         57 | product2 | 
    |         58 | product2 | 
    +------------+----------+
    6 rows in set (0.00 sec)
    
    mysql> select * from vendor;
    +--------+-------------+
    | vendor | vproduct_id |
    +--------+-------------+
    |      6 |          54 | 
    |      7 |          53 | 
    |      6 |          57 | 
    |      7 |          58 | 
    |      8 |          55 | 
    |      8 |          56 | 
    +--------+-------------+
    6 rows in set (0.00 sec)
    Code:
    mysql> SELECT v.vproduct_id
        ->   FROM product p
        ->      INNER JOIN vendor v
        ->         ON p.product_id = v.vproduct_id
        ->                              AND
        ->              v.vendor = 7
        ->   WHERE p.model = 'product2';
    +-------------+
    | vproduct_id |
    +-------------+
    |          58 | 
    +-------------+
    1 row in set (0.00 sec)
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,601
    Rep Power
    595
    Using real data, I get this:
    Code:
    SELECT v.vproduct_id FROM product p INNER JOIN vendor v ON p.product_id=54 AND v.vendor=4 WHERE p.model='igol';
    +-------------+
    | vproduct_id |
    +-------------+
    |          52 |
    |          54 |
    +-------------+
    2 rows in set (0.00 sec)
    Code:
    select product_id,model from product;
    +------------+--------+
    | product_id | model  |
    +------------+--------+
    |         52 | ego900 |
    |         53 | ego900 |
    |         54 | igol   |
    |         55 | ego900 |
    |         56 | igol   |
    |         51 | igol   |
    +------------+--------+
    6 rows in set (0.00 sec)
    Code:
    select vproduct_id,vendor from vendor;
    +-------------+--------+
    | vproduct_id | vendor |
    +-------------+--------+
    |          51 |      5 |
    |          52 |      4 |
    |          53 |      5 |
    |          54 |      4 |
    |          55 |      6 |
    |          56 |      6 |
    +-------------+--------+
    6 rows in set (0.00 sec)
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    That's because you have changed the ON condition changing the link for product_id from being a link between the tables to a literal value.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,601
    Rep Power
    595
    Arrrghhh! How stupid was that? Its fixed. Thanks.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    Originally Posted by gw1500se
    Arrrghhh! How stupid was that? Its fixed. Thanks.
    Glad it got sorted
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo