homepage Welcome to WebmasterWorld Guest from 54.197.147.90
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Help with Join Statement regarding 3 Tables
nobodyspecial




msg:4376753
 8:41 pm on Oct 19, 2011 (gmt 0)

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

 

nobodyspecial




msg:4376874
 1:31 am on Oct 20, 2011 (gmt 0)

Scratch the above.

When I do a Join, do I Join toy_info to store_inventory or to store_info?

tcsoftware




msg:4377102
 3:23 pm on Oct 20, 2011 (gmt 0)

SELECT * FROM toy_info, store_info, store_inventory WHERE store_inventory.toy_id=toy_info.toy_id AND store_info.store_id=store_inventory.store_id

nobodyspecial




msg:4377339
 10:26 pm on Oct 20, 2011 (gmt 0)

Thanks tcsoftware!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved