How to get a related row if one (another) row exists?

Question

I'm aware that this question's title might be a little bit inaccurate but I couldn't come up with anything better. Sorry.

I have to fetch 2 different fields, one is always there, the other isn't. That means I'm looking at a LEFT JOIN. Good so far.

But the row I want shown is not the row whose existence is uncertain.

I would like to do something like:

Show name and picture, but only show the picture if that name has a picture_id. Otherwise show nothing for the picture, but I still want the names regardless(left join).

I know this might be a little confusing but there's some clever guys out here so I guess somebody will understand it.

I tried some approaches but I couldn't quite say what I want in SQL.

P.S.: solutions specific to Oracle are good too.

------------------------------------------------------------------------------------------------------------------------------------

EDIT I've tried some queries but the main problem I found is that, inside the ON clause, I am only able to reference the last table mentioned, in other words: There are four tables from which I'm retrieving data, but I can only mention the last (third table) inside the on clause of the LEFT JOIN(which is the 4th table). I'll describe the tables hopefully that'll help. Try not to delve too much on the names, because they are in Portuguese:

There are 4 tables. The fields I want to retrieve are :TB395.dsclaudo and TB397.dscrecomendacao, for a given TB392.nronip. The tables are as follows:

TB392(laudoid,nronip,codlaudo) // laudoid is PK, references TB395

TB395(codlaudo,dsclaudo) //codlaudo is PK

TB398(laudoid,codrecomendacao) //the pair laudoid,codrecomendacao is PK , references TB397

TB397(codrecomendacao,dscrecomendacao) // codrecomendacao is PK

Fields with the same name are foreign keys.

The problem is that there's no guarantee that, for a given laudoid,there will be one codrecomendacao. But, if there is, I want the dscrecomendacao field returned, that's what I don't know how to do. But even if there isn't a corresponding codrecomendacao for the laudoid, I still want the dsclaudo field, that's why I think a LEFT JOIN applies.

Answer 1

Create two views and then do your left join on the views. For example:

Create View view392_395
as
SELECT
t1.laudoid,
t1.nronip,
t1.codlaudo,
t2.dsclaudo
FROM       TB392 t1
INNER JOIN TB395 t2 
        ON t1.codlaudo
         = t2.codlaudo

Create View view398_397
as
SELECT
t1.laudoid,
t1.codrecomendacao,
t2.dscrecomendacao
FROM       TB398 t1
INNER JOIN TB397 t2 
        ON t1.codrecomendacao
         = t2.codrecomendacao

SELECT 
v1.laudoid,
v1.nronip,
v1.codlaudo,
v1.dsclaudo,
v2.codrecomendacao,
v2.dscrecomendacao
FROM            view392_395 v1
LEFT OUTER JOIN view398_397 v2
             ON v1.laudoid
              = v2.laudoid

In my opinion, views are always under used. Views are your friend. They can simplify some of the most complicated queries.

Answer 2

Sounds like you want your primary row source to be the join of TB392 and TB395; then you want an outer join to TB398, and when that gets a match, you want to lookup the corresponding value in TB397.

I would suggest coding the primary join as one inline view; the join between the two extra tables as a second inline view; and then doing an outer join between them. Something like:

SELECT ... FROM
  (SELECT ... FROM TB392 JOIN TB395 ON ...) join1
  LEFT JOIN
  (SELECT ... FROM TB398 JOIN TB397 ON ...) join2
  ON ...

Answer 3

I think you need:

SELECT ...
FROM
    TB395 
  JOIN               
    TB392
        ON ...
  LEFT JOIN              --- this should be a LEFT JOIN
    TB398
        ON ...
  LEFT JOIN              --- and this as well, so the previous is not cancelled
    TB397
        ON ...

The details may be not accurate:

SELECT 
    a.dsclaudo
  , b.laudoid
  , c.codrecomendacao
  , d.dscrecomendacao
FROM
    TB395 a 
  JOIN
    TB392 b 
        ON b.codlaudo = a.codlaudo
  LEFT JOIN 
    TB398 c
        ON c.laudoid = b.laudoid
  LEFT JOIN 
    TB397 d
        ON d.codrecomendacao = c.codrecomendacao

Answer 4

It would be nice if you could specify what your tables are, which columns are on which tables, and what columns they join on. Its not clear if you have two tables or only one. I guess you have two tables because you are talking about a LEFT JOIN, and seem to imply that the join is on the name column. So you can use the NVL2 function to accomplish waht you want. So guessing what I can from your question, maybe something like:

SELECT  T1.name
    ,   NVL2( T2.picture_id, T1.picture, NULL )
FROM    table1 T1
LEFT JOIN
        table2 T2
ON      T1.name = T2.name

If you only have one table, then its even simpler

SELECT  T1.name
    ,   NVL2( T1.picture_id, T1.picture, NULL )
FROM    table1 T1