Help with Join and repeated records

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Join and repeated records

Author

Topic

edpfister
Starting Member

5 Posts

Posted - 2009-07-31 : 19:47:34

Here is a shorthand of the tables I am working with and some sample data.

Table = Info
Serial Run
111 1
111 2
111 3
111 4
222 1
222 2
333 1
333 2

Table = PMTS
Serial Run Build
111 1 B
111 2 B
222 1 A
333 1 B

So what I am trying to do is make a query that will give me all rows in the Info table that are from a particular build (user input). So if I request Build B I would want.

Serial Run
111 1
111 2
111 3
111 4
333 1
333 2

The query I am using is:

SELECT * FROM Info INNER JOIN PMTS ON Info.Serial = PMTS.Serial WHERE PMTS.Build = <user input>

This gives me the records I want but I get duplicate entries. I thought about using DISTINCT but one of the columns is text which causes problems.

Am I going about this in the wrong way? What do you suggest, thank you.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 21:07:44

[code]
SELECT *
FROM Info
INNER JOIN
PMTS
ON Info.Serial = PMTS.Serial
And Info.Run = PMTS.run
WHERE PMTS.Build = <user input>
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-01 : 11:41:22

quote:

Originally posted by russell


SELECT  *
FROM    Info
INNER JOIN
        PMTS
ON      Info.Serial = PMTS.Serial
And     Info.Run = PMTS.run
WHERE   PMTS.Build = <user input>

Using column RUN in joining would not give the wanted output posted by edpfister.

No, you're never too old to Yak'n'Roll if you're too young to die.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-01 : 16:06:52

[code]
Declare @UserInput char(1)
set @UserInput = 'B'
SELECT a.*
FROM Info a
INNER JOIN
(Select distinct aa.Serial
from
PMTS aa
where aa.Build = @UserInput) b
ON a.Serial = b.Serial

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-01 : 16:47:19

[code]SELECT i.Serial,
i.Run
FROM Info AS i
WHERE EXISTS (SELECT x.Serial FROM PMTS as x WHERE x.Serial = i.Serial AND x.Build = 'B')[/code]

N 56°04'39.26"
E 12°55'05.63"

edpfister
Starting Member

5 Posts

Posted - 2009-08-03 : 13:44:59

This is what I needed. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources