Adding second Join with Select Statement

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding second Join with Select Statement

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2011-05-09 : 15:03:12

I have the following query. I want to add another join to it, but can't seem to get the syntax correct. Can anyone help?

The syntax is Microsoft Access as I am developing an Access report.

SELECT ICTQ.STOCKGROUP AS AStockGroup, ICTQ.COSTTOTALS AS ACostTotals, SGUQ.VARIANCE AS AVariance, ICTQ.STOCKGROUP AS BSTOCKGROUP, SGAQ.VARIANCE AS BVARIANCE
FROM Inventory_Cost_Totals_Query AS ICTQ 
LEFT OUTER JOIN (SELECT DISTINCT VARIANCE, STOCKGROUP
FROM VARIANCE_BY_STOCK_GROUP_QUERY 
WHERE TYPEOFRECORD = "UPDATED RECORD") AS SGUQ
ON ICTQ.STOCKGROUP = SGUQ.STOCKGROUP

This first query works fine and I get the expected results, the field in Italics is only for this second Join. This second join is the Join I would like to add, thus supporting the field in Italics.


LEFT OUTER JOIN (SELECT DISTINCT VARIANCE, STOCKGROUP
FROM VARIANCE_BY_STOCK_GROUP_QUERY 
WHERE TYPEOFRECORD <>  "UPDATED_RECORDS") AS SGAQ
ON ICTQ.STOCKGROUP = SGAQ.STOCKGROUP

Let me elaborate for further clarification. I have two tables, but three queries that I run. The first query provides me all of our StockGroups with their total dollar value. In another table, I store records when we update the inventory. There are only three fields: TYPEOFRECORD, VARIANCE, and STOCKGROUP. However, I have two queries to run against this table. One gets all records where the TYPEOFRECORD = "UPDATE RECORD". My second query gets any other records not found in the first query. I want to join these three queries to produce a report. The report looks like the following.


STOCKGROUP   STOCKTOTALS   VARIANCE    STOCKGROUP   VARIANCE
A1           1,200,000     175.00      A1           500.00
A2           350,000       790.00      A2             0.00
A3           90,000          0.00      A3           800.00

The only difference between the left side of the report and the right side is that the left side is for UPDATED records and the right side is for any other type of record.
Hope this helps.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-10 : 03:02:10

Where is the problem?
Any error messages?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources