SQLTeam.com | Weblogs | Forums

SQL Join Query


#1
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


#2

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.


#3

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'


#4

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)


#5

Still Null values not returned from Select Query


#6

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


#7

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'


#8

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'