CODEWITHSUNDEEP
sqlsql-server-2008t-sql
donviti
donviti

Reputation: 294

Grab all rows for a Records where a single instance occurs in a different row for the record

I have a query where I am looking into three different tables and for the purposes of this post I only need to see three columns; RecordID, FieldType and Tranid. The Record ID can have multiple Field types and each field type will have a distinct tranid.

enter image description here

What I am trying to do is grab all the entire set of data if any of the Field Types = 'CO'

    SELECT     
        Header.RecordID, 
        Detail.FieldType, 
        Header.TranID
    FROM Header 
        INNER JOIN (select * from Detail where fieldtype = 'CO') as Detail 
        ON Header.RecordID = Detail.RecordID 
        INNER JOIN TranDef ON Header.TranID = TranDef.TranID
    WHERE     
        (Header.CalendarDate BETWEEN GETDATE() - 10 AND GETDATE())

But this is not working. I'm only getting one row of data back, where as I said, I want all the data for the record, not just the one row. What am I doing wrong here?

Upvotes: 2

Views: 103

Answers (3)

dckuehn
dckuehn

Reputation: 2475

I feel like your making this more complicated than it should be. Perhaps try the following? I know I've done similiar things in the past.

SELECT     
    h.RecordID, 
    d.FieldType, 
    h.TranID
FROM Header as h
    INNER JOIN Branch as b on b.BranchID  = h.BranchID
    INNER JOIN Detail as d on d.RecordID = h.RecordID
WHERE
   (h.CalendarDate BETWEEN GETDATE() - 10 AND GETDATE())
    and d.fieldtype = 'CO'

Upvotes: 0

daniness
daniness

Reputation: 383

You can try including the below in the WHERE criteria:

SELECT RecordID, FieldType, TranID
FROM Branch br, Header hr, Region rgn
WHERE br.FieldType = 'CO' and 
      hr.FieldType = 'CO' and  
      rgn.FieldType = 'CO'

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

Your query does not work because it limits the rows selected from Detail to these with fieldtype = 'CO', and inner joins to it.

It looks like you are looking for a WHERE EXISTS query:

SELECT     
h.RecordID, 
d.FieldType, 
h.TranID
FROM
     Branch b
INNER JOIN
     Header h ON b.BranchID = h.BranchID
INNER JOIN
     Detail d ON h.RecordID = d.RecordID
WHERE (h.CalendarDate BETWEEN GETDATE() - 10 AND GETDATE())
  AND EXISTS (
      SELECT *
      FROM Detail dd
      WHERE dd.RecordID = h.RecordID AND dd.fieldtype = 'CO'
  )

The idea is to join to all Detail records, and then filter the result based on existence or non-existence of 'CO' records among the Detail rows linked to the corresponding Header row.

Upvotes: 3

Related Questions