Need Help with Table Join - 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)

Need Help with Table Join

Author

Topic

puush
Starting Member

14 Posts

Posted - 2009-05-07 : 22:39:05

Hello,

I am trying to pull pull data from 2 table with this query below in SqL Server 2005. I get this error when I run the query.

Query Analyzer Error
------------------------------------------------------------------
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "tblDrumEntries.CMSPlayerID" could not be bound.
Msg 209, Level 16, State 1, Line 11
Ambiguous column name 'PromotionID'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'PromotionID'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'CMSPlayerID'.
---------------------------------------------------------

Query
-----------------------------------------------------------------
/* This query finds Points and Entries for drawings according */
/* to given date range */

SELECT PromotionID, CMSPlayerID, EntryID, Entered, GamingDate,
DailyPoints

FROM tblDrumEntries a, tblDailyPoints

WHERE tblDrumEntries.CMSPlayerID = tblDailyPoints.CMSPlayerID

AND PromotionID = '48'

AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'
------------------------------------------------------------------

Both Table have the "CMSPlayerID" field. Thanks for any help.

Michael A

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-07 : 22:43:27

[code]
SELECT PromotionID, a.CMSPlayerID, EntryID, Entered, GamingDate, DailyPoints
FROM tblDrumEntries a, tblDailyPoints
WHERE ~~tblDrumEntries~~ a.CMSPlayerID = tblDailyPoints.CMSPlayerID
AND PromotionID = '48'
AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 14:08:41

quote:

Originally posted by khtan


SELECT {a/tblDailyPoints}.PromotionID, a.CMSPlayerID, EntryID, Entered, GamingDate, DailyPoints
FROM tblDrumEntries a, tblDailyPoints
WHERE tblDrumEntries a.CMSPlayerID = tblDailyPoints.CMSPlayerID
AND {a/tblDailyPoints}.PromotionID = '48'
AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'

KH
[spoiler]Time is always against us[/spoiler]

also remember to give full qualified name for PromotionID. not sure from which table you need to retrive detail so give a or tblDailyPoints

puush
Starting Member

14 Posts

Posted - 2009-05-12 : 18:51:22

I get the error message below when I execute this script in SQL Server Management Studio.

Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near 'a'.

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 19:21:17

can you post your updated query here ?

KH
[spoiler]Time is always against us[/spoiler]

puush
Starting Member

14 Posts

Posted - 2009-05-12 : 20:05:51

/* This query finds Points and Entries for drawings according */
/* to given date range */

SELECT PromotionID, CMSPlayerID, EntryID, Entered, GamingDate,
DailyPoints

FROM tblDrumEntries a, tblDailyPoints

WHERE tblDrumEntries.CMSPlayerID = tblDailyPoints.CMSPlayerID

AND PromotionID = '48'

AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 22:18:22

if you are using table alias then you should not be referencing the table name any more.

Either you use this (without table alias)


SELECT PromotionID, tblDrumEntries.CMSPlayerID, EntryID, Entered, GamingDate,
DailyPoints

FROM tblDrumEntries, tblDailyPoints

WHERE tblDrumEntries.CMSPlayerID = tblDailyPoints.CMSPlayerID

AND PromotionID = '48'

AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'


SELECT PromotionID, a.CMSPlayerID, EntryID, Entered, GamingDate,
DailyPoints

FROM tblDrumEntries a, tblDailyPoints

WHERE a.CMSPlayerID = tblDailyPoints.CMSPlayerID

AND PromotionID = '48'

AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'

KH
[spoiler]Time is always against us[/spoiler]

puush
Starting Member

14 Posts

Posted - 2009-05-13 : 18:02:46

i'll query this and see what happens. Thanks

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

puush
Starting Member

14 Posts

Posted - 2009-05-13 : 20:10:03

when I ran this code in Sql 2005 I get this error:

error:
-------------------------------------
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblDrumEntries'.

-----------------------------------------------------------------
SELECT PromotionID, tblDrumEntries.CMSPlayerID, EntryID, Entered, GamingDate, DailyPoints

FROM tblDrumEntries, tblDailyPoints

WHERE tblDrumEntries.CMSPlayerID = tblDailyPoints.CMSPlayerID

AND PromotionID = '48'

AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'
-------------------------------------------------------------------

And I get this error when I run the other version of code:

error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblDrumEntries'.

------------------------------------------------------------------
SELECT PromotionID, a.CMSPlayerID, EntryID, Entered, GamingDate,
DailyPoints

FROM tblDrumEntries a, tblDailyPoints

WHERE a.CMSPlayerID = tblDailyPoints.CMSPlayerID

AND PromotionID = '48'

AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-13 : 20:58:06

Check if the table tblDrumEntries stil exists? Looks like it has been dropped.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 21:08:18

are you running this in the correct database ?

KH
[spoiler]Time is always against us[/spoiler]

puush
Starting Member

14 Posts

Posted - 2009-05-14 : 00:55:29

oops

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

puush
Starting Member

14 Posts

Posted - 2009-05-14 : 01:08:29

I ran "both" versions of the code in SQL 2005 on the correct database and I get this error:

-------------------------------------
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PromotionID'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'PromotionID'.
-------------------------------------

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 02:18:32

that means the column PromotionID exists in both table. Prefix with table name or the alias


SELECT a.PromotionID

OR

SELECT tblDrumEntries.PromotionID

KH
[spoiler]Time is always against us[/spoiler]

puush
Starting Member

14 Posts

Posted - 2009-05-14 : 17:20:59

I ran this version of script and I get this error.

/* This query finds Points and Entries for drawings according */
/* to given date range */

SELECT a.PromotionID, CMSPlayerID, EntryID, Entered, GamingDate,
DailyPoints

FROM tblDrumEntries a, tblDailyPoints

WHERE tblDrumEntries.CMSPlayerID = tblDailyPoints.CMSPlayerID

AND a.PromotionID = '48'

AND Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'

--------------------------------------------------------------

Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "tblDrumEntries.CMSPlayerID" could not be bound.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'CMSPlayerID'.

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 22:26:07

Can you re-read this thread from the very beginning again ? It seems that you are missing all the points

1. You either use table alias or table name to prefix the column. And not a Mixture of both
this is WRONG


SELECT a.PromotionID, .  . .
FROM   tblDrumEntries a, tblDailyPoints
WHERE  tblDrumEntries.CMSPlayerID = tblDailyPoints.CMSPlayerID

It should be this


SELECT a.PromotionID, .  . .
FROM   tblDrumEntries a, tblDailyPoints
WHERE  a.CMSPlayerID = tblDailyPoints.CMSPlayerID


SELECT tblDrumEntries .PromotionID, .  . .
FROM   tblDrumEntries, tblDailyPoints
WHERE  tblDrumEntries.CMSPlayerID = tblDailyPoints.CMSPlayerID

2. When a column name exists in more than one table, you need to prefix in with the table name or table alias
in your query, Column PromotionID and CMSPlayerID existed in both of the tables. You need to prefix it.

So either you do this with a table alias


SELECT a.PromotionID, a.CMSPlayerID, EntryID, Entered, GamingDate, DailyPoints
FROM   tblDrumEntries a, tblDailyPoints b
WHERE  a.CMSPlayerID = b.CMSPlayerID
AND    a.PromotionID = '48'
AND   Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'

OR prefix with table name


SELECT tblDrumEntries.PromotionID, a.CMSPlayerID, EntryID, Entered, GamingDate, DailyPoints
FROM   tblDrumEntries, tblDailyPoints
WHERE  tblDrumEntries.CMSPlayerID = tblDailyPoints.CMSPlayerID
AND    tblDrumEntries.PromotionID = '48'
AND   Entered BETWEEN '05/02/2009 19:00' AND '05/02/2009 22:00'

KH
[spoiler]Time is always against us[/spoiler]

puush
Starting Member

14 Posts

Posted - 2009-05-19 : 21:50:04

It Worked! I understand...Thank you Thnak you Thank you

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 23:05:51

You are welcome welcome welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources