All Forums Database
harish gupta 6 posts Joined 08/12
07 Sep 2015
Need a query

INPUT 
  Tab1                                               Tab2
ID Name                                          ID  Name
1   a                                                1   a
2  b                                                 2  b
3 c                                                  5  e
4 d                                                  6  f
 
OUTPUT :
 
 Tab3 
Id Name 
3  c
4  d
5  e
6  f

VandeBergB 182 posts Joined 09/06
07 Sep 2015

union two outer joins

Some drink from the fountain of knowledge, others just gargle.

saravanatn 10 posts Joined 07/11
07 Sep 2015

Hi,
This is the query you are looking for :

SELECT * FROM (SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM STORE
union
SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM STORE1)B WHERE B.EMPLOYEE_ID NOT IN
( SELECT C.EMPLOYEE_ID FROM STORE AS C INNER JOIN STORE1 AS D ON C.EMPLOYEE_ID=D.EMPLOYEE_ID) ORDER BY 1,2
 
 

harish gupta 6 posts Joined 08/12
08 Sep 2015

very Thanks for you respone ...
 
Thanks 
harish

harish gupta 6 posts Joined 08/12
08 Sep 2015

Hi Experts,
 
 Write a query for this ?
 
custid      productname      qty
-------     ------------    ----
c100     prodcabc         3
c100     prodcabc         4
c100         prodcabc      5
 
oupt:
---
custid     product-QTY1     PRODUCT-QTY2       PRODUCT-QTY3
----------  ---------               --------------              ------------
C100L      3                                  4                         5

manib0907 61 posts Joined 04/15
08 Sep 2015

Generate rownumber and use Max(Case when rownumber=1 then qty) as qty 1.
But do you know what is the max number of rows for a custid?

Cheers,
Mani

08 Sep 2015

Try this!!

 

create volatile table store(cust_id varchar(6), productname varchar(10), qty byteint)on commit preserve rows;

 

insert into store('c100','prodcabc',3);

insert into store('c100','prodcabc',4);

insert into store('c100','prodcabc',5);

 

sel cust_id,

max(case when rnk=1 then qty end) as product_QTY1,

max(case when rnk=2 then qty end) as product_QTY2,

max(case when rnk=3 then qty end) as product_QTY3

from 

(sel cust_id,productname,qty,  row_number() over (partition by cust_id order by qty) as rnk from store) a

group by 1

-Ravikiran

talk2soumya 28 posts Joined 11/11
08 Sep 2015

It is (A-B) union (B-A). You can write it many ways

harish gupta 6 posts Joined 08/12
24 Dec 2015

Hi Experts.
 
Need a query for this :
Input :                                                  
code           market
Na
statewide
Na
statewide
 
outpt
code          market
na               1
statewide     2
na               1
statewide     2
 
 
Thanks,
harish.
 
 
 
 

You must sign in to leave a comment.