SQLTeam.com | Weblogs | Forums

Query with left outer join (replace *=)


#1

select distinct V.cCodeRubrique, r.cLibcourt, r.ctypenombre
FROM tparamjournal V ,trubpay r , thistdetailpaie p
where r.ccoderubrique = v.ccoderubrique
AND V.cCodeRubrique *= P.cCodeRubrique
and v.codeetat = 'JRN12'

The result is ok

1 BASE SALRY
8 TRANSPORT PR
35 AV. NAT LOG
37 BIK_LOG
40 Coeff GM
300 GROSS SAL

Since the sql version no longer includes the notion of (* =)
rewrote the request:

select distinct V.cCodeRubrique, r.cLibcourt, r.ctypenombre
FROM tparamjournal V
INNER join trubpay r on r.ccoderubrique = v.ccoderubrique
LEFT OUTER JOIN thistdetailpaie p
on v.cCodeRubrique = P.cCodeRubrique
where v.codeetat = 'JRN12'

THE RESULT
1 BASE SALRY
8 TRANSPORT PR
35 AV. NAT LOG
300 GROSS SAL

So I do not have the 37 and 40 that are not in the table "thistdetailpaie"

THANKS FOR YOUR HELP


#2

Hi wbh,

Please provide with database schema or table details

regards
Anna


#3

table tparamjournal V (ccodeetat, ccoderubrique)
table trubpay r (ccoderubrique, cintitule cLibcourt, ctypenombre)
table thistdetailpaie (cmatricule, ccoderubrique, cmontant)

All the items that are in the trubpay table are also in the tparamjournal table
Topics in tparamjournal may not be in the table thistdetailpaie

Exemple: Table trubpay:
1
2
3
8
35
37
40
100
300
400
....

Exemple: Table tparamjournal:
1
8
35
37
40
300

table thistdetailpaie:

Exemple: Table trubpay:
1
2
3
8
35
300
400
....

The purpose is to read only the headings that are in the table tparamjournal
If the topic does not exist in the table thistdetailpaie I must see 0


#4

I have to read thistdetailpaie because I read a column (number) in my cursor


#5

Hi wbh,

I hope this code solves your problem

Select V.cCodeRubrique ,r.cLibcourt, r.ctypenombre from tparamjournal V
inner join trubpay r on V.ccoderubrique = r.ccoderubrique
left join thistdetailpaie p on V.ccoderubrique = p.ccoderubrique where v.codeetat = 'JRN12'

regards
Anna


#6

thanks


#7

Unless I misread something isn't that the same as the O/P has tried (apart from the DISTINCT)?


#8

I solved my problem with this:

Select V.cCodeRubrique ,r.cLibcourt, r.ctypenombre from tparamjournal V
inner join trubpay r on V.ccoderubrique = r.ccoderubrique
CROSS join thistdetailpaie p
where v.codeetat = 'JRN12'

The result is good

you confirm?