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
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.