Query help please

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

April 16th, 2008, 01:21 PM

Trogan

Registered User

Join Date: Apr 2008

Posts: 9

Time spent in forums: 1 h 53 m 38 sec
Reputation Power: 0

Query help please

Hi,

I have 6 invoices. From these invoices, I need to find out who has bought any book under the 'ORACLE' title.

I have tried the following...

Quote:

select name, title
from customer, item
where title like '%ORACLE%';

...which gives the following result, that is wrong...

Quote:

CUSTOMER.............................................TITLE
BARMESTON YOUTH INSTITUTE..........WORKING WITH ORACLE
RATHBOURNE SCHOOL...................... WORKING WITH ORACLE
BROMLET LEISURE CENTRE................ WORKING WITH ORACLE
TOOTING PRIMARY SCHOOL...............WORKING WITH ORACLE
BARMESTON YOUTH INSTITUTE..........LEARN ORACLE IN 90 DAYS
RATHBOURNE SCHOOL......................LEARN ORACLE IN 90 DAYS
BROMLET LEISURE CENTRE................LEARN ORACLE IN 90 DAYS
TOOTING PRIMARY SCHOOL...............LEARN ORACLE IN 90 DAYS

Not every customer has bought those books.

Can someone guide on how to solve this problem?

Let me know if additional info is needed.

Thanks!

April 16th, 2008, 01:56 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 294

Time spent in forums: 2 Days 16 h 48 m 15 sec
Reputation Power: 9

What you have in your query is called a "cartesian product", where each and all customers have each and all items selected.

You need to qualify the condition of the join with selecting only the books purchased by each customer, kinda like this:

Code:

select name, title
  from customer, item
 where item.cust_id = customer.cust_id
   and title like '%ORACLE%';

PS: If the item table does not contain the customer number (not an invoice) then you need to join a third table that would tie cutomers with invoices with items.

__________________

Last edited by LKBrwn_DBA : April 16th, 2008 at 02:00 PM.

April 16th, 2008, 02:18 PM

Trogan

Registered User

Join Date: Apr 2008

Posts: 9

Time spent in forums: 1 h 53 m 38 sec
Reputation Power: 0

Hi LKBrwn,

First, thanks for your willingness to help.

I have customer_id in only two tables: customer and invoice.

What do you suggest should go in the WHERE condition?

Thanks again!

April 16th, 2008, 02:32 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 294

Time spent in forums: 2 Days 16 h 48 m 15 sec
Reputation Power: 9

You have to join the three tables:

From Customer to Invoice on Customer Id, from Invoice to Item on Item Id.

Code:

Select Name, Title
  From Customer, Invoice, Item
 Where Invoice.Customer_Id = Customer.Customer_Id
   And Item.Item_Id = Invoice.Item_Id
   And Item.Title Like '%Oracle%';

April 16th, 2008, 03:16 PM

Trogan

Registered User

Join Date: Apr 2008

Posts: 9

Time spent in forums: 1 h 53 m 38 sec
Reputation Power: 0

Hi,
Thanks for the continued help.

I tried the following, but I get "no data found".

Select Name, Title
From Customer, Invoice, Item
Where Invoice.Customer_no = Customer.Customer_no
And Item.title = Invoice.customer_no
And Item.Title Like '%ORACLE%';

I see from your example above, you have Item_id which I do not have. These are the 3 of 5 tables I have...

SQL> desc invoice;
Name--------------------Null?---------------Type
INVOICE_NO-----------NOT NULL--------CHAR(1)
INVOICE_DATE--------NOT NULL --------DATE
SALESMAN_ID --------------------------CHAR(8)
CUSTOMER_NO -------NOT NULL---------CHAR(6)
PAYMENT_TYPE-------NOT NULL --------VARCHAR2(6)

SQL> desc customer;
Name ------------------- Null? ---------Type
CUSTOMER_NO-----------NOT NULL-----CHAR(6)
NAME--------------------NOT NULL-----VARCHAR2(30)
ADDRESS-----------------NOT NULL ----VARCHAR2(60)
CREDIT_LIMIT---------------------------NUMBER(6,2)

SQL> desc item;
Name-------------Null?------------Type
ISBN------------- NOT NULL ------CHAR(13)
TITLE-------------NOT NULL ------VARCHAR2(30)
PRICE-------------NOT NULL-------NUMBER(4,2)

I don't know if this helps.

Do you have any other suggestions?

Thanks again!

April 16th, 2008, 03:56 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 294

Time spent in forums: 2 Days 16 h 48 m 15 sec
Reputation Power: 9

Quote:

Originally Posted by Trogan

...
Do you have any other suggestions?

Yes, there is no column that connects invoice with item, you may need to use another table (maybe invoice_detail?)

PS: It's allways a good idea to post all the table definitions up front!

Last edited by LKBrwn_DBA : April 16th, 2008 at 04:07 PM.

April 16th, 2008, 04:08 PM

Trogan

Registered User

Join Date: Apr 2008

Posts: 9

Time spent in forums: 1 h 53 m 38 sec
Reputation Power: 0

I have a delivery table that uses a composite key which links to both invoice and item tables. It has Invoice_no and ISBN for columns.

Could this be used?

April 16th, 2008, 08:35 PM

vpire

Contributing User

Join Date: Sep 2005

Posts: 150

Time spent in forums: 1 Day 6 h 56 m 44 sec
Reputation Power: 6

Quote:

Originally Posted by Trogan

I have a delivery table that uses a composite key which links to both invoice and item tables. It has Invoice_no and ISBN for columns.

Could this be used?

Is this what you are looking for?

Code:

SELECT C.NAME, I.TITLE
FROM CUSTOMER C, INVOICE IN, ITEM I, DELIVERY D
WHERE C.CUSTOMER_NO = IN.CUSTOMER_NO AND IN.INVOICE_NO = D.INVOICE_NO AND D.ISBN = I.ISBN AND (I.TITLE LIKE 'ORACLE%' OR I.TITLE LIKE '%ORACLE%' OR I.TITLE LIKE '%ORACLE')

April 17th, 2008, 03:32 AM

Trogan

Registered User

Join Date: Apr 2008

Posts: 9

Time spent in forums: 1 h 53 m 38 sec
Reputation Power: 0

That worked! Thank you vpire...that is much appreciated.

Thank you too LKBrwn!

#10

April 17th, 2008, 03:57 AM

Trogan

Registered User

Join Date: Apr 2008

Posts: 9

Time spent in forums: 1 h 53 m 38 sec
Reputation Power: 0

Can I ask one more question please.

I need to calculate the grand total for each invoice (separately) I have (6 in total).

I have tried...

Quote:

select item.title, sum(nos)
from item, delivery
where item.ISBN = item.ISBN
group by item.title;

...however, it gives me the following that is wrong.

Quote:

THE UNIFIED MODELLING LANGUAGE ----23
INTERNET AND WORLD WIDE WEB-------23
SOFTWARE ENGINEERING---------------23
WORKING WITH ORACLE----------------23
LEARN ORACLE IN 90 DAYS-------------23
SYSTEMS ANALYSIS AND DESIGN-------23

I'm probably way off the mark with this.

Any suggestions or advise on this please?

Thanks again!

#11

April 17th, 2008, 04:26 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,710

Time spent in forums: 2 Months 2 Weeks 6 Days 4 h 21 m 49 sec
Reputation Power: 259

Read carefully

Quote:

from item, delivery
where item.ISBN = item.ISBN

__________________
My blog about OpenSource Databases
PDF tutorials about OSS databases, DBMonster ...
Please contribute to Open Source Development, fill bug reports!!!
Developer Shed eSupport
Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: