SQL Server Performance Forum – Threads Archive
SQL Query
HiI have two tables. One is master and another is transactiontable. I n my master table I have around 500 records with ’employee No’ and other fields. in my Transaction table I have more than 4,000 records with ’employee no’, city and salary.
The query is I want an output with employee number(compare with master and transaction. If it exists on both the table, then only I want to take) and salary in the transaction.
I have given a query like this: SELECT mastertable.employeeno, transaction_table.employeeno,transaction
.salary
FROM mastertable,transaction_table
WHERE mastertable.employeeno= transaction_table.employeeno But the output is giving all the 4,000 records . Instead I want only 500 records which are there in my master table. Can you please help me? Thanks
hi issolla,
try this ull definately get the ans. SELECT m.employeeno, t.employeeno,t.salary
FROM mastertable m
INNER JOIN transaction_table t ON
m.employeeno=t.employeeno U can read more about JOINS like left,right,inner,outer joins
in Books Online.
Hi Ranjitjain
The query will work if i have primary and foreign keys. But in my case there are not
primary/foreign keys. The employee nos can be repeated lot of times in both master and transation tables. In this case how ?
I’m not a developer, but for sure you have a design problem.
Try to normalize tables first.
In my oppinion in master table employee must appear only one time and many in transaction table.
Also primay key in master with foreign in transaction. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
As ur table doent have relationship u need to retrive distinct records,
SELECT DISTINCT m.employeeno, t.employeeno,t.salary
FROM mastertable m
INNER JOIN transaction_table t ON
m.employeeno=t.employeeno
]]>