I have two tables and header and detail table joined together in a view, I want to just show one row for each record, when I had the query in access I was able to select the option. Can't figure out how to only show one row, is it something within the join statement?
FROM dbo.SO_SalesOrderHeader INNER JOIN
dbo.SO_SalesOrderDetail ON dbo.SO_SalesOrderHeader.SalesOrderNo = dbo.SO_SalesOrderDetail.SalesOrderNo
You only want a single row for each Order? So what data from the detail do you want to see in that single row?
Do you want some type of totals - or the latest detail row - or the first detail row - something else?
I have a field called quantity ordered that I just want the total of all the lines to display with the one record from the header table.
Okay - that is a simple aggregate query:
SELECT ht.hdr_col1
, ht.hdr_col2
, Totals = SUM(dtl.Quantity)
FROM header_table ht
INNER JOIN detail_table dt ON dt.key = ht.key
WHERE ...
GROUP BY
ht.hdr_col1
, ht.hdr_col2;
Sorry but I got completely lost with that, trying to learn. This is my complete query and the dbo.SO_SalesOrderDetail.QuantityOrdered field is what I want to total.
SELECT dbo.SO_SalesOrderHeader.SalesOrderNo, dbo.SO_SalesOrderHeader.BillToName, dbo.SO_SalesOrderHeader.OrderDate, dbo.SO_SalesOrderDetail.QuantityOrdered, dbo.SO_SalesOrderHeader.UDF_NEWHIRE,
dbo.SO_SalesOrderHeader.UDF_EDIT, dbo.SO_SalesOrderHeader.UDF_NEWDESIGN, dbo.SO_SalesOrderHeader.UDF_OLDDESIGN, dbo.SO_SalesOrderHeader.UDF_SHIPBYDATE, dbo.SO_SalesOrderHeader.UDF_NEEDDATE,
dbo.SO_SalesOrderHeader.UDF_EVENTDATE, dbo.SO_SalesOrderHeader.UDF_DATE_PRODUCTION_RECEIVED, dbo.SO_SalesOrderHeader.UDF_DATE_PRODUCTION_COMPLETED, dbo.SO_SalesOrderHeader.OrderType,
dbo.SO_SalesOrderHeader.UDF_MONOGRAMING
FROM dbo.SO_SalesOrderHeader INNER JOIN
dbo.SO_SalesOrderDetail ON dbo.SO_SalesOrderHeader.SalesOrderNo = dbo.SO_SalesOrderDetail.SalesOrderNo
WHERE (LEFT(dbo.SO_SalesOrderDetail.ItemCode, 5) <> '/INST') AND (dbo.SO_SalesOrderHeader.UDF_MONOGRAMING = 'Y') AND (dbo.SO_SalesOrderHeader.OrderType = 'S' OR
dbo.SO_SalesOrderHeader.OrderType = 'B') AND (dbo.SO_SalesOrderHeader.UDF_DATE_PRODUCTION_RECEIVED <> CONVERT(DATETIME, '1753-01-01 00:00:00', 102))
Since you are learning - lets go over a few items in this query. The first is using table aliases - this will make the query easier to manage, second is formatting, third would be using the GUI to build queries - it has many issues and it is just easier to write the queries directly in a new query window, and finally - using functions in the where clause.
Here is the query reformatted:
SELECT soh.SalesOrderNo
, soh..OrderDate
, sod.QuantityOrdered
, soh.UDF_NEWHIRE
, soh.UDF_EDIT
, soh.UDF_NEWDESIGN
, soh.UDF_OLDDESIGN
, soh.UDF_SHIPBYDATE
, soh.UDF_NEEDDATE
, soh.UDF_EVENTDATE
, soh.UDF_DATE_PRODUCTION_RECEIVED
, soh.UDF_DATE_PRODUCTION_COMPLETED
, soh.OrderType
, soh.UDF_MONOGRAMING
FROM dbo.SO_SalesOrderHeader AS soh
INNER JOIN dbo.SO_SalesOrderDetail AS sod ON sod.SalesOrderNo = soh.SalesOrderNo
WHERE LEFT(sod.ItemCode, 5) <> '/INST'
AND soh.UDF_MONOGRAMING = 'Y'
AND (
soh.OrderType = 'S'
OR soh.OrderType = 'B'
)
AND soh.UDF_DATE_PRODUCTION_RECEIVED <> CONVERT(DATETIME, '1753-01-01 00:00:00', 102)
So - this query gives us all of the QuantityOrdered for each Order. You want to total that - so we need to add a GROUP BY. You want to group by all columns in the select list that are not aggregated - and since the only thing you want aggregated is the QuantityOrdered then you will group by all columns except that one:
SELECT soh.SalesOrderNo
, soh..OrderDate
, TotalQuantityOrdered = SUM(sod.QuantityOrdered)
, soh.UDF_NEWHIRE
, soh.UDF_EDIT
, soh.UDF_NEWDESIGN
, soh.UDF_OLDDESIGN
, soh.UDF_SHIPBYDATE
, soh.UDF_NEEDDATE
, soh.UDF_EVENTDATE
, soh.UDF_DATE_PRODUCTION_RECEIVED
, soh.UDF_DATE_PRODUCTION_COMPLETED
, soh.OrderType
, soh.UDF_MONOGRAMING
FROM dbo.SO_SalesOrderHeader AS soh
INNER JOIN dbo.SO_SalesOrderDetail AS sod ON sod.SalesOrderNo = soh.SalesOrderNo
WHERE sod.ItemCode NOT LIKE '/INST%'
AND soh.UDF_MONOGRAMING = 'Y'
AND soh.OrderType IN ('S', 'B')
AND soh.UDF_DATE_PRODUCTION_RECEIVED <> CONVERT(DATETIME, '1753-01-01 00:00:00', 102)
GROUP BY soh.SalesOrderNo
, soh..OrderDate
, soh.UDF_NEWHIRE
, soh.UDF_EDIT
, soh.UDF_NEWDESIGN
, soh.UDF_OLDDESIGN
, soh.UDF_SHIPBYDATE
, soh.UDF_NEEDDATE
, soh.UDF_EVENTDATE
, soh.UDF_DATE_PRODUCTION_RECEIVED
, soh.UDF_DATE_PRODUCTION_COMPLETED
, soh.OrderType
, soh.UDF_MONOGRAMING
I modified the WHERE clause - removing the LEFT function and using NOT LIKE instead. That may perform better if an index can be used. I also used IN for the OR criteria - simplifies the query and I removed all of the extra parentheses.
I then added a SUM for the QuantityOrdered and named that column as TotalQuantityOrdered.
If you need to add additional columns from the detail - they can be added using an appropriate aggregate. For example, if you also want to show the total number of detail line then you could add: TotalLines = COUNT(*).
Well didn't take all your advice but I got it working and formatted correctly. A couple questions, I used the design function to create the view/query (felt more comfortable because I had experience with Access), when I changed the actual statements in the design view, it greyed everything out, almost like the design view didn't like the syntax of the SQL statements. I am also using this view to pull straight into an excel sheet for viewing and calculating certain fields (amount of time between dates, etc).
That is the problem with the GUI - it has many well-known issues, which is why I don't use it. As for creating a view - I find it much easier to open a new query window, write the SQL code - test and validate the results and then add the CREATE VIEW.
The GUI doesn't like using table aliases - and it reformats code in a gawd-awful format that is hard to read, another reason I don't recommend using it.
If you continue using the GUI you will eventually run into more issues where the GUI won't allow you to use perfectly valid T-SQL because it doesn't recognize it.