CODEWITHSUNDEEP
mysqljoininner-joinouter-join
Tavo
Tavo

Reputation: 175

Correctly do an Outer Join in the same table

I have the following query 

SELECT DISTINCT P.CUSTUM_ID, P.STATUS, M.DATE_RATE, PP.PRICE_USR, PP.DISC
FROM MBR_INFO AS M
INNER JOIN PARENT_INFO AS P
  on M.CUSTUM_ID = P.CUSTUM_ID
INNER JOIN PRICE_PARENT PP
  on P.CUSTUM_ID = PP.CUSTUM_ID
WHERE M.DATE_RATE > (getDate())
  and P.USER_FLAG = 'Y'
order by M.DATE_RATE;

which produces the following output

|   CUSTUM_ID   |   STATUS  |   DATE_RATE   |   STATU   |   ORIG_P  |   PRICE_U |   DISC
---------------------------------------------------------------------------------------------
|   12345678    |   USER    |   2015-05-19  |   NULL    |   0.00    |   2990.00 |   0.00
|   12345678    |   NONUSR  |   2015-05-19  |   NULL    |   0.00    |   5980.00 |   0.00
|   56789012    |   USER    |   2015-08-06  |   NULL    |   0.00    |   0.00    |   0.00
|   56789012    |   NONUSR  |   2015-08-06  |   NULL    |   0.00    |   400.00  |   0.00
|   35789651    |   USER    |   2015-12-23  |   NULL    |   0.00    |   1990.00 |   0.00
|   35789651    |   NONUSR  |   2015-12-23  |   NULL    |   0.00    |   3980.00 |   0.00
|   25879460    |   USER    |   2016-01-01  |   NULL    |   0.00    |   2000.00 |   0.00
|   25879460    |   NONUSR  |   2016-01-11  |   NULL    |   0.00    |   4000.00 |   0.00

as you can see we have two prices, a user and nonuser. I want to change my query so it displays both user and nonuser price in the same row so it remove the user status and now display it with price for users and general public, something like this,

|   CUSTUM_ID   |   DATE_RATE   |   STATU   |   ORIG_P  |   PRICEU  |   PRICENU |   DISC
---------------------------------------------------------------------------------------------
|   12345678    |   2015-05-19  |   NULL    |   0.00    |   2990.00 |   5980.00 |   0.00
|   56789012    |   2015-08-06  |   NULL    |   0.00    |   0.00    |   400.00  |   0.00
|   35789651    |   2015-12-23  |   NULL    |   0.00    |   1990.00 |   3980.00 |   0.00
|   25879460    |   2016-01-01  |   NULL    |   0.00    |   2000.00 |   4000.00 |   0.00

I been reading and the best suggestion has been to use FULL OUTER JOIN but so far I get the same input as using the inner join. How could I change the query to display the data how I need it?

Upvotes: 0

Views: 34

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You can join the table with itself.

This is only the part to show you how convert two rows in a single one. You still need the other tables.

 SELECT P_USER.CUSTUM_ID, 
        P_USER.STATUS,    -- I know is user
        P_NOUSER.STATUS   -- I know is not user
 FROM PARENT_INFO as P_USER
 JOIN PARENT_INFO as P_NOUSER
   ON P_USER.CUSTUM_ID = P_NOUSER.CUSTUM_ID
WHERE
     P_USER.STATUS = 'USER'
 AND P_NOUSER.STATUS = 'NONUSR'

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can do it using conditional aggregation:

SELECT P.CUSTUM_ID, M.DATE_RATE, PP.DISC,
       MAX(CASE WHEN P.STATUS = 'USER' THEN PP.PRICE_USR END) AS PRICEU,
       MAX(CASE WHEN P.STATUS = 'NONUSR' THEN PP.PRICE_USR END) AS PRICENU 
FROM MBR_INFO AS M
INNER JOIN PARENT_INFO AS P
  on M.CUSTUM_ID = P.CUSTUM_ID
INNER JOIN PRICE_PARENT PP
  on P.CUSTUM_ID = PP.CUSTUM_ID
WHERE M.DATE_RATE > (getDate())
  and P.USER_FLAG = 'Y'
GROUP BY P.CUSTUM_ID, M.DATE, PP.DISC
ORDER BY M.DATE_RATE;

If there are separate M.DATE values for each group, then you can remove M.DATE field from the GROUP BY clause and include the field in the SELECT using an aggregate function, like MIN(M.DATE).

Upvotes: 2

Related Questions