Tabel -> Profile
ID – (PK, Varchar(36), not null)
Description –(nvarchar(255), null)
ID Description Deleted
105 Master Data 0
106 Duplicate Data 0
107 Sample Data 0
Table Currency
ID (PK, Varchar(36), not null)
Country –(nvarchar(50), null)
ID Country
105 MG
106 IM
107 NN
Table Price
ID – (PK, Varchar(36), not null)
Name –(nvarchar(255), null)
> ID Name Deleted
> 105 MG price 0
> 106 IM Price 0
> 107 NN Price 0
Table Profile_Text
ID (FK, varchar(36), not null)
Description –> (Varchar(36), not null)
TextLanguage (PK, varchar(36) not null)
> ID Description TextLanguage Deleted
> 105 MG Text price EN 0
> 106 0
> 107 NN Price 0
> 105 MG Text price UK 0
> 106 0
> 107 NN Price UK 0
Query:
select Price.Description, Profile_Text.description from Price
JOIN Currency_C ON Price.CurrenyID = Currency_C.ID
JOIN Profile ON Price.ProfileID = Profile.ID
LEFT OUTER JOIN Profile_Text ON Profile_Text.ID = Profile.ID
AND Profile_Text.TextLanguage = '1' WHERE Price.Deleted = 0
AND Profile.ID IN (SELECT t1.id FROM Profile t1
JOIN Profile t2 ON t1.Profile.ID = t2.ID AND t2.deleted=0 AND t2.ID
IN(SELECT t3.ID FROM Profile_Versioning t3 WHERE t3.Version='161w'))
AND Profile_Text.TextLanguage = 'EN'
The above query return only if Profile_Text.Description = any values, but we need NULL in description.
Could you please correct the SQL Join Query