Query on 2 Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query on 2 Tables

Author

Topic

david_reinjal
Starting Member

36 Posts

Posted - 2007-08-22 : 10:15:35

Hi guys,

i have 2 tables in a database. Table A contains:
State City Employee_first Employee_Last Age
Karnataka Bangalore David Na 23
Karnataka Bangalore Prakash Na 25
Karnataka Mysore Naina Na 26
Karnataka Mysore David Na 35
Maharashtra Mumbai Parneet Na 24
Maharashtra Mumbai Vikas Na 33
Maharashtra Pune Amit Na 25
Maharashtra Pune Amit Na 19

Table B contains:

State Employee_first Employee_Last Salary
Karnataka David Reinjal 15556
Karnataka Prakash Mehra 15323
Karnataka David Petre 36524
Karnataka Kumar Mehra 56123
Maharashtra Parneet Kaur 23315
Maharashtra Vikas Pandey 35645
Maharashtra Amit D'Souza 23564
Maharashtra Amit Dhogla 12354

The output should be:

State City Employee_first Employee_Last Age Salary
Karnataka Bangalore David Reinjal 23 15556
Karnataka Mysore David Mehra 35 56123
Karnataka Mysore Naina Petre 26 36524
Karnataka Bangalore Prakash Mehra 25 15323
Maharashtra Mumbai Parneet Kaur 24 23315
Maharashtra Mumbai Vikas Pandey 33 35645
Maharashtra Pune Amit D'Souza 25 23564
Maharashtra Pune Amit Dhogla 19 12354

I tried using Inner join but it gave me a all together different output. how can i do using Union? Can anybody help me with this?

Regards,

David Reinjal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-22 : 10:20:48

[code]SELECT a.State, a.City, a.Employee_first, a.Employee_Last, a.Age, b.Salary
FROM tablea a INNER JOIN tableb b
ON a.Employee_first = b.Employee_first
AND a.Employee_Last = b.Employee_Last[/code]

KH
[spoiler]Time is always against us[/spoiler]

david_reinjal
Starting Member

36 Posts

Posted - 2007-08-23 : 00:59:21

thanks man. it worked. if i have a Table say A:

State City Employee_first Employee_Last Salary
Karnataka Bangalore David Reinjal 15556
Karnataka Mysore David Reinjal 56123
Karnataka Mysore Naina Petre 36524
Karnataka Bangalore Prakash Mehra 15323

I want David Reinjal with max of salary along with rest of the output. How do i do that? I tried using:
Where state = 'Karnataka and a.Salary IN ('Bangalore', 'Mysore)
But it dint work.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-08-23 : 07:37:10

Try normalizing the data first.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-23 : 07:48:48

[code]Where state = 'Karnataka and a.Salary IN ('Bangalore', 'Mysore')[/code]

KH
[spoiler]Time is always against us[/spoiler]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-23 : 08:22:26

Where state = 'Karnataka and a.CITY IN ('Bangalore', 'Mysore')

Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 05:00:10