...

View Full Version : Joining database tables



rafiki
03-27-2007, 02:11 PM
ok im trying to normalise my data in my database
http://i143.photobucket.com/albums/r159/rafiki_1987/userstable.jpg
http://i143.photobucket.com/albums/r159/rafiki_1987/itemstable.jpg
http://i143.photobucket.com/albums/r159/rafiki_1987/itemsownedtable.jpg
there are 3 images i made of what my tables will kind of need to look like,
how would i go about making a statement to find out a) the admins items listed
b) how many items if any owned?

guelphdad
03-27-2007, 05:08 PM
Your third table SHOULD NOT include the third or fourth columns at all. INAME and IDESCRIPTION do not belong in that table, they are found in the second table.

rafiki
03-27-2007, 05:09 PM
ok so i took those two fields out of the equation, but how would i join them in a select statement to find out what items the admin owns, and how many?

rafiki
03-28-2007, 02:34 PM
SELECT u.uid, i.description, o.iid
FROM users as u
JOIN items as i
JOIN owneditems as o
ON u.uid = o.uid
ive modified some code from previous thread to the table/field names on my new tableand added a JOIN as i need to connect three tables right? is that how

guelphdad
03-28-2007, 04:38 PM
you need to join users and items on a column from each of those tables. a three table join you need something like this:



select
foo,
bar,
qux
from
table A
INNER JOIN table B
ON tableA.column = tableB.column
INNER JOIN table C
ON tableA.column = tableC.column


note that last line could join tableB.column = tableC.column instead.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum