Select values only if values in another table exist - SQL Oracle

Question

Can you help me please? Trying to select those article numbers and pic paths that has the picture (some of them are without but it is not null). Is there some HAVING condition I can write? Or maybe just AND condition?

select a.artnr, p.path
from article a, photo p
where a.artnr = p.photo_nr
having ...and ...(?);

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax. — Gordon Linoff
– Gordon Linoff, Commented May 8, 2021 at 12:30
non-ANSI syntax uses commas. That's what the school teaches, wasn't my choice :) — Baibs
– Baibs, Commented May 8, 2021 at 12:36
. . That is really, really sad. The school should be teaching correct, standard syntax. — Gordon Linoff
– Gordon Linoff, Commented May 8, 2021 at 12:39
"That's what the school teaches, wasn't my choice" @GordonLinoff says that is sad. I'd say it's scary. Like going to a school for auto repair that teaches only carburetors and not fuel injection. What would be the "instructor's" ( I use the term very loosely in this case) if you were to present a solution with proper ANSI join syntax? If they reject it, that's even scarier. — EdStevens
– EdStevens, Commented May 8, 2021 at 15:25

Jayvee · Accepted Answer · 2021-05-08 13:23:13Z

1

if you are looking for paths that are not empty, then you should do this:

select a.artnr, p.path
from article a
join photo p on a.artnr = p.artnr_nr
where p.path <> ''

If you have to do it in the very old school way using commas, which I agree with the comments that is not a good practice, then

select a.artnr, p.path
from article a, photo p
where a.artnr = p.artnr_nr
and p.path<>''

answered May 8, 2021 at 13:23

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Baibs Over a year ago

The path is not null and 'not empty', it just doesn't exist. It's not added for some articles - so no photo number or photo type either.

Jayvee Over a year ago

ok,then you don't need an extra condition, the join will only bring the articles that have something in photo. Please, notice that you have to join by a.artnr=p.artnr

Baibs Over a year ago

The actual amount of tables is bigger and it looks like I'm missing something with joining the tables, because nothing is being returned (no data found). Thanks anyway! :)

Jayvee Over a year ago

If you need the data from say table X even when there is no match in table Y, consider X LEFT Join Y on X.key = Y.key

Baibs Over a year ago

I went through all the joins and found the mistake. Took some time, though :-D Thank you!

Collectives™ on Stack Overflow

Select values only if values in another table exist - SQL Oracle

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related