Home » SQL & PL/SQL » SQL & PL/SQL » Join tables (merge)
Join tables (merge) [message #235621] Mon, 07 May 2007 08:01 Go to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

I have two tables: BOOK and SUPPLIER

Book
ISBN (PK)
title
in_Stock
supplier_id (FK)

Supplier
id (PK)
name

This is what I want to do: show all books that have in_stock= 0 and order them by name (from supplier).

Any one?
Im really new to this, so plz be patient Embarassed
Re: joining two tables with conditions [message #235624 is a reply to message #235621] Mon, 07 May 2007 08:05 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Show us the SQL statement you tried first.
Re: joining two tables with conditions [message #235627 is a reply to message #235621] Mon, 07 May 2007 08:19 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
SELECT * FROM book b WHERE in_stock=0 UNION SELECT id, name FROM supplier s WHERE b.supplier_id=s.id;
Re: joining two tables with conditions [message #235630 is a reply to message #235627] Mon, 07 May 2007 08:37 Go to previous messageGo to next message
oyvind_fredstie
Messages: 2
Registered: May 2007
Location: Stavanger, Norway
Junior Member
You should use a JOIN instead of UNION:
SELECT s.name, b.ISBN, b.title, b.in_Stock as Stock
FROM book b INNER JOIN supplier s ON b.supplier_id = s.id
WHERE b.in_Stock = 0
ORDER BY s.name;


You use UNION when you want to combine the result of two (or more tables) vertically. To use UNION the selected attributes in the two tables must be identical. For example:
SELECT emp_id, firstname, surname, phone FROM employee
UNION 
SELECT const_id, firstname, lastname, phone FROM consultant;  

[Updated on: Mon, 07 May 2007 08:54]

Report message to a moderator

Re: joining two tables with conditions [message #235631 is a reply to message #235621] Mon, 07 May 2007 08:48 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
using this then:

SELECT b.isbn, b.title, b.in_stock, b.supplier_id
FROM book b
INNER JOIN supplier s
ON (b.supplier_id = s.id)
WHERE b.in_stock=0
ORDER BY s.name;

I want to show the s.name AND b.title.
How it is rigth now:
isbn, title, in_stock, supplier id
I want it to be:
isbn, NAME, title, in_stock, supplier_id

[Updated on: Mon, 07 May 2007 09:06]

Report message to a moderator

Re: joining two tables with conditions [message #235635 is a reply to message #235631] Mon, 07 May 2007 09:13 Go to previous messageGo to next message
oyvind_fredstie
Messages: 2
Registered: May 2007
Location: Stavanger, Norway
Junior Member
Easy, just add s.name:
SELECT b.isbn, s.name, b.title, b.in_stock, b.supplier_id
FROM book b
INNER JOIN supplier s ON (b.supplier_id = s.id)
WHERE b.in_stock=0
ORDER BY s.name;
Re: joining two tables with conditions [message #235638 is a reply to message #235621] Mon, 07 May 2007 09:19 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
That did not work, NAME is not present in table BOOK, but in SUPPLIER. I get a ORA-01730.

Any ideas?

[Updated on: Mon, 07 May 2007 09:24]

Report message to a moderator

Re: joining two tables with conditions [message #235643 is a reply to message #235638] Mon, 07 May 2007 09:45 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

did you get the error?
can you show us the sql

i did not get any error when i executed the above

create table
create table supplier (id number,name varchar2(25),constraint pk_iot_ primary key (id))

create table book(isbn number primary key,title varchar2(10),in_stock number,
supplier_id number references supplier(id))

insert data
insert into supplier values(1,'a');
insert into supplier values(2,'b');
insert into supplier values(3,'c');

insert into book values(10,'test',0,3);
insert into book values(20,'test1',0,3);
insert into book values(30,'test2',0,3);
insert into book values(40,'test3',0,3);


query and the output
SELECT b.isbn, s.name, b.title, b.in_stock, b.supplier_id
FROM book b
INNER JOIN supplier s ON (b.supplier_id = s.id)
WHERE b.in_stock=0
ORDER BY s.name;

ISBN NAME TITLE IN_STOCK SUPPLIER_ID 
10    a       test   0       1  
20    b       test1  0       2 
30    b       test1  0       2 
40    c       test1  0       3 




regards
shanth

Re: joining two tables with conditions [message #235650 is a reply to message #235621] Mon, 07 May 2007 10:21 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Im trying this code:

SELECT b.isbn, s.name, b.title, b.in_stock, b.supplier_id
FROM book b
INNER JOIN supplier s
ON (b.supplier_id = s.id)
WHERE b.in_stock=0
ORDER BY s.name

and I get the ORA-01730 - invalid number of column names specified

[Updated on: Mon, 07 May 2007 10:24]

Report message to a moderator

Re: joining two tables with conditions [message #235657 is a reply to message #235650] Mon, 07 May 2007 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where did you get that?
Copy and paste your screen here.

Regards
Michel
Re: joining two tables with conditions [message #235660 is a reply to message #235621] Mon, 07 May 2007 11:10 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
./fa/2414/0/
  • Attachment: error.JPG
    (Size: 73.75KB, Downloaded 628 times)

[Updated on: Mon, 07 May 2007 11:11]

Report message to a moderator

Re: joining two tables with conditions [message #235664 is a reply to message #235660] Mon, 07 May 2007 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why didn't you post the REAL problem.
You tried to create a view.
You gave it 4 columns.
Your query contains 5 columns.

Thus the error.

How can you hope an answer to your probem if you post another question?

Regards
Michel
Re: joining two tables with conditions [message #235667 is a reply to message #235621] Mon, 07 May 2007 11:23 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Sorry for that! Embarassed

Thanks for all your help! Thanks to mr Cadot I have fixed the problem...u learn somehing everyday...

[Updated on: Mon, 07 May 2007 11:26]

Report message to a moderator

Re: joining two tables with conditions [message #235670 is a reply to message #235667] Mon, 07 May 2007 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either add the name (bad name, this is a reserved word) to your view definition or remove it from your query.

Regards
Michel
Re: joining two tables with conditions [message #235936 is a reply to message #235621] Tue, 08 May 2007 08:06 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Instead of flooding the forum with new posts, I continue in this one. Still working with books...

Using this code in a VIEW:

SELECT MIN(price) AS lowest_price, MAX(price) AS highest_price, AVG(price) AS average, SUM(price) AS sum FROM book;

This code works just fine. But I want to add another function to it. I want to se the same thing as above, but for every category of a book. (type_id in BOOK (FK), id (PK) in type_of_book)

This code does the other thing I want:

SELECT MIN(b.price) AS lowest_price, MAX(b.price) AS highest_price, AVG(b.price) AS average, t.id, SUM(b.price) AS sum FROM book b JOIN type_of_book t ON b.type_id = t.id
GROUP BY t.id

But how do I use this two together?
Im creating a VIEW, by the way.

[Updated on: Tue, 08 May 2007 08:16]

Report message to a moderator

Re: joining two tables with conditions [message #235940 is a reply to message #235936] Tue, 08 May 2007 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean?
Quote:
how do I use this two together?

Regards
Michel
Re: joining two tables with conditions [message #235941 is a reply to message #235940] Tue, 08 May 2007 08:28 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
I want to show both the MIN, MAX, AVG and SUM for every CATEGORY of a book and the MIN, MAX, AVG and SUM for ALL the books. Is this possible in the same query?

[Updated on: Tue, 08 May 2007 08:29]

Report message to a moderator

Re: joining two tables with conditions [message #235944 is a reply to message #235941] Tue, 08 May 2007 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want the sum of all on each line or in one supplemental line?

Regards
Michel
Re: joining two tables with conditions [message #235950 is a reply to message #235944] Tue, 08 May 2007 08:44 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
One SUM for each line (for the category) and one for the total of all books.
Re: joining two tables with conditions [message #235958 is a reply to message #235950] Tue, 08 May 2007 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select decode(grouping(deptno),1,'Total',to_char(deptno)) deptno,
  2         sum(sal)
  3  from emp
  4  group by rollup(deptno);
DEPTNO                                     SUM(SAL)
---------------------------------------- ----------
10                                             8750
20                                             6775
30                                             9400
Total                                         24925

4 rows selected.

Regards
Michel
Re: joining two tables with conditions [message #235963 is a reply to message #235621] Tue, 08 May 2007 09:33 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Thats great, but I still want the MIN, MAX, AVG to show up. How do I do that?

[Updated on: Tue, 08 May 2007 09:37]

Report message to a moderator

Re: joining two tables with conditions [message #235967 is a reply to message #235963] Tue, 08 May 2007 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just add the other columns!
SQL> select decode(grouping(deptno),1,'Overall',to_char(deptno)) deptno,
  2          sum(sal), min(sal), max(sal), avg(sal)
  3  from emp
  4  group by rollup(deptno)
  5  /
DEPTNO    SUM(SAL)   MIN(SAL)   MAX(SAL)   AVG(SAL)
------- ---------- ---------- ---------- ----------
10            8750       1300       5000 2916.66667
20            6775        800       3000 2258.33333
30            9400        950       2850 1566.66667
Overall      24925        800       5000 2077.08333

4 rows selected.

Regards
Michel
Re: joining two tables with conditions [message #236025 is a reply to message #235621] Tue, 08 May 2007 13:36 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Thanks for your help! Do you have any good links for SQL to give me?
Re: joining two tables with conditions [message #236030 is a reply to message #236025] Tue, 08 May 2007 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I only know one: SQL Reference.

Regards
Michel
Re: joining two tables with conditions [message #236268 is a reply to message #235621] Wed, 09 May 2007 08:07 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Thanks for your help! Razz I will return here with some more questions in a while...
Re: joining two tables with conditions [message #236481 is a reply to message #235621] Thu, 10 May 2007 01:36 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Well, Im back! I will continue in this thread.
Im creating a view that shall do this:

Show what that have been bougth the last year, sorted after customer. Every customer should be represented with rows=book title (bougth 4 different books, 4 rows). If a customer have bougth more then ONE title, only a summarized line will be shown.

This is what I got so far:

SELECT c.name, i.payment_date
FROM customer c
JOIN invoice i
ON (c.id=i.customer_id)
WHERE i.payment_date > SYSDATE-365
ORDER BY c.name

The output:

NAME PAYMENT_DA
------------------------------ ----------
Bengt Larsson 2006-10-18
Bengt Larsson 2006-10-23
.
.
.

I think I have to add one more table. Item_of_invoice where its possible to se if a customer have bougth a book more then ones. (includes invoice_nr, item_id, quantity and isbn).

Does anybody have a clue? Feel free to ask for more information if you think something is missing, except my SQL-skill...
Re: joining two tables with conditions [message #236491 is a reply to message #236481] Thu, 10 May 2007 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Every customer should be represented with rows=book title (bougth 4 different books, 4 rows). If a customer have bougth more then ONE title, only a summarized line will be shown.


Are there 4 rows or 1 row (or 5 rows)?
If 1 (or 5) what should containt this (last) row?

Regards
Michel
Re: joining two tables with conditions [message #236560 is a reply to message #236491] Thu, 10 May 2007 04:44 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

This is how it should look like, never mind the swedish, you get the picture Laughing

./fa/2433/0/
  • Attachment: example.JPG
    (Size: 61.16KB, Downloaded 426 times)
Re: joining two tables with conditions [message #236568 is a reply to message #236560] Thu, 10 May 2007 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey, where is the "i.payment_date" column?
Do you want a date, each date, the last date, any date, no date? Smile
If it just a count well I think COUNT function will do it.

Regards
Michel
Re: joining two tables with conditions [message #236569 is a reply to message #235621] Thu, 10 May 2007 04:54 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
The output should look like the picture above. But only the books bougth in the last 365 days should appear.
Re: joining two tables with conditions [message #236576 is a reply to message #236569] Thu, 10 May 2007 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So just modify your first query and remove "i.payment_date" from select clause and add a count instead plus few other changes.

Regards
Michel
Re: joining two tables with conditions [message #236615 is a reply to message #235621] Thu, 10 May 2007 06:59 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Well, im confused.
Here are the involved tables:

BOOK: isbn (pk), title, in_stock, price, author_id (FK), type_id (FK), supplier_id(FK)

CUSTOMER: id (PK), name, adress, contact

ITEM_OF_INVOICE: invoice_nr (PK), item_id, quantity, isbn (FK from book)

INVOICE: invoice_nr (PK), invoice_date, payment_date, amount, customer_id(FK from customer)

So, i want to show the name of the customer (from table customer), the title (from book) and the quantity (from item_of_invoice). (se picture above)

I just dont get it!
How should I do this?

Is this totally wrong??

SELECT c.name, b.title, x.quantity
FROM customer c
JOIN invoice i, book b, item_of_invoice x
ON (c.id=i.customer_id) AND
(b.isbn = x.isbn)
WHERE i.payment_date > SYSDATE-365
Re: joining two tables with conditions [message #236628 is a reply to message #236615] Thu, 10 May 2007 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this totally wrong??

Not at all.
Modify the query to make it syntaxically valid and sum the quantity per name and title.

Regards
Michel
Re: joining two tables with conditions [message #236832 is a reply to message #236628] Fri, 11 May 2007 01:56 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Well, I have tried my code and I get a "missing keyword" on line 3. Any ideas?

SELECT c.name, b.title, x.quantity
FROM customer c
JOIN invoice i, book b, item_of_invoice x <---------------***
ON (c.id=i.customer_id) AND
(b.isbn = x.isbn)
WHERE i.payment_date > SYSDATE-365
Re: joining two tables with conditions [message #236833 is a reply to message #236832] Fri, 11 May 2007 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JOIN only join 2 tables.
You have to JOIN the JOIN...

Regards
Michel
Re: joining two tables with conditions [message #236841 is a reply to message #236833] Fri, 11 May 2007 02:17 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Now I get the "missing a keyword" on line 7...

SELECT c.name, b.title, x.quantity
FROM customer c
JOIN book b
JOIN item_of_invoice x
JOIN invoice i
ON (c.id=i.customer_id)
AND (b.isbn = x.isbn) <-------------------------------
WHERE i.payment_date > SYSDATE-365

Re: joining two tables with conditions [message #236851 is a reply to message #236841] Fri, 11 May 2007 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read join clause and the associated examples.

[edit: fix link]

Regards
Michel

[Updated on: Fri, 11 May 2007 03:13]

Report message to a moderator

Re: joining two tables with conditions [message #236854 is a reply to message #236851] Fri, 11 May 2007 02:45 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Michel Cadot wrote on Fri, 11 May 2007 02:37
Please join clause and the associated examples.



What?

The link is not working.
Re: joining two tables with conditions [message #236863 is a reply to message #236854] Fri, 11 May 2007 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fixed.
joining 4 tables [message #236963 is a reply to message #235621] Fri, 11 May 2007 07:46 Go to previous messageGo to previous message
hristo
Messages: 258
Registered: May 2007
Senior Member
Im creating a view that shall do this:

Show what that have been bougth the last year, sorted after customer. Every customer should be represented with rows=book title (bougth 4 different books, 4 rows). If a customer have bougth more then ONE title, only a summarized line will be shown.

Example:

customer_name title quantity
Peter SQL for dummies 1
John Webdev 2.0 1
John Playboy 1
Eric Office for Dummies 5



Here are the involved tables:

BOOK: isbn (pk), title, in_stock, price, author_id (FK), type_id (FK), supplier_id(FK)

CUSTOMER: id (PK), name, adress, contact

ITEM_OF_INVOICE: invoice_nr (PK), item_id, quantity, isbn (FK from book)

INVOICE: invoice_nr (PK), invoice_date, payment_date, amount, customer_id(FK from customer)

So, i want to show the name of the customer (from table customer), the title (from book) and the quantity (from item_of_invoice).

Feel free to ask if something is unclear

This is what I got, codewise:

SELECT c.name, b.title, x.quantity
FROM customer c
JOIN book b
JOIN item_of_invoice x
JOIN invoice i
ON (c.id=i.customer_id)
AND (b.isbn = x.isbn)
WHERE i.payment_date > SYSDATE-365

I know that this doesnt work, I get a "missing keyword" on line 7.
Plz help!
Previous Topic: How to natural join 3 tables efficiently?
Next Topic: SQL
Goto Forum:
  


Current Time: Fri Dec 09 23:19:47 CST 2016

Total time taken to generate the page: 0.09421 seconds