Inner Join 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

SQL Server 2005 Forums

Transact-SQL (2005)

Inner Join help

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2008-08-12 : 10:13:05

Almost have this working I know but need a little input.

I have 3 tables and one parameter being passed into the sp.
I get the userId (num) and select from 2 tables all the rows.
Now one of these tables also has userId's and I need to take that id and hit another table and get all the info for that user...

SELECT a.fldNum,
a.fldDate,
a.fldIdOwned,
b.fldCurrentValue,
b.fldCurrentCredits,
c.email

FROM dbo.tblOne a,
tblTwo b
WHERE a.fldUserId = @pUserId
INNER JOIN tblInfo c ON c.fldNum = a.fldUserId

Suggestions?

Thanks,

Zath

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-12 : 11:45:40

No relationship between a and b ?

This would work if you *want* the Cartesian product.


SELECT
	a.fldNum,
	a.fldDate,
	a.fldIdOwned,
	b.fldCurrentValue,
	b.fldCurrentCredits,
	c.email
FROM
	dbo.tblOne a
	INNER JOIN tblInfo c ON c.[fldNum] = a.[fldUserId]
	CROSS JOIN tblTwo b
WHERE
	a.fldUserId = @pUserId

-------------
Charlie

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2008-08-12 : 12:38:03

Thanks. that looks like it would work. And no, there is no relationship between table a and b except both have a constraint to table c, the userId.

Zath

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 12:40:11

quote:
Originally posted by Zath

Thanks. that looks like it would work. And no, there is no relationship between table a and b except both have a constraint to table c, the userId.

Zath

then it might be this

SELECT
	a.fldNum,
	a.fldDate,
	a.fldIdOwned,
	b.fldCurrentValue,
	b.fldCurrentCredits,
	c.email
FROM
	dbo.tblOne a
	INNER JOIN tblInfo c ON c.[fldNum] = a.[fldUserId]
	INNER JOIN tblTwo b ON b.UserId=c.UserId
WHERE
	a.fldUserId = @pUserId

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-12 : 16:47:54

*sigh* no-one ever wants the Cartesian product it seems.....

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 00:30:11

quote:
Originally posted by Transact Charlie

*sigh* no-one ever wants the Cartesian product it seems.....

-------------
Charlie

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2008-08-13 : 08:33:24

quote:

then it might be this

SELECT
	a.fldNum,
	a.fldDate,
	a.fldIdOwned,
	b.fldCurrentValue,
	b.fldCurrentCredits,
	c.email
FROM
	dbo.tblOne a
	INNER JOIN tblInfo c ON c.[fldNum] = a.[fldUserId]
	INNER JOIN tblTwo b ON b.UserId=c.UserId
WHERE
	a.fldUserId = @pUserId

Yes thanks! that worked better for what I needed and was able to add another table to it easily.

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources