Home » SQL & PL/SQL » SQL & PL/SQL » join (merged 3)
join (merged 3) [message #306391] Fri, 14 March 2008 01:04 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,
I have two tables.
1.Bought 2. Catch
1. Bought contains " Bought , Bought_name,Bought_code" columns
2. Catch contains " Item, Description,cost,date" columns.

There is no common column in two tables. But I want to use join condition in these two tables. .How to write join condition.

Please give me the solution for this

Thank you
Re: sql join [message #306393 is a reply to message #306391] Fri, 14 March 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to write join condition

On what? If you don't know you can't write it.

Regards
Michel
Re: sql join [message #306394 is a reply to message #306393] Fri, 14 March 2008 01:11 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

There is no common column but i want to join these two.I don't know the condition. here I am giving what columns it contains.

So please give me how to write
Re: sql join [message #306396 is a reply to message #306391] Fri, 14 March 2008 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SELECT bought_name, cost
FROM bought, catch
WHERE catch = 22
  AND bought_code = 42;
Re: sql join [message #306398 is a reply to message #306394] Fri, 14 March 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If there is no column you can join, then you can't join.
The only thing you can do it is a cartesian product that is without no join condition.

Regards
Michel
join (merged) [message #306908 is a reply to message #306391] Mon, 17 March 2008 04:22 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi I have 3 tables.
Table                   columns
--------------------------------------------------------
1. Buy                     buyer,sellername
2. IM                      product,hdate,UOM,dept,sec,subsec
3. stud                    dept,sec,subsec



Required O/P :
--------------
-------------------------------------
buyer   product   dept  sec  subsec
-------------------------------------


so please send me the query for this.

Thank you.
Re: join query [message #306909 is a reply to message #306908] Mon, 17 March 2008 04:27 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:
so please send me the query for this.

how about you posting your code first and the problem your are facing in achiveing the desired o/p?


regards,
Re: join query [message #306910 is a reply to message #306908] Mon, 17 March 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select '-------------------------------------' from dual
union all 
select 'buyer   product   dept  sec  subsec' from dual
union all 
select '-------------------------------------' from dual
/

Regards
Michel

Re: join query [message #306911 is a reply to message #306910] Mon, 17 March 2008 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or better:
select '
-------------------------------------
buyer   product   dept  sec  subsec
-------------------------------------' 
from dual
/

Regards
Michel

[Updated on: Mon, 17 March 2008 04:30]

Report message to a moderator

Re: join query [message #306915 is a reply to message #306909] Mon, 17 March 2008 04:39 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I wrote the query as follows

select b.buyer,iem.product,iem.dept,iem.class,iem.subsec
from buyer b,im iem,stud st 
where iem.class=st.class and b.buyer in
(select buyer from buyer)


Error :
--------
I got for the same item i am getting different buyers..
So please resolve this issue.
I am not able to join BUYER table. Hope i am getting the error
at (select buyer from buyer)
so can you please help me in this issue.

Thank you.
Re: join query [message #306916 is a reply to message #306915] Mon, 17 March 2008 04:42 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I am not able to join buyer table



As it looks to me, there is no way to join the buyer-table, since there is no "buyer" or "seller" column in any of the other tables.

So what you ask is impossible without changing the data model.
Re: join query [message #306917 is a reply to message #306915] Mon, 17 March 2008 04:48 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:
where iem.class=st.class and b.buyer in

No where do i see the column class in any of your tables.Refer to the Oracle Docs for JOINS.


regards,
Re: join query [message #306923 is a reply to message #306917] Mon, 17 March 2008 05:11 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I am getting the output with the above mentioned query as

buyer   product  dept    class     subsecs
------------------------------------------------
90      glass    203     310      234
120     glass    203     310      234
354     glass    203     310      234
435     belts    330     243      890
435     belts    330     243      890
...
...


like this I am getting the result...
So please help me in this issue..
Thank you.
Re: join query [message #306927 is a reply to message #306923] Mon, 17 March 2008 05:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

As it looks to me, there is no way to join the buyer-table, since there is no "buyer" or "seller" column in any of the other tables.

So what you ask is impossible without changing the data model.



Re: join query [message #306955 is a reply to message #306910] Mon, 17 March 2008 06:21 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel ,
Those r required columns from all those mentioned tables.
so select '---' from dual
union all
select 'product status dept ' from dual
union all
select '-----' from dual is not working.

so coiuld you please help me in this issue...

Thank you.
Re: join query [message #306956 is a reply to message #306955] Mon, 17 March 2008 06:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

As it looks to me, there is no way to join the buyer-table, since there is no "buyer" or "seller" column in any of the other tables.

So what you ask is impossible without changing the data model.



The answer will not change, no matter how often you repeat the question.

Even if you are to stubborn to learn even basic oracle skills, how about a little bit of that "reading" stuff? No?
join [message #307208 is a reply to message #306908] Tue, 18 March 2008 02:33 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I have only 2 tables.

1.Supplier 2. Customer

Supplier has columns as follows.
Dept dname Seller
-------------------------
123 dsafd 321
321 dsfdd 3323
...
....
Customer has following columns
Goods Receipt level dept
-----------------------------------

Now i need the details as
Dept seller goods level receopt
--------------------------------------
.....
.....
.....

I wrote a query as follows
select d.dept,d.seller,c.goods,c.level,c.receipt where c.dept=d.dept;


Using this query I am getting duplicate records...
How to avoid those duplicate records.
Please give me a query for this..

Tahnk you.
Re: join [message #307213 is a reply to message #307208] Tue, 18 March 2008 02:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First of all: with that query you would get nothing but an error.
If your datamodel is indeed as simple as you picture, and your query resembles anything like you showed, the reason for duplicate records is that you have dupes in (one of) your tables.
Re: join [message #307221 is a reply to message #307213] Tue, 18 March 2008 02:52 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,
I have entered the query with error
select d.dept,d.seller,c.goods,c.level,c.receipt from supplier d,customer c where c.dept=d.dept;


Thank you
Re: join [message #307222 is a reply to message #307208] Tue, 18 March 2008 02:53 Go to previous messageGo to next message
xost
Messages: 9
Registered: February 2008
Junior Member
you can use union with out join ,
it would eliminate duplicate records ,

example
select Dept, dname, Seller from Supplier
union
select Goods, Receipt, level, dept from Customer

i am a beginner user also , just trying to help ,

Re: join [message #307224 is a reply to message #307208] Tue, 18 March 2008 02:59 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://www.orafaq.com/forum/t/99086/94420/
Is there any particular reason for you to start another thread effectively with the same question and the same explanation ?

Regards

Raj
Re: join [message #307256 is a reply to message #307224] Tue, 18 March 2008 04:48 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Ram.
Now I got common column. Then I wrote one query for this. Then I am getting the duplicate rows also. That's why I ask for your help.

Thank you.
Re: join [message #307259 is a reply to message #307256] Tue, 18 March 2008 04:53 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
user71408 wrote on Tue, 18 March 2008 10:48
Hi Ram.
Now I got common column. Then I wrote one query for this. Then I am getting the duplicate rows also. That's why I ask for your help.

Thank you.


And do you think we know why you're having duplicate rows without having even a notion of the data in your tables?

If your query delivers duplicate rows, then probably there are duplicate rows in your table(s).

Kindly have a glance at OraFAQ Forum Guide and try posting something useful to us.
Re: join [message #307263 is a reply to message #307259] Tue, 18 March 2008 05:05 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Here I am giving the O/P what am getting
Seller 	   Dept      Goods                     Level   Receipt
------------------------------------------------------------
123	  97	 NECKLACE	                A	43	
123	  39	 NECKLACE	                A	43
123	  07	 NECKLACE               	A	43
123	  14	 NECKLACE               	A	43
123	  38	 RIBBON                 	A	43
123	  .45	 TWISTER                	A	43

Like this am getting duplicates....
Please look into this.

Thank you

[Updated on: Tue, 18 March 2008 05:07]

Report message to a moderator

Re: join [message #307265 is a reply to message #307263] Tue, 18 March 2008 05:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't see any duplicate rows
Re: join [message #307270 is a reply to message #307265] Tue, 18 March 2008 05:21 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member


Seller 	   Dept      Goods                     Level   Receipt
------------------------------------------------------------
123	  97	 NECKLACE	                A	43	
123	  97	 NECKLACE	                A	43
123	  97	 NECKLACE               	A	43
123	  97	 NECKLACE               	A	43
123	  97	 RIBBON                 	A	43
123	  97	 TWISTER                	A	43


Please observe here.....
Re: join [message #307274 is a reply to message #307270] Tue, 18 March 2008 05:27 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
user71408 wrote on Tue, 18 March 2008 11:21

Seller 	   Dept      Goods                     Level   Receipt
------------------------------------------------------------
123	  97	 NECKLACE	                A	43	
123	  97	 NECKLACE	                A	43
123	  97	 NECKLACE               	A	43
123	  97	 NECKLACE               	A	43
123	  97	 RIBBON                 	A	43
123	  97	 TWISTER                	A	43


Please observe here.....


Then there are duplicate rows in your base tables; either SUPPLIER or CUSTOMER has duplicate rows.
Re: join [message #307288 is a reply to message #307274] Tue, 18 March 2008 06:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Or he is still joining on the "department" column which doesn't make any sense, and hence the result doesn't make any sense.

But we will probably never know, since there is never any coherent and understandable data + select + results.

./fa/917/0/
Re: join [message #307313 is a reply to message #307270] Tue, 18 March 2008 07:57 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
user71408 wrote on Tue, 18 March 2008 06:21

Seller 	   Dept      Goods                     Level   Receipt
------------------------------------------------------------
123	  97	 NECKLACE	                A	43	
123	  97	 NECKLACE	                A	43
123	  97	 NECKLACE               	A	43
123	  97	 NECKLACE               	A	43
123	  97	 RIBBON                 	A	43
123	  97	 TWISTER                	A	43


Please observe here.....


This is not the same output as your other output. I can easily fake an output too by just typing over numbers when they are not duplicated to make them look like duplicates.

Why don't you show us something useful that has been asked over and over and over again? Are these the kind of responses you give you manager when he asks you for something?
Previous Topic: top 3 average sales
Next Topic: how many Sundays
Goto Forum:
  


Current Time: Sat Dec 03 05:45:44 CST 2016

Total time taken to generate the page: 0.11609 seconds