Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Deyken
Starting Member
1 Post |
 Posted - 2011-02-02 : 01:57:48
|
| Good Day All SQL Server Experts!I certainly hope that someone can assist... I have a 4-tier Master/Detail relationship between 4 Tables. They look like this:tblBOM_Master (PK)BOM_NOBOM_DATECOMMENTStblBOM_SITES (PK)SITE_NO(FK)BOM_NOSITE_NAMEtblBOM_HEADINGS (PK)HEADING_NO(FK)SITE_NOHEADING_DESCRtblBOM_DETAILENTRY_NO(PK)ENTRY_NO(FK)HEADING_NOPRODUCT_CODEPRODUCT_DESCRIPTIONQTYSo the user captures a Master Bill of Materials (BOM), then a range of Sites that belong to the Master BOM, then a range of Descriptive Headings that belong to the Sites and finally a range of Products under each Heading for Each Site under this Bill of Materials.For the User Interface I need a DBGrid listing all the Products: PRODUCT_CODE, PRODUCT_DESCRIPTION, QTY but the QTY Column has to relate to each individual site's QTY. That is, the user wants to capture the Quantities of all the Products for each Site in a single Database Grid - i.e. a QTY Column for each Site, the number of columns then equal to the number of records created on the tblBOM_Sites Table. I realize that this will likely require a complex SELECT statement with all 4 Tables JOINed. This Query has to incorporate into the overall Statement, a QTY column for each Site... How can this be done? JOINING the table in a SELECT STATEMENT does not deliver multiple QTY columns for each Site record. My question, thus: Using the above table columns (on standard PK/FK relationships), what should the SQL Query look like OR What changes do I need to make to the Table Columns/Structures to make this type of Query Possible?I look forward to hearing from the experts!DeykenDeezineTechhttp://www.deezinetech.co.za |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-02 : 11:44:42
|
Can you show some sample data and desired result? It is possible with a select statement, and a CTE I think...
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
|
|
|
Nested SELECT Statement