CODEWITHSUNDEEP
mysql
Newton
Newton

Reputation: 47

mySQL: Get data from two tables relationed by an ID

I need your help. I have two tables: products and presentations with the next structure and data:

products:
id | name | description | price
5  | iPad | description | 950.00   

presentations:
id_product | name  | price
5          | White | 955.00
5          | Black | 945.00

I need to get a product by id from the 'products' table and all records related to it from 'presentations' table. I want the next result in a html table or divs:

Product name: Ipad
Description: This is the description based on the text contained if the field of the table

Please, select the ipad you want:        
  (*) Normal - price:  $950.00
  ( ) White  - price:  $955.00
  ( ) Black  - price:  $945.00

How can I do it? Thanks to all!

Upvotes: 0

Views: 50

Answers (4)

Carlos
Carlos

Reputation: 2923

If I'm not understanding you wrong, I think that what you need is a JOIN between the two tables. Something like

SELECT prod.name AS product_name, description, pres.name AS presentation_name, pres.price AS price 
FROM products AS prod INNER JOIN presentations AS pres 
ON (prod.id = pres.id_product)`

Will return results like:

| product_name | description | presentation_name | price |

| iPad | description | White | 955.00 |

And so on. Is that what you wanted?

Upvotes: 0

ravikumar
ravikumar

Reputation: 893

Use INNER JOIN,

SELECT p.name,p.description,p.price,pr.name  AS presentationName,pr.price AS 
presentationPrice
FROM product p INNER JOIN presentations pr ON p.id=pr.id_product WHERE p.id='5'

Upvotes: 0

Jeff
Jeff

Reputation: 167

SELECT 'Normal' AS name, price from products
  WHERE id = 5
UNION
SELECT name, price FROM presentations
  WHERE id_product = 5

Upvotes: 4

said
said

Reputation: 553

use the request 

select name from products where id=5 and  id in(select id_product from presentations);

or 

select name from products P, presentations Pr whre P.id=Pr.id_product and P.id=5;

Upvotes: 0

Related Questions