query help

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 363,435 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,179 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Oracle advertiser:

Thread Tools Display Modes

Views: 343 | Replies: 3

•

Join Date: Nov 2007

Posts: 84

Reputation: k2k is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

k2k

Offline

Junior Poster in Training

query help

Apr 13th, 2008

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 ?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
 
--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));  
--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));  

Last edited by peter_budo : Apr 17th, 2008 at 1:49 pm. Reason: Correcting [code = sql] tag, there are no spaces for [code=sql]

•

Join Date: Feb 2007

Location: Bangalore,India

Posts: 1,134

Reputation: debasisdas is an unknown quantity at this point

Rep Power: 4

Solved Threads: 68

debasisdas debasisdas is offline

Offline

Veteran Poster

Re: query help

Apr 14th, 2008

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

Share your Knowledge.

•

Join Date: Nov 2007

Posts: 84

Reputation: k2k is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

k2k

Offline

Junior Poster in Training

Re: query help

Apr 14th, 2008

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

Help with Code Tags

(Toggle Plain Text)

--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');

--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');

•

Join Date: Nov 2007

Posts: 51

Reputation: Nige Ridd is an unknown quantity at this point

Rep Power: 1

Solved Threads: 8

Nige Ridd

Offline

Junior Poster in Training

Re: query help

Apr 22nd, 2008

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