help with the query

Question

Post reply

help with the query

anjaliagarwal5

Ten Centuries

Points: 1267
More actions
June 15, 2016 at 2:22 pm

#317281

Hi All,
I have an employee table,log table and material table with the following structure. From User Id and To user Id from Log table exists in employee table as userID
Log Table
Id From User Id To User ID LogID date
1 12 14 1 12/23/16
2 16 18 2 12/15/2016
3 23 25 6 05/23/2015
4 28 29 7 12/23/2014
employee table
userID First name last name Full Name
12 Test1 Test2 Test1Test2
16 Test3 Test6 Test1Test6
28 Test5 Test5
23 Test6 Test5 Test6Test5
I have another table called Materials that Serial # and LogId
LogId Serail #
1 123-786
2 675-342
5 789-324
6 344-000
7 Q123-564
In the final query, I want to get
FullName of From User ID | Full Name of To User ID| Serail # |date |
any help will be appreciated. I really need help with this. Do I need to give any additional details.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

This seems like a pretty straight forward query. Can you show us what you've tried and where you are stuck?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

As John said above, this looks pretty straight-forward

I will give you some hints to get you started though

1. Have a look at INNER JOIN's and LEFT OUTER JOIN's in BOL or Google and use one of these in the below steps as required

2. Perform a JOIN between Log Table and employee table to get FullName of From User ID

3. Perform another JOIN between the same set of tables to get FullName of To User ID

4. Perform a JOIN between Log Table and Materials to get the Serial #

Let us know if you have any troubles in following the steps and we will be glad to help.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

bala7malai2013 SSC Enthusiast Points: 105 More actions · Answer 3

You have without duplicate data use union...,If you want with duplicate use union all......

select * from (select * from log_table where from_user_id in (select userid from employee_table)

union

select * from log_table where to_user_id in (select userid from employee_table)) a where logid in (select logid from materials_table)

if any problem..,mail to id bala7malai2013@gamail.com

durai nagarajan SSCoach Points: 15778 More actions · Answer 4

Bala,

user wants to produce the results of below fields

FullName of From User ID | Full Name of To User ID| Serail # |date |

your query will not produce proper results.

Regards
Durai Nagarajan