SQL Join Query

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

You have

	AND Profile_Text.TextLanguage = 'EN'

in your WHERE clause which will convert your Outer Join to an Inner Join.

Change:

	LEFT OUTER JOIN Profile_Text 
		ON Profile_Text.ID = Profile.ID 
		AND Profile_Text.TextLanguage = '1' 

to

	LEFT OUTER JOIN Profile_Text 
		ON Profile_Text.ID = Profile.ID 
		AND Profile_Text.TextLanguage = '1' 
		AND Profile_Text.TextLanguage = 'EN'

(and remove that from the where clause)

My suggestion would be to lay your code out with better formatting - IME this type of thing is much easier to see in well formatted code and consistent code styling.

Great Thanks .

Sorry I missed one thing to mentioned "Profile_Text.TextLanguage = '1'" and "Profile_Text.TextLanguage = 'EN'"
its samw my front end return 1, if 1 means --> Profile_Text.TextLanguage = 'EN'

Now I look at my code its clearly not going to work as Profile_Text.TextLanguage can't be both "1" and "EN" !!

If you need EITHER then use OR (and some parenthesis so the AND and OR are unambiguous)

Still Null values not returned from Select Query

Post your code pls - otherwise we are just going to be guessing ...

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 = '1'

Same problem you had before, you've reference the Outer Join table in the WHERE forcing it to become an inner join

...
LEFT OUTER JOIN Profile_Text 
	ON Profile_Text.ID = Profile.ID 
	AND Profile_Text.TextLanguage = '1' 
...
WHERE 
...
    AND Profile_Text.TextLanguage = '1'