Simple 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 2005 Forums

Transact-SQL (2005)

Simple join question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-06-02 : 11:57:48

Table 1
id
1
2
2

Table 2
id name
1 alpha
2 beta

I want this result

id name
1 alpha
2 beta
2 beta

I've tried this

SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN table2 t2 on t1.id = t2.id

This gives

id name
1 alpha
2 beta
2 NULL

What did I do wrong?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:01:39

[code]SELECT t2.id,t2.name
FROM Table2 t2
JOIN Table1 t1
ON t1.id=t2.id
[/code]

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-06-02 : 12:03:58

ummm... assuming that the id column is an int in both tables, that wouldn't produce that result set with that query. As written, you'd get:

id id name
1  1  alpha
2  2  beta
2  2  beta

I think you want:

SELECT t1.id, t2.name FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id

If you are getting something different, the data in your tables isn't what you think it is. Perhaps one or both of your id columns is a CHAR type and one has a trailing space or something.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources