Re: Speaking of SQL.....

[Peter Dow <petercdow@xxxxxxxxx>]

No need for UNION:

Select *  from table1  join table2  on (date and type=’FI’) or type=’I’

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx>

/
On 3/12/2021 6:40 AM, Charles Wilt wrote:
> 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