Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

filter out complicated sql statement

I have this complicated join statement. I am trying to filter out with a where and I don't know where to put it.
SELECT     TOP (100) PERCENT dbo.items.groupid, dbo.orderDetail.itemId, SUM(dbo.orderDetail.quantity) AS quantity, dbo.items.title, dbo.items.soldOutAmount, 
                      dbo.items.description, dbo.items.image, dbo.items.cost, dbo.items.per, dbo.items.placementHolder, dbo.items.inStock, dbo.items.id, dbo.items.categoryid, 
                      dbo.items.dateToReset
FROM         dbo.items LEFT OUTER JOIN
                      dbo.orderDetail ON dbo.items.id = dbo.orderDetail.itemId
GROUP BY dbo.items.groupid, dbo.orderDetail.itemId, dbo.items.title, dbo.items.soldOutAmount, dbo.items.description, dbo.items.image, dbo.items.cost, dbo.items.per, 
                      dbo.items.placementHolder, dbo.items.inStock, dbo.items.id, dbo.items.categoryid, dbo.items.dateToReset
ORDER BY dbo.orderDetail.itemId, dbo.items.groupid

Open in new window


I want to filter out where
 
  dbo.orderDetail.dateAdded > dbo.items.dateToReset 

Open in new window

0
rivkamak
Asked:
rivkamak
  • 2
2 Solutions
 
Simone BSenior E-Commerce AnalystCommented:
Normally you would put it after the FROM and before the GROUP BY:

SELECT     TOP (100) PERCENT dbo.items.groupid, dbo.orderDetail.itemId, SUM(dbo.orderDetail.quantity) AS quantity, dbo.items.title, dbo.items.soldOutAmount,
                      dbo.items.description, dbo.items.image, dbo.items.cost, dbo.items.per, dbo.items.placementHolder, dbo.items.inStock, dbo.items.id, dbo.items.categoryid,
                      dbo.items.dateToReset
FROM         dbo.items LEFT OUTER JOIN
                      dbo.orderDetail ON dbo.items.id = dbo.orderDetail.itemId
WHERE .....
GROUP BY dbo.items.groupid, dbo.orderDetail.itemId, dbo.items.title, dbo.items.soldOutAmount, dbo.items.description, dbo.items.image, dbo.items.cost, dbo.items.per,
                      dbo.items.placementHolder, dbo.items.inStock, dbo.items.id, dbo.items.categoryid, dbo.items.dateToReset
ORDER BY dbo.orderDetail.itemId, dbo.items.groupid
0
 
Simone BSenior E-Commerce AnalystCommented:
Sorry, I missed that second bit of code. Here it is:

SELECT     TOP (100) PERCENT dbo.items.groupid, dbo.orderDetail.itemId, SUM(dbo.orderDetail.quantity) AS quantity, dbo.items.title, dbo.items.soldOutAmount,
                      dbo.items.description, dbo.items.image, dbo.items.cost, dbo.items.per, dbo.items.placementHolder, dbo.items.inStock, dbo.items.id, dbo.items.categoryid,
                      dbo.items.dateToReset
FROM         dbo.items LEFT OUTER JOIN
                      dbo.orderDetail ON dbo.items.id = dbo.orderDetail.itemId
WHERE   dbo.orderDetail.dateAdded > dbo.items.dateToReset
GROUP BY dbo.items.groupid, dbo.orderDetail.itemId, dbo.items.title, dbo.items.soldOutAmount, dbo.items.description, dbo.items.image, dbo.items.cost, dbo.items.per,
                      dbo.items.placementHolder, dbo.items.inStock, dbo.items.id, dbo.items.categoryid, dbo.items.dateToReset
ORDER BY dbo.orderDetail.itemId, dbo.items.groupid
0
 
rivkamakAuthor Commented:
It came back with 0 results.
0
 
Brendt HessSenior DBACommented:
Note that the use of a LEFT JOIN makes the WHERE check ambiguous.  If there is no orderDetail record for the items row, will you ever want to show the row with the orderDetail data and NULL for the items data?

If yes, the WHERE should be:

WHERE (
    dbo.orderDetail.dateAdded > dbo.items.dateToReset  
    OR
    dbo.items.dateToReset IS NULL
    )

If no, then make the join an INNER JOIN and remove (at least) optimizer ambiguity.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now