True or False Indicator when matching two tables

Question

I need some assistance, I need to pull data from my DB, now I have a FD_Documents table and FD_Revision tables.

I need to match the GUID from both tables and also need to be a revision greater than 2, once the match has been made it must add a new column within the result, QC'ed "1" for matching criteria or a "0" for not matching criteria.

I wrote the below but it does not give me result.

select Case when EXISTS (
       SELECT FD_Documents.GUID,
      FD_Documents.FD_C231FD9A as [Store],
      FD_Documents.FD_97CDB35A as [Store 2],
      FD_Documents.FD_8B9BD5C6 as [Transaction Date],
      FD_Documents.FD_84A4EF1A as [Account Number],
      FD_Documents.FD_1A3D602F as [Name],
      FD_Documents.FD_F8EFD019 as [Date of Birth],
      FD_Documents.FD_9DAADEC8 as [Document Type],
      FD_Documents.FD_8B43AE0B as [Year],
      FD_Documents.FD_E77BE253 as [File Name]
      FROM FD_Documents, FD_Revisions
      WHERE FD_Documents.GUID = FD_Revisions.GUID
      AND FD_Documents.Deleted = '0'
      AND FD_Revisions.Revision < '3')
      THEN CAST(1 as bit)
      ELSE CAST(0 AS bit) END

Just use left join, with HAVING(count) clause .. and in select have that case of bit — Veljko89, May 18 '17 at 10:39
Hi @Veljko89, thank you for your response, please could elaborate by adding your script, this will be well appreciated. — user3309798, May 18 '17 at 10:41
You have 'need to be a revision greater than 2' but the query is FD_Revisions.Revision < '3' - which is it? — NP3, May 18 '17 at 10:43

score 2 · Accepted Answer · answered May 18 '17 at 10:43

I am assuming that FD_Documents.Deleted is an int or bit (not a string as in your post), and that Revision is an int (and should be greater than 2 as in your question description as opposed to your code),

SELECT FD_Documents.GUID,
  FD_Documents.FD_C231FD9A as [Store],
  FD_Documents.FD_97CDB35A as [Store 2],
  FD_Documents.FD_8B9BD5C6 as [Transaction Date],
  FD_Documents.FD_84A4EF1A as [Account Number],
  FD_Documents.FD_1A3D602F as [Name],
  FD_Documents.FD_F8EFD019 as [Date of Birth],
  FD_Documents.FD_9DAADEC8 as [Document Type],
  FD_Documents.FD_8B43AE0B as [Year],
  FD_Documents.FD_E77BE253 as [File Name],
  CASE ISNULL(FD_Revisions.GUID,'') WHEN '' THEN 0 ELSE 1 END AS IsMatching
  FROM FD_Documents LEFT JOIN FD_Revisions
  ON FD_Documents.GUID = FD_Revisions.GUID
  AND FD_Documents.Deleted = 0
  AND FD_Revisions.Revision > 2

it works great, I just added a distinct close on the FD_Document, because every time there is a new revision, it records it in the revision table. Thank you so much — user3309798, May 18 '17 at 10:58

score 0 · Answer 2 · 2017-05-18T11:00:00.203

0

This will give you a distinct list of GUIDs in the status you require

select f.GUID from FD_Documents f
inner join FD_Revisions r on f.GUID = r.GUID
where f.Deleted = '0' and r.Revision>2
group by f.GUID

edited May 18 '17 at 11:00

answered May 18 '17 at 10:45

score 0 · Answer 3 · answered May 18 '17 at 10:47

Something like this

  SELECT FD_Documents.GUID,
      FD_Documents.FD_C231FD9A as [Store],
      FD_Documents.FD_97CDB35A as [Store 2],
      FD_Documents.FD_8B9BD5C6 as [Transaction Date],
      FD_Documents.FD_84A4EF1A as [Account Number],
      FD_Documents.FD_1A3D602F as [Name],
      FD_Documents.FD_F8EFD019 as [Date of Birth],
      FD_Documents.FD_9DAADEC8 as [Document Type],
      FD_Documents.FD_8B43AE0B as [Year],
      FD_Documents.FD_E77BE253 as [File Name],
  case when r.GUID IS NULL then 1 else 0 end [MatchCount]
  FROM FD_Documents d
        LEFT JOIN  FD_Revisions r on d.GUID = r.GUID AND FD_Revisions.Revision > '2' 
  where FD_Documents.Deleted = '0'

I can't say for sure, but that is idea, if you provide some testing data this query could be more specific, as this query ain't tested

True or False Indicator when matching two tables

3 Answers3