Click here to Skip to main content
14,982,471 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Team

I am struggling to count, number of times units passed between 06:00am-20:00pm today using inner join. What i want to find out how many units(PNR column from Checkpoint-Movement table) has passed(Date-Passed column from Checkpoint-Movement table) to Station Table(Station Description column). The issue now i dont get data on PNR column, that is where i need to see what value it should return using inner join

What I have tried:

SELECT TOP 1000 [KNR]
	,[Station Description] 
      ,[PNR]
      ,[Detect_Or_Misread]
       ,[Timestamp]   
      
  FROM [Tracking_Server_DB].[dbo].[TS_Station], [Tracking_Server_DB].[dbo].[Checkpoint Movement], [Tracking_Server_DB].[dbo].[Reader_Info] 
  where [Tracking_Server_DB].[dbo].[TS_Station].[Transaction Code]  = [Tracking_Server_DB].[dbo].[Checkpoint Movement].[Transaction Code]
  and [Tracking_Server_DB].[dbo].[TS_Station].[Transaction Code]  = [Tracking_Server_DB].[dbo].[Reader_Info].[Transaction_Code]  
Posted
Updated 12-May-21 22:08pm
v2
Comments
FranzBe 12-May-21 3:50am
   
What you show in the "What I have tried" section is merely a copy-paste of Sql Server Management Studio generated SELECT statements. That's not much. I have difficulties to see how the two tables are linked.
From my point of view it is not best practice to have blanks in column names and tables names; If you provide an underscore to TS_Station why not do so at Checkpoint_Movement as well (same for Return_CP_Name). This is inconsistent and will make your work harder.
Gcobani Mkontwana 2021 12-May-21 19:07pm
   
@FranzBe please have a look at my new query, the problem i am facing, PNR record the column is empty. How do i filter to see any data is available by using inner join
CHill60 13-May-21 3:49am
   
Some sample data and expected results would be incredibly helpful
FranzBe 13-May-21 9:21am
   
As a hint: When you use the "Reply" button to add your comment to my comment, I will get a notification. This way I would have looked earlier; You've got a perfect answer from CHill60 anyway.

1 solution

Part of your problem is the way you are doing your JOIN. Don't use the WHERE clause to define how tables are joined - use the ON clause. There is further information on why here - Difference between WHERE and ON in SQL to JOIN data[^]. The main improvements will be readability and being able to do LEFT OUTER joins when you need them.

Also consider using Table Aliases (see SQL Server: ALIASES[^] ) - again it makes your code easier to read. Code that is easier to read is easier to understand, fix and maintain.

As I said in my comment, some sample data for each table would have been incredibly helpful, but for the lack of it I am going to assume that [PNR] is on either [Checkpoint Movement] or [Reader_Info]. I'll make your code more readable as well
SQL
USE [Tracking_Server_DB];
SELECT TOP 1000 
	[KNR]
	,[Station Description] 
    ,[PNR]
    ,[Detect_Or_Misread]
    ,[Timestamp]   
      
FROM [TS_Station] TSS 
INNER JOIN [Checkpoint Movement] CM 
    ON TSS.[Transaction Code]  = CM.[Transaction Code]
INNER JOIN [Reader_Info] RI 
    ON TSS.[Transaction Code] = RI.[Transaction_Code];
Quote:
the issue now i dont get data on PNR column,
Now here's the thing, because you are using INNER join you won't get any rows where the [Transaction Code] is not on all of the tables (this is explained fully in Visual Representation of SQL Joins[^] )
So, if you are getting rows in your results and the [PNR] column is blank, then the row(s) for that [Transaction Code] on the table that holds the [PNR] column don't have any values in the [PNR] column. It is as simple as that.

However, if you mean you are not getting any rows back or not as many as you were expecting, change those INNER JOIN to LEFT OUTER JOIN and examine your data.
   
Comments
Gcobani Mkontwana 2021 13-May-21 19:45pm
   
@Chill60 thanks mate, you made interesting points there. Let me examine this solution and get back to you if encounter any issues.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900