Solved

SQL 2008 R2

Posted on 2013-10-30
8
366 Views
Last Modified: 2013-11-15
I am working with two tables:

1- Contract_Information
2- Additional_Tests

In Contract_Information I have the following columns:

Id      int      Unchecked
ParentId      int      Unchecked
Date_Printed_D      date      Checked
Time_Printed_S      nvarchar(1000)      Checked
Applicant_S      nvarchar(1000)      Checked
Certificate_No_I      int      Checked
Contract_No_I      int      Checked
Date_Received_D      date      Checked
Date_Inspected_D      date      Checked
Grower_S      nvarchar(1000)      Checked
Field_No_I      int      Checked
Total_Weight_I      int      Checked
Fee_F      float      Checked
Remarks_S      nvarchar(1000)      Checked
Time_Printed_T      time(7)      Checked
Certificate_No_S      nvarchar(1000)      Checked
Contract_No_S      nvarchar(1000)      Checked
Fee_S      nvarchar(1000)      Checked
Field_No_S      nvarchar(1000)      Checked
Supercedes_Cert_No_S      nvarchar(1000)      Checked
Reason_For_Supercede_S      nvarchar(1000)      Checked

In Additional_Tests I have these columns

Id      int      Unchecked
ParentId      int      Unchecked
INSECT_F      float      Checked
_1_COMPOSITE_F      float      Checked
PIT_SCAB_DAM_F      float      Checked
            

If I type the following query

SELECT     Id, ParentId, Time_Printed_S, Applicant_S, Certificate_No_I, Contract_No_I, Date_Received_D, Date_Inspected_D, Grower_S, Field_No_I, Total_Weight_I, Fee_F,
                      Remarks_S, Time_Printed_T, Certificate_No_S, Contract_No_S, Fee_S, Field_No_S, Supercedes_Cert_No_S, Reason_For_Supercede_S, Date_Printed_D
FROM         dbo.Contract_Information
WHERE     (Applicant_S = N'Company_C') AND (Date_Received_D > CONVERT(DATETIME, '2012-07-31 00:00:00', 102)) AND
                      (Date_Received_D < CONVERT(DATETIME, '2013-08-01 00:00:00', 102))

I get good data from the Contract_Information table for Company_C for the dates 8-1-2012 thru 7-31-2013

How do I get the data associated with these lines of data from the table additional tests??

I have tried this query but it return zero results

SELECT     dbo.Contract_Information.ParentId, dbo.Contract_Information.Time_Printed_S, dbo.Contract_Information.Applicant_S, dbo.Contract_Information.Certificate_No_I,
                      dbo.Contract_Information.Contract_No_I, dbo.Contract_Information.Date_Received_D, dbo.Contract_Information.Date_Inspected_D,
                      dbo.Contract_Information.Grower_S, dbo.Contract_Information.Field_No_I, dbo.Contract_Information.Total_Weight_I, dbo.Contract_Information.Fee_F,
                      dbo.Contract_Information.Remarks_S, dbo.Contract_Information.Time_Printed_T, dbo.Contract_Information.Certificate_No_S, dbo.Contract_Information.Contract_No_S,
                      dbo.Contract_Information.Fee_S, dbo.Contract_Information.Field_No_S, dbo.Contract_Information.Supercedes_Cert_No_S,
                      dbo.Contract_Information.Reason_For_Supercede_S, dbo.Contract_Information.Date_Printed_D, dbo.Additional_Tests.ParentId AS Expr1,
                      dbo.Additional_Tests._1_COMPOSITE_F
FROM         dbo.Contract_Information INNER JOIN
                      dbo.Additional_Tests ON dbo.Contract_Information.ParentId = dbo.Additional_Tests.ParentId
WHERE     (dbo.Contract_Information.Applicant_S = N'Company_C') AND (dbo.Contract_Information.Date_Received_D > CONVERT(DATETIME,
                      '2012-07-31 00:00:00', 102)) AND (dbo.Contract_Information.Date_Received_D < CONVERT(DATETIME, '2013-08-01 00:00:00', 102))


Thanks for your help!!

I hope this makes at least a little bit of sense.
0
Comment
Question by:HDM
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 125 total points
ID: 39612683
I see both tables have id and parent_id.  What columns would be used to relate the two tables together, these or something else?

Currently the JOIN only uses ParentID
FROM  dbo.Contract_Information ci
   JOIN dbo.Additional_Tests at ON ci.ParentId = at.ParentId

Open in new window

btw if you use aliases like the above ci and at, instead of the full table names, the T-SQL statement will be a lot easier to read.
0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 125 total points
ID: 39612688
There is no problem with your query so the issue is with your data or your understanding of it.  Run the query...

SELECT COUNT(1)
FROM dbo.Contract_Information AS C
INNER JOIN dbo.Additional_Tests AS T
   ON C.ParentId = T.ParentId

If you receive a result of 0 or a value much lower than expected than the tables are probably not related as you expect.  Just because the columns have the same name doesn't mean that they are related.  Parent_Id is often used in a hierarchical relation to denote a related record in the same table.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39612689
btw since Date_Received is a date, you shouldn't need to use CONVERT

WHERE .. . AND
  ( ci.Date_Received_D >=  '2012-07-31' AND ci.Date_Received_D < '2013-08-01' )
0
 
LVL 9

Assisted Solution

by:guswebb
guswebb earned 125 total points
ID: 39613096
BriCrowe is probably right here...you are doing an inner join on additional_tests with contract_information binding on parentid and so require the values in the parentid field in one table to also exist in the other table otherwise there'd be nothing upon which to join. Sounds like you have no matching values in the respective parentid columns and so the inner join returns an empty record set.

Post up the results from the following and we'll know a bit more about your data...

select top 10 id, parent_id from contract_information order by parentid
select top 10 id, parent_id from additional_tests order by parentid
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 39613099
those converts aren't being performed on the data so there isn't any penalty in using them.

I'd suggest leaving them.

the following involves 2 "implict conversions" from string to datetime
WHERE .. . AND
  ( ci.Date_Received_D >=  '2012-07-31' AND ci.Date_Received_D < '2013-08-01' )

and while sql server is very adept at handling this - arguably - it is better to be explicit.

(nb: a bias. having cut my teeth in SQL using Oracle, where explicit conversions are needed, I find it odd that so many implicit conversions are the norm is sql server)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39613124
but, I would make 2 suggestions on the where clause, one Jim has implicitly identified
a. line 5 below should be greater than or equal to
FROM dbo.Contract_Information
        INNER JOIN dbo.Additional_Tests
                ON dbo.Contract_Information.ParentId = dbo.Additional_Tests.ParentId
WHERE (dbo.Contract_Information.Applicant_S = N'Company_C')
AND (dbo.Contract_Information.Date_Received_D > CONVERT(datetime, '2012-07-31 00:00:00', 102))
AND (dbo.Contract_Information.Date_Received_D < CONVERT(datetime, '2013-08-01 00:00:00', 102))

Open in new window

b. go easy on the parentheses, but you have missed 2 potentially important ones.

when using >= with < as a date range filter, it should be surrounded by parentheses, and I'd do it this way:
FROM dbo.Contract_Information
        INNER JOIN dbo.Additional_Tests
                ON dbo.Contract_Information.ParentId = dbo.Additional_Tests.ParentId
WHERE dbo.Contract_Information.Applicant_S = N'Company_C'
AND (
     dbo.Contract_Information.Date_Received_D >= CONVERT(datetime,'2012-07-31 00:00:00', 102)
 AND dbo.Contract_Information.Date_Received_D <  CONVERT(datetime,'2013-08-01 00:00:00', 102)
     )

Open in new window

as all your current where conditions are ANDs this suggestion doesn't make a difference right now, but is sure does if you ever need to include an OR
0
 

Author Comment

by:HDM
ID: 39651938
I am going to accept everyones input and close this for now as I just have not had time to finish this question.  I will assign points to everyone evenly.  I hope thast is ok.

Thanks everyone!!  :-)
0
 

Author Closing Comment

by:HDM
ID: 39651942
Thanks!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now