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

General SQL Server Forums

New to SQL Server Programming

Problem with query

Author

Topic

vasko102
Starting Member

6 Posts

Posted - 2010-11-15 : 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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-15 : 05:44:54

Hint

table1 left join table2

Madhivanan

Failing to plan is Planning to fail

vasko102
Starting Member

6 Posts

Posted - 2010-11-15 : 05:58:55

I know about the left join :) but 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

quote:
Originally posted by madhivanan

Hint

table1 left join table2

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-15 : 06:03:36

Post the code you used

Madhivanan

Failing to plan is Planning to fail

vasko102
Starting Member

6 Posts

Posted - 2010-11-15 : 06:11:40

SELECT P.Name, A.Value
FROM Table1 P LEFT JOIN Table2 a on P.ID= A.table1_ID where A.ID = 1

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-11-15 : 06:23:15

u have to remove the where condition from your query.bcoz when you apply where condition the query engine looks for the matching records for the id that you pass in your where condition.in your query you have passed id=1 in the where clause, so you will be getting only the matching records with id=1

vasko102
Starting Member

6 Posts

Posted - 2010-11-15 : 06:32:12

but if i remove the where condition how can i get my result?

quote:
Originally posted by ahmeds08

u have to remove the where condition from your query.bcoz when you apply where condition the query engine looks for the matching records for the id that you pass in your where condition.in your query you have passed id=1 in the where clause, so you will be getting only the matching records with id=1

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-11-15 : 06:51:46

when you want to get all the records whats the fun of passing where condition.you pass the where condition when you want to get the records for a particular id.just remove the where condition and check the query.is it giving all the records or not.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-15 : 07:37:35

Instead of where use AND

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources