Another Join Question - 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 2000 Forums

Transact-SQL (2000)

Another Join Question

Author

Topic

Firemaple
Starting Member

14 Posts

Posted - 2007-06-20 : 12:59:11

Hey All, I'm fairly new to t-SQL, so this may be an easy one for most of you.

I have 7 table that I want to join, So i run into alot of confusion.
Mainly here is the problem:

--------------Query------------
select a.Address, b.Name, c.orderID
from tblB as b left join tblDates on (b.currentDate = tblDates.alldates)
inner join tblA as a on (a.ID = b.ID),
(tblA LEFT JOIN tblC ON (tblA.ID = tblC.ID)
AND
(tblDates.AllDates = tblC.HTEffDate))
------------------------------

I get an error that says: "The column prefix 'tblDates' does not match with a table name or alias name used in the query." and it only happens when i add in the last line. I'm sure i am doing something wrong.. just not sure what it is.

Thanks in advance for any help!

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 13:06:55

tblDates is not in scope at that point.

I recommend that you put your JOIN conditions the other way round - the Join'd table's columns first. Generally you are trying to satisfy the PK on the Joined table, and IMHO putting the Joined Table's Columns on the left makes it easier to see if you have got everything, or left a column out!

You also have a "comma" in the middle of your Table / Join, so that is going to be satisfied only by the WHERE clause, otherwise you will get a cartesian join between the two data sets.

Moving the final "tblDates.AllDates = tblC.HTEffDate" to the WHERE clause will fix the problem, but I expect you actually need to restructure the FROM / JOIN statements instead!


select	a.Address, b.Name, c.orderID
from	tblB as b
	left join tblDates AS D
		on D.alldates = b.currentDate
	join tblA as a 
		on a.ID = b.ID
	,
	tblA AS A2
	LEFT JOIN tblC AS C 
		 ON C.ID = A2.ID
		AND C.HTEffDate = D.AllDates -- "D" is out of scope here

Kristen

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 13:12:02

Perhaps what you want is:


select	a.Address, b.Name, c.orderID
from	tblB as b
	left join tblDates AS D
		on D.alldates = b.currentDate
	join tblA as a 
		on a.ID = b.ID
	LEFT JOIN tblC AS C 
		 ON C.HTEffDate = D.AllDates
	LEFT JOIN tblA AS A2
		 ON A2.ID = C.ID

but given that "A2" in not referenced anywhere I'm don't feel very confident!

Maybe you want the OUTER JOIN to tblC AS C, but then a nested INNER JOIN to tblA AS A2 - i.e. select any [appropriate] rows from tblC AS C if they exist, but ONLY if there are also corresponding rows from tblA AS A2 ??

Kristen

Firemaple
Starting Member

14 Posts

Posted - 2007-06-20 : 13:52:42

Thanks, that does make sense, but when i remove the 'comma' it doesn't execute. Not sure if i need an AND there or not. However my big problem is this... but with about 7 tables and joins. Sorry, like i said i am fairly new to this. I have someones Access Query... which a large nested query, and I'm trying to convert it to a SQL Stored Procedure. But as far as I can tell, you can't nest queries in SQL, so it's really throwing me for a loop trying figure it all out. So now I'm trying to just recreate the statement from scratch.

Firemaple
Starting Member

14 Posts

Posted - 2007-06-20 : 15:31:46

Ok, If there is a good place for me just to read up on this, feel free to post it and i will leave you alone... Here is my problem.. with a join on 2 tables.. i understand how that works, but on more tables... i get confused. The following Query I am having problems with:

-------------
select tblAnalyst.[USW_Number], tblAnalyst.[Analyst_First_Name],
tblAnalyst.[Analyst_Last_Name], tblAnalyst.Supervisor,
tblAttendence.CurrentDate, tblAttendence.[USW_Num], tblAttendence.MinuteVarianceStored,
tblProduction.[Open], tblProduction.Closed, tblProduction.Escalated

from tblAnalyst
LEFT JOIN tblAttendence
ON (tblAnalyst.[USW_Number] = tblAttendence.[USW_Num])
LEFT join tblProduction
ON (tblAnalyst.[USW_Number] = tblProduction.ProdUserName)

where tblAttendence.CurrentDate between @START And @END
Order by USW_NUmber, CurrentDate

------------------
What i needs is One line of data for each "CurrentDate" but instead i get multiple lines for each "currentDate." That may make sense to you, but as i type this out i realize... explaining problems with TSQL is difficult.

Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources