Joins And Insert Queries

[goingcrazy]

a bit confused with how to properly use Joins. This may not even be possible.

 

my database structure

table_name(column_1, column2, column3), underlined is primary key, *foreign key

 

stores(store_id, store_name)

products(product_id, *store_id, product_number, product_name)

values(value_id, *product_id, value, datetime)

 

Question: Can I write an INSERT query for the values table knowing the following: value, datetime, store_id, product_number?

Or do I first have to do a select query to grab the correct product_id from the products table?

 

NOTE: I didn't have these questions when I had a sloppy database design, lol, I'm trying.

 

Thanks for the help. Barrett

[Larry]

I'm not exactly sure I understand the relationship among the tables, but... Yes, you can write an INSERT query for the values table as long as you know the product_id, value, and datetime. You don't need to do a SELECT query to grab those values unless you don't have them. Not sure how this is a JOIN issue, though.

[Matthaus]

Edit: Larry beat me to it. I also don't see how this is a join issue. Two tables are involved, but no join is needed for this task.

 

Short answer is maybe, but it depends on a how the tables are defined here. If product_number (or product_number and store_id combined do) uniquely identifies an item in the products table, then yes. It would look something like this...

INSERT INTO `values` VALUES (NULL, (SELECT product_id FROM products WHERE product_number = $prodNum AND store_id = $storeID), $value, '$datetime');
or
INSERT INTO `values` (SELECT NULL, product_id, $value, '$datetime' FROM products WHERE product_number = $prodNum AND store_id = $storeID);

Both of those should work. Hope that helps.

 

-matthaus

[goingcrazy]

Edit: Larry beat me to it. I also don't see how this is a join issue. Two tables are involved, but no join is needed for this task.

 

Short answer is maybe, but it depends on a how the tables are defined here. If product_number (or product_number and store_id combined do) uniquely identifies an item in the products table, then yes. It would look something like this...

INSERT INTO `values` VALUES (NULL, (SELECT product_id FROM products WHERE product_number = $prodNum AND store_id = $storeID), $value, '$datetime');
or
INSERT INTO `values` (SELECT NULL, product_id, $value, '$datetime' FROM products WHERE product_number = $prodNum AND store_id = $storeID);

Both of those should work. Hope that helps.

 

-matthaus

 

 

Table Relationship: stores have multiple products, and each product has a value that changes with time, every minute. The value is not a sale price, but a chemical composition value.

 

I think you have what I was looking for. To do the INSERT into the values table I need the product_id, but I don't have it unless I write a seperate SELECT query to get it (and I didn't want to do that, but yes, I could). The product_id is the primary key in the products table, and combined product_number and store_id are unique. Your INSERT query is a form I am not familiar with; an INSERT query with a little SELECT section in it?

 

Question: What does the NULL value represent, is that for if the SELECT query fails and does not return a product_id from products table, a NULL value gets inserted?

Question: I didn't find this in Larry's Book, PHP 6 and MYSQL 5, 3rd Edition, is there a web reference I could read?

 

Thanks, Barrett

[Larry]

ANSWER1: NULL means "no known value". It's a very special kind of thing. It's possible you'll see it on a SELECT with no product_id in a LEFT JOIN.

ANSWER2: A Web reference on what?

[goingcrazy]

ANSWER1: NULL means "no known value". It's a very special kind of thing. It's possible you'll see it on a SELECT with no product_id in a LEFT JOIN.

ANSWER2: A Web reference on what?

 

 

INSERT INTO `values` VALUES (NULL, (SELECT product_id FROM products WHERE product_number = $prodNum AND store_id = $storeID), $value, '$datetime');

 

ANSWER1:

I have a basic understanding of the NULL value, question is what is it doing in the above example query? Why not write the query like this:

INSERT INTO 'values' VALUES (SELECT product_id FROM products WHERE product_number = $prodNum AND store_id = $storeID), $value, '$datetime')

 

ANSWER2:

There are no examples of a query written like this in PHP 6 and MYSQL 5, 3rd edition, an INSERT with a SELECT section inside it? I was wondering where the information or direction came from on how to write it.

 

thanks, Barrett

[Matthaus]

