Want (1) Result with (3) Rows versus what I'm getting now (3) Results with (1) Row

Question

question

Steen asked Jun 13, '20

Want (1) Result with (3) Rows versus what I'm getting now (3) Results with (1) Row

select alan8,aleftb,aladd1,aladd2,alcty1,aladds,aladdz from proddta.f0116 where alan8 = 3443 and aleftb = 120063

select alan8,aleftb,aladd1,aladd2,alcty1,aladds,aladdz from proddta.f0116 where alan8 = 4014 and aleftb = 120059

select alan8,aleftb,aladd1,aladd2,alcty1,aladds,aladdz from proddta.f0116 where alan8 = 4050 and aleftb = 120162

select

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steen commented · Jun 15, 2020 at 07:01 PM

Thank you everyone! This has been very helpful indeed!

0 ·

Magnus Ahlkvist commented · Jun 16, 2020 at 09:37 AM

If there was an answer that helped you, please like it, and if any of the answers solved your problem, please mark it as an answer, for other users to see what worked and what didn't.

0 ·

Answer 1 · 2020-06-13T12:47:51Z

ThomasRushton answered Jun 13, '20

Two options:

UNION your resultsets together

Keep all the SELECT / FROM from the first statement, and use OR clauses to pull together the different results. NB: You'll need to be careful with your brackets.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2020-06-15T00:03:35Z

Magnus Ahlkvist answered Jun 15, '20

I've got a variation on the OR clause suggestion from @ThomasRushton: Use the EXISTS clause in combination with INTERSECT and UNION ALL. In this case, there are only two predicates in your WHERE clauses, so paranthesizing shouldn't be too hard, but when there are more complex predicates in your WHERE clause, this will add clarity and is less error prone.

Here's an example

SELECT alan8, aleftb, aladd1, aladd2, alcty1, aladds, aladdz
FROM proddta.f0116
WHERE EXISTS(
  SELECT alan8, aleftb
  INTERSECT (
    SELECT 3443, 120063
    UNION ALL SELECT 4014, 120059
    UNION ALL SELECT 4050, 120162
  )
)

This has another benefit: When searching for NULL values, INTERSECT will do equality comparison with NULL values, whereas in predicates, you'll have to use IS NULL.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question