Re: Speaking of SQL.....

[Charles Wilt <charles.wilt@xxxxxxxxx>]

No need for CTE's

UNION ALL is all the Richard needs

Select * from table1 join table 2 on date and type=’FI’
UNION ALL
Select * from table1 join table 2 on type=’I’

If you want duplicates between the two sets removed, then just use UNION
Select * from table1 join table 2 on date and type=’FI’
UNION
Select * from table1 join table 2 on type=’I’

UNION is a SQL set operator.  The others available are INTERSECT and EXCEPT
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzmark.htm

Charles

On Fri, Mar 12, 2021 at 5:58 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> Try this
> With tablefi as
> (Select * from table1 join table 2 on date and type=’FI’),
> tableI as
> (Select * from table1 join table 2 on type=’I’)
> Select a.* from tablefi a
> Union all
> Select b.* from tablei
>
> Or something like that
>
>
>
>
>
>
>
> Alan Shore
> Solutions Architect
> IT Supply Chain Execution
>
> [cid:image001.png@01D71715.6745F8F0]
>
> 60 Orville Drive
> Bohemia, NY 11716
> Phone [O] : (631) 200-5019
> Phone [C] : (631) 880-8640
> E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx
>
> ‘If you're going through hell, keep going.’
> Winston Churchill
>
> From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
> Of Richard Schoen
> Sent: Friday, March 12, 2021 7:31 AM
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Subject: [EXTERNAL] Speaking of SQL.....
>
> OK I have a new one. At least for me…..
>
> I have a need to query a single table with 2 different join criteria to
> pull out 2 distinct sets of records in a single query as one resultset.
>
> A two for one 😊
>
> Pseudo:
> Select * from table1 join table 2 on date and type=’FI’
> Select * from table1 join table 2 on type=’I’
>
> So the SQL isn’t real, but the concept question is.
>
> Can you execute 2 different queries together, sequentially, in tandem,
> whatever you want to call it… and return them together in a single
> resultset.
>
> I would rather not go the RPG or stored procedure route, but I will if
> needed. The results will be consumed by a PHP app.
>
> Thoughts appreciated.
>
> Regards,
> Richard Schoen
> Web: http://www.richardschoen.net<http://www.richardschoen.net><
> http://www.richardschoen.net/<http://www.richardschoen.net/>>
> Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx<mailto:
> richard@xxxxxxxxxxxxxxxxx%3cmailto:richard@xxxxxxxxxxxxxxxxx>>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:
> MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l<
> https://lists.midrange.com/mailman/listinfo/midrange-l>
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:
> MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l<
> https://archive.midrange.com/midrange-l>.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
> support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com<https://amazon.midrange.com>
>
>
>
> The Nature’s Bounty Co. is now The Bountiful Company! Our email address
> will be changing from @nbty.com to @bountifulcompany.com. Please update
> your email address book so that important communications are not
> interrupted.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com