question

Steen avatar image
Steen asked

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 comments
10 |1200

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

Thank you everyone! This has been very helpful indeed!

0 Likes 0 ·

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 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered

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

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

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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