Urgent Join please help - 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

Urgent Join please help

Author

Topic

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-19 : 09:37:37

i have two tables temp 2 and BP

the temp 2 tables has 27356 rows , i want to pull one more colums from the dbo.bp table so i have still 27356 records , all the coulms from the temp 2 table and one extra colum from thebr table how ever when i run the query below it come back with 30582 records??? am i doing a wrong join? below is query

SELECT dbo.Temp2.*, dbo.BP.Key
FROM dbo.BP RIGHT OUTER JOIN
dbo.Temp2 ON dbo.BP.AKey = dbo.Temp2.AKey

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-19 : 09:43:51

It would be nice to have table structure, sample data and wanted result like this example:

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-19 : 09:47:08

nothing...it's a 1 to many relationship

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-19 : 09:58:11

it should be 1 to 1 join

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-19 : 10:08:51

select * from
(
SELECT dbo.Temp2.*, dbo.BP.Key
FROM dbo.BP RIGHT OUTER JOIN
dbo.Temp2 ON dbo.BP.AKey = dbo.Temp2.AKey
)dt
group by Akey
having count(*) > 1

That will show you the duplicates...

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-19 : 10:17:15

it should be

Post the DDL including the constraints and indexes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-19 : 10:33:41

Thanks guys i will get back to you

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-19 : 11:23:39

ok i will ad more details on becuase i am now trying another way , there are three tables which are

Table 1 : Temp
TID
am
age
date
code
Pid

Table 2 :DH
Fid
OrgDate
pid
SUM
Hid

Table 3:DH
HidHcode
Hdesc

now I have 27000 records in temp table I want to get the Hcode and Hdesc information across so the final output should look like below

Final Output: 27000 rows

TID
am
age
date
code
Pid
Hcode
Hdesc

How can I link this across

Thank you guys

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 11:53:09

Hello w1102157 ,

The question that is being asked is the relations between these three table and the joining values: Are they 1 to 1 or 1 to many?

SELECT
	t1.TID,
	t1.am,
	t1.age,
	t1.date,
	t1.code,
	t1.Pid,
	t3.HidHcode,
	t3.Hdesc
FROM table1 t1
LEFT JOIN table2 t2 ON t2.pid = t1.pid  --is this 1 to 1 or 1 to many with t1.pid?
LEFT JOIN table3 t3 ON t3.hid = t2.hid -- is this a 1 to 1 or 1 to many with t2.hid

It is likely that there are more than 1 value in one or more tables which is producing a result set larger than you expect.

HTH.

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-19 : 11:57:13

1 to 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources