Join tables question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join tables question

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-10 : 15:02:41

I have the following query which may look complicated, but I am just trying to get 5 columns and a sixth one with a CASE statement. I am trying every which way to get a list of stations that did not synchronize yesterday and a determine from another table whether they had any appointments during that period or not and put a 'yes' or 'no' in the scheduled column if they haven't. I have been getting all kinds of results that are not correct including too many rows, too few rows, etc. I would appreciate some help for this, as I have struggled too long with this and other tasks at this new job I have and I feel my job may be on the line if I can't get it. Thank you.

DECLARE  @STARTDATE DATETIME

DECLARE  @ENDDATE DATETIME

SET @STARTDATE = Dateadd(d,-1,Convert(DATETIME,Convert(VARCHAR(11),Getdate(),109),
                                      109))

SET @ENDDATE = @STARTDATE + 1

SELECT   o_operator.operator_name        AS [LAST USER],
         o_workstation.station_name      AS station,
         o_workstation.last_synchronized AS [MOST RECENT SYNC DATE],
         sys_synch_log.synch_type        AS TYPE,
--       SYS_SKED_CONFLICT.DATE_PROVIDED AS SCHEDULED_DATE,
         (SELECT Max(sys_sked_conflict.date_provided)
          FROM   sys_sked_conflict
          WHERE  date_provided BETWEEN @STARTDATE AND @ENDDATE) scheduled_date,
         CASE 
           WHEN sys_synch_log.end_date_time BETWEEN @STARTDATE AND @ENDDATE
           THEN 'Yes'
           ELSE 'No'
         END scheduledyesno
FROM     o_workstation
         INNER JOIN sys_synch_log
           ON o_workstation.station_id = sys_synch_log.station_id
              AND o_workstation.last_synchronized = sys_synch_log.end_date_time
         INNER JOIN sys_synch_conflict
           ON sys_synch_log.synch_log_id = sys_synch_conflict.synch_id
         INNER JOIN o_operator
           ON sys_synch_log.operator_id = o_operator.operator_id
         INNER JOIN sys_sked_conflict
           ON sys_synch_conflict.resource_id = sys_sked_conflict.resource_id
WHERE    sys_synch_log.end_date_time NOT BETWEEN @STARTDATE AND @ENDDATE

It has 4 columns and I want to add a 5th which is whether there is a scheduled date in another table (Yes or No question). But I have to go through a few joins in order to get to that date.

Duane

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-10 : 15:13:56

I think u have missed some conditions in where clause..go through the tables and understand the structures...


DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE   DATETIME
SET @STARTDATE = DATEADD(d,-1,CONVERT(DATETIME,CONVERT(VARCHAR(11),GETDATE(),109), 109))
SET @ENDDATE   = @STARTDATE + 1
SELECT o_operator.operator_name        AS [LAST USER]            ,
       o_workstation.station_name      AS station                ,
       o_workstation.last_synchronized AS [MOST RECENT SYNC DATE],
       sys_synch_log.synch_type        AS TYPE                   ,
       --       SYS_SKED_CONFLICT.DATE_PROVIDED AS SCHEDULED_DATE,
       (SELECT MAX(sys_sked_conflict.date_provided)
       FROM    sys_sked_conflict
       WHERE   date_provided BETWEEN @STARTDATE AND @ENDDATE
       ) scheduled_date,
       CASE
              WHEN sys_synch_log.end_date_time BETWEEN @STARTDATE AND @ENDDATE
              THEN 'Yes'
              ELSE 'No'
       END scheduledyesno
FROM   o_workstation
       INNER JOIN sys_synch_log
       ON     o_workstation.station_id        = sys_synch_log.station_id
          AND o_workstation.last_synchronized = sys_synch_log.end_date_time
       INNER JOIN sys_synch_conflict
       ON     sys_synch_log.synch_log_id = sys_synch_conflict.synch_id
       INNER JOIN o_operator
       ON     sys_synch_log.operator_id = o_operator.operator_id
       INNER JOIN sys_sked_conflict
       ON     sys_synch_conflict.resource_id = sys_sked_conflict.resource_id
WHERE  sys_synch_log.end_date_time NOT BETWEEN @STARTDATE AND @ENDDATE

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-10 : 15:21:33

Thank you for the response and I will do that. I was just wondering, though, because I go through this over and over, that I get way too many rows (sometimes I use a DISTINCT and still get duplicates). I must not be understanding the JOIN statements. I know what CROSS joins are, but I am not sure how I am getting them or if I am getting them, but my results are often in the millions and should be under 10,000 or even less than 1000.

Duane

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-10 : 15:33:47

Hi duanecwilson

You are using only INNER JOINS right..so it will act as a CROSS JOIN but you have to understand the table data
Example
Table A is master it has 10 item details records
Table B is transaction it has 20 sales records

If you put the join both the tables it will display many records
which means one item has sales multiple times right..

so u have some were missed some condition in any of the the WHERE clause..

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources