two tables 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

General SQL Server Forums

New to SQL Server Programming

two tables question

Author

Topic

jokxx
Starting Member

17 Posts

Posted - 2007-10-02 : 05:00:11

i've got an sql question. i got two tables:
1)contains two columns : user_id, username
2)contains three columns : from_id, to_id, message

i want to use a sql statment that returns a table with three columns:
from (contains username where from_id=user_id),to (..like from) and the message.

...i guess you understand what i want. could someone please tell me what sql statement i'm suppose to use (i tried inner/outer join but i can't make it work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-02 : 05:20:49

SELECT s.UserName, r.UserName, m.Message
FROM Table2 AS m
INNER JOIN Table1 AS s ON s.User_ID = m.From_ID
INNER JOIN Table1 AS r ON s.User_ID = m.To_ID

E 12°55'05.25"
N 56°04'39.16"

jokxx
Starting Member

17 Posts

Posted - 2007-10-02 : 05:49:41

thank you for the quick response.
but i get an error now :
"Syntax error(missing operator) in query expression 's.UserID=m.From.ID INNER JOIN users AS r ON s.User_ID = m.To_ID'. "

any ideas? am i missing something?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-02 : 06:05:11

's.UserID=m.From.ID INNER JOIN ....' shouldn't that be From_ID ??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-02 : 06:09:37

SELECT s.UserName, r.UserName, m.Message
FROM Table2 AS m
INNER JOIN Table1 AS s ON s.User_ID = m.From_ID
INNER JOIN Table1 AS r ON r.User_ID = m.To_ID

E 12°55'05.25"
N 56°04'39.16"

jokxx
Starting Member

17 Posts

Posted - 2007-10-02 : 08:36:32

i still get that error. am i suppose to make more settings?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-02 : 08:38:28

can you please post the query exactly as you've executed it?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-02 : 08:39:57

Jokxx, can you post the current error? It can't be exactly the same error because Peso changed a few things.

Future guru in the making.

jokxx
Starting Member

17 Posts

Posted - 2007-10-02 : 08:41:05

i get type mismatch in expression

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 08:43:12

Sounds like Access, rather than SQL Server, to me ...

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-02 : 08:44:10

Jokxx, what are the data types for the columns in each table?

Future guru in the making.

jokxx
Starting Member

17 Posts

Posted - 2007-10-02 : 08:46:36

that's what i entered: (using vs2005)
SELECT s.UserName, r.UserName AS Expr1, m.Message
FROM ((Table2 m INNER JOIN
Table1 s ON s.User_ID = m.From_ID) INNER JOIN
Table1 r ON r.User_ID = m.To_ID)

now i get "type missmatch in expression"

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 08:50:49

No error here:


declare @Table1 table
(
	UserName varchar(10),
	User_ID int
)
declare @Table2 table
(
	Message varchar(10),
	To_ID int,
	From_ID int
)

SELECT s.UserName, r.UserName AS Expr1, m.Message
FROM ((@Table2 m INNER JOIN
@Table1 s ON s.User_ID = m.From_ID) INNER JOIN
@Table1 r ON r.User_ID = m.To_ID)

Kristen

jokxx
Starting Member

17 Posts

Posted - 2007-10-02 : 09:00:16

i do use access but the statements suppose to be the same..aren't they?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-02 : 09:05:56

quote:
Originally posted by jokxx

i do use access but the statements suppose to be the same..aren't they?

No they are not the same.

http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

Future guru in the making.

jokxx
Starting Member

17 Posts

Posted - 2007-10-02 : 09:10:18

omg i problem was so stupid. one of the fields was declared as char (please don't kill me :) ) . thanks for everyone i never got so many responses so quickly. u people are amazing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources