[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.
Kent
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