SQL Filtering by Joins

Question

I'm writing my first SQL query, so please excuse my lack of knowledge on the matter.

I'm looking to filter multiple times from each join, and it seems the executed query total rows is growing, instead of shrinking.

/*  

Reads Order Status, determines if it's OPEN
Pulls all OPEN orders to Time Tickets
Reads the Time Ticket TicketDate, determines if it's > 90 days old
Compares PODet JobNo, joins PO table
Reads the PO DateMod, determines if it's > 90 days old

*/


DECLARE @now DATETIME
DECLARE @90daysago DATETIME


SET @now = GETDATE()
SET @90daysago = DATEADD(day, -90, @now)

SELECT
    o.JobNo,
    o.OrderNo,
    o.PartNo,
    o.Status,
    o.JobNo,
    t.TicketDate,
    p.Status,
    p.OutSideService,
    p.PONum,
    po.DateEnt,
    po.DateMod


FROM
    RBCBEMD.dbo.OrderDet AS o       /* OrderDet = o */

INNER JOIN RBCBEMD.dbo.TimeTicketDet AS t       /* TimeTicket = t */
    ON o.JobNo = t.JobNo

INNER JOIN RBCBEMD.dbo.PODet AS p           /* PODet = p */
    ON o.JobNo = p.JobNo

INNER JOIN RBCBEMD.dbo.PO AS po         /* PO = po */
    ON p.PONum = po.PONum

WHERE 
    o.Status = 'Open' AND
    t.TicketDate <= @90daysago AND
    po.DateMod <= @90daysago

ORDER BY
    cast(t.TicketDate as DATETIME) DESC

The query is supposed to go find the OPEN orders from the OrderDet table. From there, if it is OPEN then pull in the last TicketDate from the TimeTicketDet table. Determine if the TicketDate is >90 days old. If it is >90 days old, pull the PONum from the PO table, find its DateMod and determine if it is >90 days old.

If the (o.status ='Open') AND (t.ticketDate >90 days old) AND (po.DateMod >90 days old) then post the result for the JobNo.

I can't find anything wrong with the query from a quick scan. It is doing exactly what you are looking for. Now, since more and more tickets are going to be older than 90 days, I would expect the results to grow each time you run it. You have the ```t.ticketDate and po.DateMod``` listed in the results, why don't you spot check to see if they are indeed older than 90 days each? A specific problem would be helpful for us in helping you out. — Anand, Apr 21 '17 at 19:42

score 0 · Answer 1 · answered Apr 21 '17 at 19:39

Let me answer your question narrowly first: your tables have a one to many or many to many relationship which is causing duplicate rows returned. You'll want to create tables that act to sort the many to many join or apply stronger filters in your where statement to get rid of them.

If you look at your results you'll see duplicated keys, probably something like this:

O.Jobno | P.Status 1 | open 1 | closed

Note how your primary key from your first table (O.Jobno) will occur multiple times.

Here's a good post to get you going in the right direction: Resolve many to many relationship

score 0 · Accepted Answer · answered Apr 21 '17 at 19:49

0

Oh I see the problem after reading your needs a second time. You only want to pull the last ticket date from TimeTicketDet table. You need a simple not exists to remove the duplicate rows:

DECLARE @now DATETIME
DECLARE @90daysago DATETIME

SET @now = GETDATE()
SET @90daysago = DATEADD(day, -90, @now)

SELECT
    o.JobNo,
    o.OrderNo,
    o.PartNo,
    o.Status,
    o.JobNo,
    t.TicketDate,
    p.Status,
    p.OutSideService,
    p.PONum,
    po.DateEnt,
    po.DateMod


FROM
    RBCBEMD.dbo.OrderDet AS o       /* OrderDet = o */

INNER JOIN RBCBEMD.dbo.TimeTicketDet AS t       /* TimeTicket = t */
    ON o.JobNo = t.JobNo

INNER JOIN RBCBEMD.dbo.PODet AS p           /* PODet = p */
    ON o.JobNo = p.JobNo

INNER JOIN RBCBEMD.dbo.PO AS po         /* PO = po */
    ON p.PONum = po.PONum

WHERE 
    o.Status = 'Open' AND
    t.TicketDate <= @90daysago AND
    po.DateMod <= @90daysago
and not exists (
    select 1
    from RBCBEMD.dbo.TimeTicketDet as t2
    where t2.JobNo = o.JobNo
    and t2.TicketDate > t.TicketDate
    )

ORDER BY
    cast(t.TicketDate as DATETIME) DESC

answered Apr 21 '17 at 19:49

Anand

1,165
10
18

Anand - I really appreciate it. That seems to do the trick, I will do some research to figure out what that not exists clause does. I am now seeing that I have duplicates of many columns, is there any way to remedy that? I saw something online of SELECT DISTINCT but it throws an error on my query. – RBC Kyle Bullard Apr 21 '17 at 20:13
Yes, I suspected you still had duplicates. Do not use a DISTINCT clause here. What I suspect is happening is that you have the PODet table joined in here and I suspect that stands for Purchase Order Details, in which case the ticket is being repeated for each line item in the PO. If all you want is the JobNo, remove that table and any corresponding columns from the select list. – Anand Apr 21 '17 at 20:19
If you need that table to connect job to PO, you can use a DISTINCT but remove any columns from the select list that might make the row distinct. – Anand Apr 21 '17 at 20:22
The database unfortunately was not designed by me, I am also assuming PODet means Purchase Order Details. I will check and see if I am able to link the PO table with the JobNo and delete the PODet from the join. I am getting told to leave the office so I will have to try that Monday and get back to you. Thank you for all the useful information. – RBC Kyle Bullard Apr 21 '17 at 20:27
Noob ;-) no worries, do let me know what you ended up doing to de-duplicate the results; it's always good to know. – Anand Apr 24 '17 at 16:20
Right now I am cheating and exporting it to excel which I have written a macro to delete duplicates. – RBC Kyle Bullard Apr 25 '17 at 12:18

SQL Filtering by Joins

2 Answers2