Problem with query - 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 2008 Forums

Transact-SQL (2008)

Problem with query

Author

Topic

vasko102
Starting Member

6 Posts

Posted - 2010-11-15 : 06:41:16

Posted - 11/15/2010 : 05:37:21
Hi guys,

I have problem with my query. I have 2 tables, table 1 looks like this:

Table1
ID int,
Name string

and Table2 looks like this:

Table2
ID int,
table1_ID int,
Value int

My query should search for a record with some ID from table 2 but i wanna have all records from table 1 something like this:

Select table1.Name, table2.Value From..... Where table2.ID = something

Name-----------------Value
Something1------------1
Something2------------3
Something3------------16
Something4------------0(Value is null)
Something5------------0(Value is null)
Something6------------7

I cant use left join couse the problem is that with left join i only get the columns where i have values. For exemple if i have this records in table 2:

ID = 1, table1_ID = 1, Value = 100
ID = 1, table1_ID = 2, Value = 3
ID = 1, table1_ID = 3, Value = 16
ID = 1, table1_ID = 6, Value = 7

I'll get only 4 records and i want to have 6 records and the result to be like this:

Name-----------------Value
Something1------------1
Something2------------3
Something3------------16
Something4------------0(Value is null)
Something5------------0(Value is null)
Something6------------7

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-11-15 : 06:44:31

Select table1.Name, table2.Value From
Table1
LEFT JOIN
Table2
ON Table1.ID = Table2.table1_ID
AND table2.ID = something