MySQL Forums
Forum List  »  General

select distinct with constant value for left join mysql 5.7
Posted by: SANGHATI GHOSH
Date: January 04, 2017 05:36AM

I have a table t_tender_table where there is a field tenderid. There are 3 rows with value 2.

I need to left join the table with itself. It has some constant values as well. The right side is not returning any value. Here is a simplified version of the query:

SELECT a.tenderid,b.const_field
FROM (SELECT tenderid FROM t_tender_table WHERE tenderid=2 ) a
LEFT JOIN (SELECT 'A' AS const_field, tenderid FROM t_tender_table WHERE 1=2 ) b
ON a.tenderid = b.tenderid;

Result:

tenderid const_field
2,(NULL)
2,(NULL)
2,(NULL)
Now, for MySQL 5.7, when we add a distinct to the clause, the const_field field automatically adds the constant value. Query:

SELECT DISTINCT a.tenderid,b.const_field
FROM (SELECT tenderid FROM t_tender_table WHERE tenderid=2 ) a
LEFT JOIN (SELECT 'A' AS const_field, tenderid FROM t_tender_table WHERE 1=2 ) b
ON (a.tenderid = b.tenderid) ;

Result:

tenderid const_field
2,A
In MySQL 5.5, we got the proper result which is 2, (NULL). Upgrading MySQL version to 5.7 is causing this issue

Please help me resolve this Issue.

Options: ReplyQuote


Subject
Written By
Posted
select distinct with constant value for left join mysql 5.7
January 04, 2017 05:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.