asked on

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

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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?

Fordraiders

ASKER

what brand of database is it by the way?

ACCESS 2010

PortletPaul

Access. OK, thanks. Not sure that my contribution will make any difference in Access.

Fordraiders

ASKER

thanks