Show ALL rows 1for 1 - 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

Show ALL rows 1for 1

Author

Topic

spehe
Starting Member

1 Post

Posted - 2011-03-08 : 04:57:08

The code

select [No_],[Cost price]
from [Material]
where [No_]in (
63560113430,
63560113430,
63560113430,
63560113430,
63560113430)

will only return one row even though I send in 5 articles. This is probably becuase the 5 articles are the same. But if I want SQL to return one row for each article regardless if it is the same or not, how do I do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-08 : 05:10:54

put the article in a table variable or temp table and INNER JOIN to the Material table


declare @articles table
(
    article int
)

insert into @articles select 63560113430
insert into @articles select 63560113430
insert into @articles select 63560113430
insert into @articles select 63560113430
insert into @articles select 63560113430

select [No_],[Cost price]
from   [Material] m
       inner join @articles a on m.[No_] = a.article

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

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-03-08 : 05:11:49

Try this:


SELECT M.[No_], [Cost price]
  FROM [Material] AS M
       INNER JOIN
       (VALUES(63560113430),
              (63560113430),
              (63560113430),
              (63560113430),
              (63560113430)) AS N([No_])
       ON N.[No_] = M.[No_]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-08 : 09:38:22

quote:

Originally posted by malpashaa

Try this:


SELECT M.[No_], [Cost price]
  FROM [Material] AS M
       INNER JOIN
       (VALUES(63560113430),
              (63560113430),
              (63560113430),
              (63560113430),
              (63560113430)) AS N([No_])
       ON N.[No_] = M.[No_]

Is this syntax valid? Never seen it before...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-08 : 10:15:26

Why do you need this behaviour?
In SQL it looks senseless.
If this is for display in an application needed then do the repeating in your front end.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-10 : 10:26:08

quote:
Originally posted by Lumbago

quote:
Originally posted by malpashaa

Try this:

SELECT M.[No_], [Cost price]
FROM [Material] AS M
INNER JOIN
(VALUES(63560113430),
(63560113430),
(63560113430),
(63560113430),
(63560113430)) AS N([No_])
ON N.[No_] = M.[No_]
Is this syntax valid? Never seen it before...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

It is valid in SQL Server 2008. Refer this for more examples
http://beyondrelational.com/blogs/madhivanan/archive/2010/08/02/values-clause-in-sql-server-2008.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources