sql querie

[Chris]

Hi there,

I have the following Database:
create table big_spenders (cust_id int primary key)
create table customers (cust_id int primary key, age int, cust_name
varchar(10))
create table orders (ord_id int primary key, cust_id int, date_ordered
datetime)
create table accounts (cust_id int primary key, account_balance int)

create index idx_cust_id on orders(cust_id)

insert into customers values(1,20,'Sam S')
insert into customers values(2,20,'Tom, P')
insert into customers values(3,15,'tom S')
insert into customers values(4,17,'Sam P')

insert into big_spenders values (1)

insert into orders values (1,1, getdate())
insert into orders values (2,2, getdate())
insert into orders values (3,1, '1 jan 2000')
insert into orders values (4,1, '30 dec 2000')

insert into accounts values (1, 2167)
insert into accounts values (2, 100)
insert into accounts values (3, 778)
insert into accounts values (4, 175)

Now I try to select all orders placed by customers with the first name of
tom

This does not really work:

select * from orders where (orders.cust_id = customers.cust_id) AND
(customers.cust_name = 'tom')

This does not give me the proper result. What am I doing wrong? Thanks for
your help

How would the sql statement look in T-SQL?

 

[Chris]

I thing I've got it:

SELECT orders.ord_id, orders.date_ordered

FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id

WHERE ((customers.cust_name)='tom');

But how would it look like in T-SQL

Cheers

Chris

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Shud be:

SELECT orders.ord_id, orders.date_ordered
FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id
WHERE customers.cust_name LIKE 'tom*'

Not the use of LIKE and the wildcard character * (asterisk). In T-SQL
the wildcard char is % (percent sign).

Wildcard chars:

JET SQL (access) T-SQL
================ =====
1 or more chars * %
only 1 char ? _

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgr1O4echKqOuFEgEQKc8gCg9Hy6dHEyQG3E222Aq/+xdOEKO0IAoNfY
Zs037AFndVBiRjfclMsn9wqE
=OymD
-----END PGP SIGNATURE-----