query help

[k2k]

from last thread, I figured it out my tables relationship and they do work i believe. however, i can't successfully get elements from each of the table out. if i do:

select b_customer_name, h_customer_firstname, h_customer_lastname, telephone
from customers c, business_customers b, home_customers h
where c.customer_id = b.b_customer_id and
c.customer_id = h.h_customer_id;

i have no row selected.... what is wrong ?

--customers
drop table Customers cascade constraint;
create table Customers(
Customer_id Number(5) primary key not null,
Credit varchar2(20),
Street varchar2(20),
City varchar2(12),
State varchar2(2),
Zip varchar2(5),
Telephone varchar2(14));

--business_customers
drop table business_customers cascade constraint;
create table business_customers(
b_customer_id number(5) primary key not null,
b_customer_name varchar2(20), 
FOREIGN KEY (b_customer_id) references Customers(customer_id));


--home_customers
drop table home_customers cascade constraint;
create table home_customers(
h_customer_id number(5) not null,
h_customer_lastname varchar(10),
h_customer_firstname varchar(10),
PRIMARY KEY (h_customer_id),
FOREIGN KEY (h_customer_id) references Customers(customer_id));

[debasisdas]

Are you getting any error from the query.
Are you sure related data exists in the tables ?

[k2k]

below is the whole code.... I think i have 3 related tables... how do i select data from each of them?

--customers
drop table Customers cascade constraint;
create table Customers(
Customer_id Number(5) primary key not null,
Credit varchar2(20),
Street varchar2(20),
City varchar2(12),
State varchar2(2),
Zip varchar2(5),
Telephone varchar2(14));

insert into customers values(1,'3245-3242-6456-9584', '12 Reed St', 'Boston', 'MA','02140','617-945-3893');
insert into customers values(2,'1235-5664-3244-3241','132 porter st', 'Cambridge', 'MA', '02183','324-324-3421');
insert into customers values(3,'n/a','1908 Mass Ave', 'Cambridge', 'MA', '02140', '617-324-5342');
insert into customers values(4, 'n/a', '22 Jackson St', 'Boston', 'MA', '02165', '617-324-3421');
insert into customers values(5, '1324-3241-7657-3452', '355 Hancock st', 'Quincy', 'MA', '02169', '617-324-6543');
insert into customers values(6, '1321-5756-4534-3234', '166 Orchar st','Natick', 'MA', '02876', '781-234-3241');
insert into customers values(7, '8324-4211-4352-9876', '155 Broadway', 'Boston', 'MA', '02140', '617-324-2134');
insert into customers values(8, 'n/a', '133 Main st','Randolph', 'MA', '02368', '716-324-9283');
insert into customers values(9, '3524-3242-2134-5345', '23 tanny st', 'Boston', 'MA', '02158', '617-342-2345');
insert into customers values(10, '9324-6345-2134-9843', '11 Washington st', 'Cambridge', 'MA', '02140', '617-987-2346');


--business_customer
drop table business_customers cascade constraint;
create table business_customers(
b_customer_id number(5) primary key not null,
b_customer_name varchar2(20), 
FOREIGN KEY (b_customer_id) references Customers(customer_id));

insert into business_customers values(1, 'Action inc');
insert into business_customers values(2, 'ComTech inc');
insert into business_customers values(5, 'Boston City');
insert into business_customers values(6, 'Cambridge Consult');
insert into business_customers values(7, 'Boston Ele Inc');

--home_customers
drop table home_customers cascade constraint;
create table home_customers(
h_customer_id number(5) not null,
h_customer_lastname varchar(10),
h_customer_firstname varchar(10),
PRIMARY KEY (h_customer_id),
FOREIGN KEY (h_customer_id) references Customers(customer_id));

insert into home_customers values(3, 'li', 'henry');
insert into home_customers values(4, 'Gerald', 'Gannon');
insert into home_customers values(8, 'Kim', 'Thomas');
insert into home_customers values(9, 'Gilmore', 'Phil');
insert into home_customers values(10, 'Costello', 'Sunny');

[Nige Ridd]

The reason why your not getting any data back is that your query is effectively looking for customers who are both business and home customers at the same time.
You'll have to use outer joins to allow you to retrieve one or the other.
Nigel