CODEWITHSUNDEEP
mysqldatabasemany-to-many
Nicholas Gerrer
Nicholas Gerrer

Reputation: 43

MYSQL Pulling multiple values from multiple tables

Forgive me my title is poorly chosen, I'm not sure what to label this question but believe I can explain it.

I have a many to many relationship between a products table and a stores table.

table name: products
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(200)     | NO   |     | NULL    |                |
| sku   | varchar(10)      | NO   |     | NULL    |                |
| units | int(5)           | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

table name: stores
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| number   | int(5)           | NO   |     | NULL    |                |
| location | varchar(50)      | NO   |     | NULL    |                |
| phone    | varchar(10)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+


table name: products_stores
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| pID   | int(10) unsigned | NO   | PRI | NULL    |       |
| sID   | int(10) unsigned | NO   | PRI | NULL    |       |
+-------+------------------+------+-----+---------+-------+

I am running a query to display all products available at the store where its ID = 1

SELECT p.name, p.sku 
FROM products p 
  INNER JOIN products_stores ps
    ON p.id = ps.pID 
WHERE ps.sID = 1;

I have no problem with this query and get the results I desire. However, in my display results I would also like to show the store location value for each result row. I thought that I would be able to just include it comma separated in my FROM statement such as:

SELECT p.name, p.sku, s.location
FROM products p, stores s
  INNER JOIN products_stores ps
    ON p.id = ps.pID
WHERE ps.sID = 1;

However this does not work. I'm sure the answer is very simple and I will continue to research for a solution. I figured I would throw this up in hopes that somebody might be able lead me in the right direction.

Thanks.

Upvotes: 1

Views: 66

Answers (2)

Mihai
Mihai

Reputation: 26784

Use explicit JOINs,implict JOIN are deprecated(and less clear)

SELECT p.name, p.sku ,s.location
FROM products p 
  INNER JOIN products_stores ps
    ON p.id = ps.pID 
INNER JOIN stores s
ON s.ID=ps.sID
WHERE ps.sID = 1;

Upvotes: 1

Zahid Ali
Zahid Ali

Reputation: 466

SELECT 
  p.name,
  p.sku,
  s.location 
FROM
  products p,
  stores s,
  products_stores ps 
WHERE p.id = ps.pID 
  AND s.sID = ps.sID 
  AND ps.sID = 1 ;

Upvotes: 1

Related Questions