Help in Query - modified now - 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)

Help in Query - modified now

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-05-22 : 01:28:46

SELECT distinct table1.Continent,table1.Culture,table1.LastName,table1.Gender,table3.NameBreakdown
FROM table1 INNER JOIN
table3 ON table1.Culture = table3.LastNameCode LEFT OUTER JOIN
table2 ON table1.Name = table2.Name order by Name

I have 3 tables table1, table2, table3. I want to retrive all columns and all rows data from table1,
NameBreakdown column from table3. I need to match the lastname column from table1 to lastname column from table2
and also need to match the culture column from table1 to culture column from table3. In displaying results, if there is no match then the namebreakdown column should display 'not found' for table1 data.

But now with above query it displaying some data...
G. Satish

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 01:45:05

are you sure the current query is working ? The table2 appear twice in the query without aliasing

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 03:14:39

[code]
SELECT distinct table1.Continent,table1.Culture,table1.LastName,table1.Gender,
NameBreakdown = case when table2.Name is null
then 'not found'
else table3.NameBreakdown
end
FROM table1 INNER JOIN
table3 ON table1.Culture = table3.LastNameCode LEFT OUTER JOIN
table2 ON table1.Name = table2.Name
order by Name
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:17:28

may be this?


SELECT distinct table1.Continent,table1.Culture,table1.LastName,table1.Gender,COALESCE(table3.NameBreakdown,'Not Found')
FROM table1 
LEFT OUTER JOIN table3 ON table1.Culture = table3.LastNameCode 
LEFT OUTER JOIN table2 ON table1.Name = table2.Name 
order by Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources