Is Join really what I want? - 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

Development Tools

Other Development Tools

Is Join really what I want?

Author

Topic

mswantek
Starting Member

3 Posts

Posted - 2008-09-29 : 16:31:16

Help.....
First... sorry for the long post...
As I learn more.... I realize how much I still need to learn.!!

I am building some Web based apps that pull data from MS SQL 2000.

I am using two tables....

One is TagList and the other is incomingtrans....

Taglist is a table that has a sequential number as the PK and contains information regarding properties. incomingtrans tracks each request that comes in using the sequential number from TagList.

TagList..... Table

ID........Description
2000...... Blue
2001...... Green
2002...... yellow
2003...... purple
2004...... black

incomingtrans

ID....... tagID......sequence
1........ 2001...... 01
2........ 2001.......02
3........ 2000.......01
4........ 2002.......02
5........ 2002.......03

I want to show a table with a repeating region that will list all of the data in the incomingtrans table. Included with that, I want to show the description of the tagID.
When I do this sql query, I get the results in SQL Query Analyzer but not on the web page...
What I want to see in the resulting table is this....

Tag#.......Property Description.........Sequence
2000..............Blue....................01
2000..............Blue....................02
2001..............Green...................01
2002..............yellow..................01
2002..............yellow..................02

Using this SQL.....

SELECT TagList.description as tags, incomingtrans.tagID 
FROM incomingtrans 
INNER JOIN TagList
on incomingtrans.tagID = TagList.ID

What I get.... is this....
Tag#.......Property Description.........Sequence
2000..............yellow..................01
2000..............yellow..................02
2001..............yellow..................01
2002..............yellow..................01
2002..............yellow..................02

I get the same Property description whatever the Tag# is......But again... when I run this in the Query Analyzer it matches them up fine.... what am I doing wrong???

Thanks.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 00:48:39

Can you explain how you got 2 records in result for 2000? i can see only a single record in incomingtrans with tagID 2000.

CodesMyBusiness
Starting Member

9 Posts

Posted - 2008-09-30 : 08:48:19

I think you need to switch your condition in the ON part:

SELECT
  TagList.description as "tags",
  IncomingTrans.tagID
FROM incomingtrans
  INNER JOIN TagList
  ON TagList.ID = IncomingTrans.TagID
--ON incomingtrans.tagID = TagList.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources