Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I relate tables in SQL

Posted on 2004-09-05
6
Medium Priority
?
265 Views
Last Modified: 2013-11-23
Problem: I have a table that contains user information such as name, phone, username, address, etc.  I would like for a user to add multiple "books" to their account.  I could do this all on one table, but I want them to be able to add as many books as they would like.  I am sure there is some way for them to have subtables of books that would have fields such as title, autor, print date, etc.  I can make both tables separatly, but how do I combine the book tables with the correct user table.  The answer/solution to this question will need to have some "high-level" conceptual information as well as some code.

Thank you EXPERTS,

-TH
0
Comment
Question by:travishaberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 11985351
Greetings travishaberman!

Table relationships can be established in two ways
  Through Joins like

     SELECT Table1.*, Table2.* LEFT JOIN Table1.PrimaryKey=Tables2.ForeignKey

or where natural relationships don't exist...

     SELECT Table1.*, Table2.* WHERE (Table1.PrimaryKey=Tables2.ForeignKey)

either way accomplishes the same goal,... one will be more correct than the other under various circumstances.

regards
:)-j-
0
 
LVL 18

Expert Comment

by:JR2003
ID: 11985356
You would normally have 3 tables for this type of relationship: Users, Books and UserBooks

Table Users contains information about all the users
Table Books contains information about all the books available
Table UserBooks contains information about the books users have and relates the Users and book table together.

To add a book to the user account you just add a record to the UserBooks table.


Table Users
========
Username
UserId <- This is a unique key
name
address
etc...


Table Books
========
BookId
Title
Author
etc...

Table  UserBooks
===========
UserId
BookId


To select information about what books a particular user has you would do a query like this:

select U.UserName, B.Title
from Users U, Books B, UserBooks UB
where U.UserId = UB.UserId
   and UB.BookId = B.BookId
   and U.UserId = 123

To select information about what users have a particular book would do a query like this:

select U.UserName, B.Title
from Users U, Books B, UserBooks UB
where U.UserId = UB.UserId
   and UB.BookId = B.BookId
   and B.BookId = 'XYZ'

0
 
LVL 32

Expert Comment

by:jadedata
ID: 11985357
your link between your tables will be some kind of id for the person "owning" these books.  
Whether you need a junction table between the two is a matter of design meeting the business rules of the process.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:JR2003
ID: 11985506
jadedata,
Sure, it depends on the business rules. I was assuming that there would be a reference table of books or that there it would certainly be desirable to have one. As you know this is the standard way to design databases particulariy if the book details are not freeform text or there is the possiblility of a many to many relationship of users to books.

travishaberman,
If you decide to have a Books table and a UserBooks table then you can also add attributes of the UserBook to the UserBook table for example you could add a DatePurchased field to it.

Table  UserBooks
===========
UserId
BookId
DatePurchased
0
 
LVL 1

Accepted Solution

by:
pharaon earned 2000 total points
ID: 11991002
I have done a similar thing in MYSQL. whe you need to do is have one unique pice of info that connects one table to the other

you can have table1 for the user information. for example :
----Table1------
user_name
user_phone
account_number
 
and table2 for the books accounts like this:

----Table2------
account_number
Book_code
Date_borrowd


notice that account_number is duplicated between the 2 tables so it is the key the relate them to each other
now to search for all books under some user (lets say user with account number = 5151):

SELECT Table2.Book_code from Table1, Table2 where Table1.account_number = "5151" and Table2.account_number = "5151";

Good Luck
Pharaon
0
 

Author Comment

by:travishaberman
ID: 12000250
That is perfect! thank you,

-TH
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Progress
Suggested Courses

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question