Hi,
First, sorry for the long post
I new to SQL, MYSQL specifically.
I'm having a problem with Join Statements.
mysql> desc toy_info;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| toy_id | int(11) | NO | PRI | NULL | auto_increment |
|
| toy | varchar(40) | YES | | NULL | |
| color | varchar(40) | YES | | NULL | |
| cost | decimal(5,2) | YES | | NULL | |
| weight | decimal(5,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+------------
mysql> desc store_info;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| store_id | int(11) | NO | PRI | NULL | auto_increment |
| address | varchar(40) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| manager | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-----------
mysql> desc store_inventory;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| toy_id | int(11) | NO | PRI | NULL | |
| store_id | int(11) | NO | PRI | NULL | |
| inventory | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
I want to be able to get specific info like the store that carries the white ball or the price in a certain range or
how many certain toy is available at the store.
Or even to pull up the whole record.
I tried this:
select toy_info.toy, toy_info.color, toy_info.cost, store_info.address, store_info.manager,
store_inventory.inventory
FROM toy_info, store_info
INNER JOIN
store_inventory
ON toy_info.toy_id=store_inventory.toy_id
;
I get Error Code: 1054. Unknown column 'toy_info.toy_id' in 'on clause'
If I replace the ON with where, I get a Cartesian result.
For the life of me I can't get a grasp on join statements.
From what I think I understand,
I have 2 lists, toys and stores.
I want to make an inventory database.
So I create the 2 tables.
This would make a many-many relationship.
So I created the Junction Table (store_inventory)
with primary keys to link the information together
like a certain color toy is sold at a certain store, right?
The inventory amount column is placed in the Junction table.
So I use the SELECT on the columns I want the information on.
Then use the FROM to pick the table(s) I'm getting the information from.
Here's where I get lost.
I just ran this just to see what the result would be:
select toy_info.toy, toy_info.color, toy_info.cost, store_info.address, store_info.manager
FROM toy_info, store_info
where toy_info.color= 'white'
;
And again I'm getting a Cartesian result with toys that are white. Not what I expected.
Is it because I'm asking for info in two tables?
I ran this next:
select *
FROM toy_info
where toy_info.color= 'white'
;
And just got the toy info I request. (2 rows different toys)
What I expected.
Any links explaining joins would be appreciated as well
Thanks in advance
