Monday, November 18, 2013

SQL | Joins Explained | Using Commerce tables.

This example below outlines lot of SQL Join functionality that we use in a lot of queries in our codes. The below example use commerce tables to demonstrate the joins functionality.

--EXAMPLE 2 OUTER JOIN Table XORDERITEMS:
CREATE TABLE XCATENTRY (catentry_id NUMBER,partnumber varchar2(255), lastupdate timestamp);

--2. Data insertion into table
insert into  XCATENTRY values (11,'DSC_1',sysdate-800);
insert into  XCATENTRY values (33,'DSC_3',sysdate-700);
insert into  XCATENTRY values (1010,'DSC_10',sysdate-500);
insert into  XCATENTRY values (1111,'DSC_11',sysdate-300);

output::
Tables Orders:
ORDERS_ID    PARTNUMBER    STATUS
-----------------------------------------
1        DSC_1    P
2        DSC_2    Y
3        DSC_3    c
4        DSC_4    c
5        DSC_5    c
6        DSC_4    P
7        DSC_3    y
8        DSC_7    c
9        DSC_8    P
10        DSC_9    P

Tables Catentry:
CATENTRY_ID   PARTNUMBER  LASTUPDATE
--------------------------------------------
11        DSC_1    29-08-2011 13:53:21.000000
33        DSC_3    07-12-2011 13:53:21.000000
1010        DSC_10    24-06-2012 13:53:21.000000
1111        DSC_11    10-01-2013 13:53:21.000000

Query: For fetching all partnumber from left table along with matching partnumber */

--Query 1
select o.partnumber from XORDERITEMS o left outer join xcatentry c on o.partnumber = c.partnumber;

--Query 2
select o.partnumber from XORDERITEMS o , xcatentry c where o.partnumber = c.partnumber(+);

output:
PARTNUMBER
DSC_1
DSC_3
DSC_3
DSC_4
DSC_4
DSC_8
DSC_9
DSC_7
DSC_5
DSC_2

--Query: For fetching all partnumber from right table along with matching partnumber

--QUERY
select distinct c.* from XORDERITEMS o right outer join xcatentry c on o.partnumber = c.partnumber;

--ALTERNATE QUERY:
select distinct c.* from XORDERITEMS o , xcatentry c where o.partnumber(+) = c.partnumber;

Result:
CATENTRY_ID    PARTNUMBER    LASTUPDATE
11    DSC_1    29-08-2011 13:53:21.000000
33    DSC_3    07-12-2011 13:53:21.000000
1010    DSC_10    24-06-2012 13:53:21.000000
1111    DSC_11    10-01-2013 13:53:21.000000

-- Query: For fetching all partnumber from left and right table along with matching partnumber

select o.*,c.* from XORDERITEMS o full outer  join xcatentry c on o.partnumber = c.partnumber;


ORDERS_ID    PARTNUMBER    STATUS    CATENTRY_ID    PARTNUMBER_1    LASTUPDATE
1    DSC_1    P    11    DSC_1    29-08-2011 13:53:21.000000
7    DSC_3    y    33    DSC_3    07-12-2011 13:53:21.000000
3    DSC_3    c    33    DSC_3    07-12-2011 13:53:21.000000
6    DSC_4    P
4    DSC_4    c
9    DSC_8    P
10    DSC_9    P
8    DSC_7    c
5    DSC_5    c
2    DSC_2    Y
            1111    DSC_11    10-01-2013 13:53:21.000000
            1010    DSC_10    24-06-2012 13:53:21.000000


--EXAMPLE 3 Query with date range
--Query 1: select partnumber of year 2011
select partnumber from xcatentry where to_date(to_char ( lastupdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('01/01/2011','DD/MM/YYYY') AND TO_DATE('31/12/2011','DD/MM/YYYY')

output:
PARTNUMBER
DSC_1
DSC_3

--Query 2: select partnumber of year 2011 and 2012 but not in orders tables
select partnumber from xcatentry c where to_date(to_char ( c.lastupdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('01/01/2011','DD/MM/YYYY') AND TO_DATE('31/12/2012','DD/MM/YYYY')
and not exists  (select 1 from XORDERITEMS o where o.partnumber = c.partnumber)

RESULTS:
PARTNUMBER
DSC_10

--EXAMPLE 4  Query : Count the status
select status,count(*) from orders group by status order by status;

output:
STATUS    COUNT(*)
P          4
Y          1
c          4
y          1

Tables XORDERITEMS:
ORDERS_ID         PARTNUMBER   STATUS
-----------------------------------------
1                              DSC_1   P
2                              DSC_2   Y
3                              DSC_3   c
4                              DSC_4   c
5                              DSC_5   c
6                              DSC_4   P
7                              DSC_3   y
8                              DSC_7   c
9                              DSC_8   P
10                           DSC_9   P

Tables XCatentry:
CATENTRY_ID   PARTNUMBER  LASTUPDATE
--------------------------------------------
11                           DSC_1   29-08-2011 13:53:21.000000
33                           DSC_3   07-12-2011 13:53:21.000000
1010                       DSC_10 24-06-2012 13:53:21.000000
1111                       DSC_11 10-01-2013 13:53:21.000000

Tables Xcatentdesc:
CATENTRY_ID    LANGUAGE_ID SHORTDESCRIPTION
11           -1            DSC_1_DESCRIPTION
33           -1            DSC_3_DESCRIPTION
1010       -1            DSC_10_DESCRIPTION
1111       -1            DSC_11_DESCRIPTION

Query 1: Example: inner join - two table
select o.orders_id, o.partnumber,c.lastupdate from Xorders o inner join Xcatentry c on o.partnumber= c.partnumber;
o/p:
ORDERS_ID         PARTNUMBER   LASTUPDATE
1              DSC_1   8/29/2011 1:53:21.000000 PM
3              DSC_3   12/7/2011 1:53:21.000000 PM
7              DSC_3   12/7/2011 1:53:21.000000 PM

Query 2: Example: inner join - three table
select o.orders_id, o.partnumber,cd.shortdescription from Xorders o inner join Xcatentry c on o.partnumber= c.partnumber inner join Xcatentdesc cd on c.catentry_id
= cd.catentry_id;

output:
ORDERS_ID         PARTNUMBER   SHORTDESCRIPTION
1              DSC_1   DSC_1_DESCRIPTION
3              DSC_3   DSC_3_DESCRIPTION
7              DSC_3   DSC_3_DESCRIPTION


1 comment:

  1. Regardless of what the good reasons may be, truly appreciate these kind of ideas from guidance as a result of all of our designer watches. Due to a good model of which enhances the switch and the lug length of time http://www.hotreplicaonline.com, the one sports much wider as compared to you could imagine. I have got 7 " arms as well as face who are employed to observing looks after about this hand, although the follow seems perfectly and also sub-40mm sizing keeps a different watches because of feeling funky and / or too big for their design. You receive all the effect this 38mm had not been simply picked out about the wish, but instead how the completely new dimension for many within their almost all well-known different watches was initially the topic of significant amounts of account not to mention intending. Any timepieces keep to therefore directly for their insular feeling regarding design and style which they usually often are in existence close to tastes and design.

    ReplyDelete