[Tutor] DB design

Kent Johnson kent37 at tds.net
Tue Feb 15 12:02:59 CET 2005

I don't think you can do exactly that. But SQL does have powerful capabilities to do selects on 
multiple tables at once. It's called a 'join' and it is very common.

For examples suppose you have a customer database with a Customer table:
cust_id cust_name
111     Liam Clarke
222     Kent Johnson

and a Customer_Order table (way oversimplified):
cust_id  order_date
111      2004-4-22
111      2004-5-6
222      2004-1-31
111      2005-2-1

To show all customer orders with the customer id and name, you would join these two tables on the 
cust_id column:

select c.cust_id, c.cust_name, co.order_date
   from Customer c, Customer_Order co
   where c.cust_id = co.cust_id

To get just Liam's orders, add another clause to the above:
   and c.cust_id = '111'

Anyway, why don't you tell us more about what you are trying to do and we can give better suggestions.


Liam Clarke wrote:
> Hi, 
> Working my way through the basics of SQL, and I can see that it's very
> powerful for searching by different criteria.
> I'm already hitting my conceptual troubles however, as I'm visualising
> each table as a 'card'.
> Always my problems, too much imagination. But yeah, it seems very 1
> dimensional. But what I was wondering was is it possible within the
> bounds of SQL to contain a SQL query reference as a value, so that
> retrieving a certain value retrieves the results of that query i.e.
> table foo
> a b c
> 1  2 select d from bar
> table bar
> d e f
> 3 4 5 
> and select c from foo retrieves d from bar? I know I'm leapfrogging
> way ahead of myself, and probably confusing myself unnecessarily, but
> I'm just trying to make my cards more 3D in terms of linking data....
> Any assistance for my vague question gratefully appreciated.
> Regards, 
> Liam Clarke

More information about the Tutor mailing list