reduce redundant sql select statement with a different FROM clause for union query

excel vba

i need some way to shorten this code please:
different FROM clause in union query.
SELECT tblXrefInfo_CmNum.Item, tblXrefInfo_CmNum.MFRNUM, tblXrefInfo_CmNum.MFRNAME, tblXrefInfo_CmNum.CompSKUDesc, tblXrefInfo_CmNum.XREF, tblXrefInfo_CmNum.COMMENTS, tblXrefInfo_CmNum.fld_Tag
FROM tblXrefInfo_Mn INNER JOIN tblXrefInfo_CmNum ON tblXrefInfo_Mn.Mfrnum = tblXrefInfo_CmNum.MFRNUM
WHERE (((tblXrefInfo_Mn.Mfrnum)="42236"))
UNION
SELECT tblXrefInfo_CmSku.Item, tblXrefInfo_CmSku.MFRNUM, tblXrefInfo_CmSku.MFRNAME, tblXrefInfo_CmSku.CompSKUDesc, tblXrefInfo_CmSku.XREF, tblXrefInfo_CmSku.COMMENTS, tblXrefInfo_CmSku.fld_Tag
FROM tblXrefInfo_Mn INNER JOIN tblXrefInfo_CmSku ON tblXrefInfo_Mn.Mfrnum = tblXrefInfo_CmSku.MFRNUM
WHERE (((tblXrefInfo_Mn.Mfrnum)="42236"))
UNION
SELECT Null AS Item, tblXrefInfo_Cm_No_Sku.MFRNUM, tblXrefInfo_Cm_No_Sku.MFRNAME, tblXrefInfo_Cm_No_Sku.CompSKUDesc, Null AS Xref, Null AS Comments, tblXrefInfo_Cm_No_Sku.fld_Tag
FROM tblXrefInfo_Mn INNER JOIN tblXrefInfo_Cm_No_Sku ON tblXrefInfo_Mn.Mfrnum = tblXrefInfo_Cm_No_Sku.MFRNUM
WHERE (((tblXrefInfo_Mn.Mfrnum)="42236"));
UNION
SELECT Null AS Item, tblXrefInfo_CmSku_No_GSku.MFRNUM, tblXrefInfo_CmSku_No_GSku.MFRNAME, tblXrefInfo_CmSku_No_GSku.CompSKUDesc, Null AS Xref, Null AS Comments, tblXrefInfo_CmSku_No_GSku.fld_Tag
FROM tblXrefInfo_Mn INNER JOIN tblXrefInfo_CmSku_No_GSku ON tblXrefInfo_Mn.Mfrnum = tblXrefInfo_CmSku_No_GSku.MFRNUM
WHERE (((tblXrefInfo_Mn.Mfrnum)="42236"))
UNION SELECT tblXrefInfo.Item, tblXrefInfo.MFRNUM, tblXrefInfo.MFRNAME, Null AS CompSKUDesc, tblXrefInfo.XREF, tblXrefInfo.COMMENTS, Null AS fld_Tag
FROM tblXrefInfo_Mn INNER JOIN tblXrefInfo ON tblXrefInfo_Mn.Mfrnum = tblXrefInfo.MFRNUM
WHERE (((tblXrefInfo_Mn.Mfrnum)="42236"));

Open in new window


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
You can combine the Where clause in this manner:
Select * From (Select a, b From Table1 UNION Select a, b From Table2) Where a >1

Open in new window

Looking at your SQL string, if the reason for joining tblXrefInfo_Mn is just for the Mfrnum criteria, I would consider even getting rid of the join.
SELECT *
FROM (SELECT Item, MFRNUM, MFRNAME, CompSKUDesc, XREF, COMMENTS, fld_Tag FROM tblXrefInfo_CmNum
      UNION
      SELECT Item, MFRNUM, MFRNAME, CompSKUDesc, XREF, COMMENTS, fld_Tag FROM tblXrefInfo_CmSku
      UNION
      SELECT Null AS Item,MFRNUM, MFRNAME, CompSKUDesc, Null AS Xref, Null AS Comments, fld_Tag FROM tblXrefInfo_Cm_No_Sku
      UNION
      SELECT Null AS Item, MFRNUM, MFRNAME, CompSKUDesc, Null AS Xref, Null AS Comments, fld_Tag FROM tblXrefInfo_CmSku_No_GSku
      UNION
      SELECT Item, MFRNUM, MFRNAME, Null AS CompSKUDesc, XREF, COMMENTS, Null AS fld_Tag FROM tblXrefInfo)
WHERE MFRNUM="42236";

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
If performance is of concern at all:

Do you actually need "UNION"?
You could try "UNION ALL" (for a performance gain)

or, if a blanket UNION ALL doesn't suit, then there appear to be 3 "sets" of rows due to the use of NULL in some columns, so only some tables might produce repeated rows. Perhaps this?
SELECT *
FROM (
	SELECT Item, MFRNUM, MFRNAME, CompSKUDesc, XREF, COMMENTS, fld_Tag
	FROM tblXrefInfo_CmNum
	
	UNION
	
	SELECT Item, MFRNUM, MFRNAME, CompSKUDesc, XREF, COMMENTS, fld_Tag
	FROM tblXrefInfo_CmSku
	)
WHERE MFRNUM = "42236"

UNION ALL

SELECT *
FROM (
	SELECT NULL AS Item, MFRNUM, MFRNAME, CompSKUDesc, NULL AS Xref, NULL AS Comments, fld_Tag
	FROM tblXrefInfo_Cm_No_Sku
	
	UNION
	
	SELECT NULL AS Item, MFRNUM, MFRNAME, CompSKUDesc, NULL AS Xref, NULL AS Comments, fld_Tag
	FROM tblXrefInfo_CmSku_No_GSku
	)
WHERE MFRNUM = "42236"

UNION ALL

SELECT Item, MFRNUM, MFRNAME, NULL AS CompSKUDesc, XREF, COMMENTS, NULL AS fld_Tag
FROM tblXrefInfo
WHERE MFRNUM = "42236";

Open in new window

what brand of database is it by the way?
0
FordraidersAuthor Commented:
what brand of database is it by the way?

ACCESS 2010
0
PortletPaulfreelancerCommented:
Access. OK, thanks. Not sure that my contribution will make any difference in Access.
0
FordraidersAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.