Insert referring index

__berfuzz

Looking for a way to minimize a table I'm trying to use index instead of strings in a table. However, my limited knowledge of sql - querys stops me from sorting it out.

Example:
table - deal
id | product_id | time

table - product
product_id | product

If its possible.
1. How do I INSERT rows into the table - deal while referring to table - product?
2. How do I SELECT rows from table - deal so I get the string from table - product instead of the index from table - deal?

If my Idea is all bad, is there better way to do it?

laserlight

1. How do I INSERT rows into the table - deal while referring to table - product?

You just need to know the prodyct_id value. If you only have the product name, then run a query for it (a subquery should work too).

2. How do I SELECT rows from table - deal so I get the string from table - product instead of the index from table - deal?

Join the deal and product tables on the product_id.

__berfuzz

Thx for speedy answer!!!

laserlight wrote:
You just need to know the prodyct_id value. If you only have the product name, then run a query for it (a subquery should work too).

I'm not familiar with the term subquery, Is that all_in_one query or is it some sort of attachment to the query?

laserlight wrote:
Join the deal and product tables on the product_id.

Trying to muster up something, would that be something like this? My comments are guesses of what a join query should hold after reading up, a bit.

SELECT *   //selecting all
FROM deal  // from table deal
JOIN product  //joining table product
ON deal.product_id=product.product_id  //where the reference should be made

I take it that my first idea wasn't that far from target. Is this a good way to minimize databases, is their better ways?

__berfuzz

Selecting rows seems to work now. My query, after Ockhams razor blade.

SELECT *
FROM deal d, product p
WHERE
d.product_id = p.produkt_id

I still haven't figure out how to insert data into the tables!

__berfuzz

I have this query and like to ask some questions.

$query = "
SELECT
 	pay,
	client,
	product,
	SUM(pay) AS tot_pay,
	COUNT(product) AS count_product

FROM
	product p,
	result r

WHERE
	r.product_id = p.product_id
	AND control = 1
	AND waste_id IS NULL

GROUP BY product
ORDER BY client";

I have a table result_extra that holds things that can be added to product. If product_id in result_extra is the same as ID(int auto) in product. I want to use that row in result_extra to get strings out of table-extra. So if result.ID = result_extra.result_id I what to fetch columns from extra. Should I get another query to do the extra part, or can it be done in a tidy all-in-one-way?

result
ID(int auto) | product(int) | time | etc

result_extra
result_id(int) | extra_id(int)

extra
extra_id(int) | extra(string) | price(int) | etc

Is it possible to make a understandable table where this all-in-one data in shown? I haven't been able to do that, but that could be my limited sql skill stopping me.