How to apply a GROUP BY clause with a 3-Tier (innerjoin) Select Statement

Question

Post reply

How to apply a GROUP BY clause with a 3-Tier (innerjoin) Select Statement

deyken

SSC Rookie

Points: 29
More actions
November 22, 2010 at 12:27 am

#228909

Hi All,
I am an absolute noob with SQL statements (beyond basic select statements) and have been struggling with the following scenario:
I need to create a 3-Tier select statement (Master table - Middle Tier Table - Detail Table) that can display a column for each middle tier record in the final result set. The purpose of the result set is to deliver a list of (Bill of Materials) Products where the user can subsequently alter the quantities in the SITE NAME/SITE CODE columns of the listed products.
Its physical application should deliver a result set the looks like this:
Prod_Code, Description, UOM, SiteName(Qty), SiteName(Qty)... /*The SiteName Column(s) come from the Middle Tier Table, the Qty figure comes from the Detail table, where the SiteCode FK lives. */
Herewith the SQL Code I have attempted so far:
SELECT tblBOM_Detail.PRODUCT_CODE AS [Product #], tblBOM_Detail.PRODUCT_DESCRIPTION AS [Description], tblBOM_Detail.UOM AS [Unit (kg/mt...)], tblBOM_SITES.SITE_NAME AS [Site Name], tblBOM_Detail.NETT_QTY
FROM tblBOM_Detail INNER JOIN
tblBOM_Master ON tblBOM_Detail.BOM_NO = tblBOM_Master.BOM_NO INNER JOIN
tblBOM_SITES ON tblBOM_Detail.SITE_CODE = tblBOM_SITES.SITE_CODE AND tblBOM_Master.BOM_NO = tblBOM_SITES.BOM_NO
WHERE (tblBOM_Detail.BOM_NO = '9') /* this will become a parameter @BomNO */
group by tblBOM_SITES.SITE_CODE
Herewith the Error Code SQL Studio delivers:
sg 8120, Level 16, State 1, Line 1
Column 'tblBOM_Detail.PRODUCT_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Anyone have suggestions? Where am I losing the plot?
Appreciate any assistance! Thank you very much.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Pulastya SSC Veteran Points: 223 More actions · Answer 1

GROUP BY Clause is not required...

SELECT tblBOM_Detail.PRODUCT_CODE AS [Product #],

tblBOM_Detail.PRODUCT_DESCRIPTION AS [Description],

tblBOM_Detail.UOM AS [Unit (kg/mt...)],

tblBOM_SITES.SITE_NAME AS [Site Name],

tblBOM_Detail.NETT_QTY

FROM tblBOM_Detail

INNER JOIN tblBOM_Master

ON tblBOM_Detail.BOM_NO = tblBOM_Master.BOM_NO

INNER JOIN tblBOM_SITES

ON tblBOM_Detail.SITE_CODE = tblBOM_SITES.SITE_CODE

AND tblBOM_Master.BOM_NO = tblBOM_SITES.BOM_NO

WHERE (tblBOM_Detail.BOM_NO = '9') /* this will become a parameter @BomNO */

deyken SSC Rookie Points: 29 More actions · Answer 2

Hi cal

Thank you very much for your input. Although this statement works fine, what I actually need the resut set to display is each individual SITE (from tblBOM_SITES.SITE_CODE) as a Column, regardless of the RecordCount of the middle tier (BOM SITES) table.

Can that be done?

Brigadur SSCrazy Points: 2097 More actions · Answer 3

Hi,

If you want to show rows as columns, you need to use PIVOT or CROSSTAB technique.

There are some good articles about this:

SQL Pivot and Cross Tab

Using PIVOT[/url]

Using PIVOT and UNPIVOT

I hope this is what you are looking for. If you need further help, I would appreciate table schema and som sample data.

Cheers

deyken SSC Rookie Points: 29 More actions · Answer 4

Hi Brigadur,

This seems to be exactly what I'm looking for. Thank you very much!

I will study this up and let you know of I managed.

Cheers