matched records from table

Question

matched records from table

purushotham216

Valued Member

Points: 65
More actions
September 18, 2010 at 12:43 am

#238413

hi to all .thanks in advance
I want to get matched recods(if name in both locations ) from table
Table is like this
Name Location
a loc1
b loc1
a loc2
o/p (name a in both locations loc1 and loc2)
a loc1
a loc2

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 1

purushotham216 (9/18/2010)
hi to all .thanks in advance
I want to get matched recods(if name in both locations ) from table
Table is like this
Name Location
a loc1
b loc1
a loc2
o/p (name a in both locations loc1 and loc2)
a loc1
a loc2

Hi

Puru

The following may work for you. Just try It.

SELECT *

FROM loc1 INNER JOIN loc2

ON loc1.name = loc2.name;

Ali
MCTS SQL Server2k8

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

What Ali mentioned above will put both names on the same line (and any extra data), which is what you'll want 99% of the time in SQL. If you want them to follow each other in the same result set, it'll be more complex because you'll need to use UNION statements.

You'll pardon if my bigger concern is that the join is a very basic piece of doing anything in SQL server for data. I would recommend some research into basic SQL standards (not necessarily T-SQL, just SQL) for querying the database if you'll be working in it for a while.

You can also use the GUI view creator in the Management Studio, and then look at the SQL that shows up afterwards.

A few sites that might help:

http://sqlzoo.net/

http://www.w3schools.com/sql/sql_intro.asp

Go through those a little bit and you'll feel a lot more confident in the system.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

purushotham216 Valued Member Points: 65 More actions · Answer 3

purushotham216

Valued Member

Points: 65

September 18, 2010 at 1:31 am

#1223431

loc1 and loc2 are not tables..

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 4

purushotham216 (9/18/2010)
loc1 and loc2 are not tables..

hi

Puru

May we know the tables name and design? and some dummy records for exact solution

:w00t:

Ali
MCTS SQL Server2k8

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Ah, I see the confusion.

You want to test the table to see if the table has the same name in two locations? Can it be any two locations, or only those specific two?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

purushotham216 Valued Member Points: 65 More actions · Answer 6

Thanks ali ... u r reply

Table structure

create table temp( lastname nvarchar(100),locationname nvarchar(100))

lastname locationname

a loc1

b loc1

a loc2

c loc1

c loc2

d loc1

o/p (a and c has both locations loc1 and loc2)

a loc1

a loc2

c loc1

c loc2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

For posting sample DDL/tables and data, please do a quick check in the link in my signature, will help out tremendously.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA