How to count in SQL query using inner join

Question

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 11-May-21 10:57am

Gcobani Mkontwana 2021

Updated 12-May-21 22:08pm

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2021-05-12T22:08:00

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.