Your INSERT query is a form I am not familiar with; an INSERT query with a little SELECT section in it?

Yes, exactly. Selects queries return rows (or sets of tuples) and insert statements insert/use rows (or sets of tuples), so you can use a select in an insert statement as long as both rows (or tuples) have the same number of columns. So in both the queries I posted, there are four columns in the insert statement. In the first, the select query returns one of the columns for the insert, and in the second query, the select is finding the product_id and returning three constants (value, null, and datetime) with that result. Both should produce the same row(s).

 

But the basic idea is that if a select query returns a row that has 4 columns of NULL, 12, 3.95, '3/16/2011', then it can be used in an insert (or other query), as the same thing as (NULL, 12, 3.95, '3/16/2011').

 

You can also do other fun things like copy a table with a select statement ["CREATE TABLE destinationTable (SELECT * FROM sourceTable)"]. Or if you just want the schema of a table ["CREATE TABLE destinationTable (SELECT * FROM sourceTable LIMIT 0)"]. Anyway, that's a little bit off track, but I hope it helps clear things up.

 

Question: What does the NULL value represent, is that for if the SELECT query fails and does not return a product_id from products table, a NULL value gets inserted?

Well in that case the reason I put it there was that I assumed value_id was an auto_increment column, in which case the insert would just use the next value for the column instead of the null. It would be as if you had written an explicit insert for the table, and passed 3 columns (value, product_id, and datetime) instead of all four columns.

 

In any case, hope that helps clear things up.

-matthaus

[Matthaus]

ANSWER1:

I have a basic understanding of the NULL value, question is what is it doing in the above example query? Why not write the query like this:

INSERT INTO 'values' VALUES (SELECT product_id FROM products WHERE product_number = $prodNum AND store_id = $storeID), $value, '$datetime')

If you try running the insert statement above it will fail claiming that the column count is wrong (MySQL will expect 4 columns, and you're only providing 3). You could do the above with a full insert, like...

INSERT INTO 'values' (product_id, value, datetime) VALUES (SELECT product_id FROM products WHERE  product_number = $prodNum AND store_id = $storeID), $value,  '$datetime')

But in the absence of an explicit column list, insert statements expect all columns to be present. That's why I threw the NULL in. It basically tells MySQL that "I know there should be a column here, but I don't have a value for it just now (or maybe there isn't a value for it), so just add the row anyway."

 

Sidenote: I added backticks (``), not apostrophe/single quote (') around values because it is a MySQL keyword, and otherwise may not be recognized as a table name. In general, it's a good idea to avoid naming columns, tables, views, or keys using reserved words/keywords (words like TABLE, SELECT, VALUES, INSERT, ...).

 

Hope that helps.

-matthaus

[goingcrazy]

thanks Matthaus, that makes sense. I've already changed the name of my table, and again reviewed mysql list of keywords to avoid.

 

thanks for the help.

 

Barrett

[HartleySan]

The analogy I saw once regarding what NULL is is imagine a CD. A CD with no data on it is an empty string, and no CD at all is NULL.

 

Anyway, what happens if a product exists in multiple stores? Maybe your example is just that, an example, but it doesn't seem like your database is normalized to begin with.

[goingcrazy]

The analogy I saw once regarding what NULL is is imagine a CD. A CD with no data on it is an empty string, and no CD at all is NULL.

 

Anyway, what happens if a product exists in multiple stores? Maybe your example is just that, an example, but it doesn't seem like your database is normalized to begin with.

 

a product_id can only exist in one store, store A can have product_number =1, and store B can have product_number = 1, but never the same product_id., thanks for the little cd analogy.

[HartleySan]

So product ID 1 in store A and product ID 1 in store B are actually different products? A bit confusing, if that's the case, but it's your database, so I'll trust ya.

[goingcrazy]

So product ID 1 in store A and product ID 1 in store B are actually different products? A bit confusing, if that's the case, but it's your database, so I'll trust ya.

 

nope, product_number 1 in store A and product_number 1 in Store B are different products, and they have different product_id's, I know its confusing, I'm a rookie poster and need to learn to be clearer

[HartleySan]

All right, I'll take your word for it. Sorry for getting the topic side-tracked anyway. Thanks for explaining that.