outer join with restrictive clause

[stroy]

Help


Languages
IdLang
Description

Expresssion
IdExp
Date
…

LangExpression
IdExp
IdLand
Description

I need a list of all languages with an indicator (true/false or count…)to know if the expression exist for that language for a specific expression

Language
Fr French
En English
Sp Spanish

Expression
1 10/10/10
2 10/10/10
3 10/10/10

LangExpression
1 Fr jour
1 En day
1 Sp dias
2 Fr nuit
2 En night
3 Fr rue
3 En street

I need for expression 2
FR true
EN true
SP false

If I go with joins, the “WHERE idExp = 2“ removes the SP entry from the resultset.

Please help
Thanks.

 

[tlbroadbent]

The answer will depend on your database. Here is a Microsoft SQL Server solution.

Select
q.IdExp, q.IdLang,

--Use Case statement to determine exists value
Case When le.IdLang Is Null
Then 'False'
Else 'True'
End As ExpExists
From

--Create a cross join of Language and Expression
(Select IdExp, IDLang
From Language As l
Cross Join Expression As e) As q

--Left join sub query to LangExpression
Left Join LangExpression As le

--Join on two columns
On q.IDExp=le.IdExp
And q.IdLang=le.IDLang

Order By q.IdExp, q.IdLang Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[stroy]

Thanks Terry, I will work on this! I am using oracle 8i so if you also have oracle knowledge...It would really help me.

 

[stroy]

Me again,

I am not sure I undersatnd where I put my condition
"idExp = 2"...
Thanks

 

[tlbroadbent]

You can put the Where clause just before the Order By clause. I have no knowledge of Oracle. The query may require some minor changes. I recommend asking your questions in an Oracle forum rather than the ANSI SQL forum. See faq220-1073 for a list of other SQL forums at Tek-Tips. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[stroy]

I think I got it right...

select t2.idLANG, decode((select t1.idLANG from langExpression t1 where t1.idexp = '1' and t1.idLANG = t2.idlang), null, 'false', 'true')
from langue t2

thanks

 

[tlbroadbent]

Good! Decode is an Oracle extension. It is not standard ANSI and is not available in the products that I use. It is important to get into the correct forum to ask your questions because each RDMS has its own level of ANSI SQL compliance as well as its own extensions of the language. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.