help - 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

Author

Topic

vguptais
Starting Member

8 Posts

Posted - 2008-05-15 : 08:43:38

Hi,

Please help me in this. I have 3 tables

Table 1

orderno Customer Value
11083 12312 1254.23
11084 14521 12542.35
11086 12548 1254.20
11089 14586 1452.51

Table 2

orderno Textno
11084 15425
11084 15421
11084 15426
11086 15441
11089 12548

Table3

Textno Text
15425 the material is to be delivered by DHL
15421 XXX special discount
15426 price 52.32 net
15441 XXX quantity discount

Now i need the result as

Orderno customer value Text

One order can have more than one text but only one or no text starts by XXX. if there is corresponding text starts by XXX i need that text in the resulting table if not it should just put ###.

The problem is that I need all the records of the first table and no record should be duplicated.

Please help.
Vikrant Gupta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 10:04:15

[code]SELECT t1.Orderno,t1.customer,t1.value,coalesce(t3.Text,'###') as text
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.orderno=t1.orderno
LEFT JOIN Table3 t3
ON t3.Textno=t2.Textno
AND LEFT(t3.Text,3)='XXX'[/code]

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-05-15 : 10:20:35

Hi friend...
chk the below query,
i think it would satisfy u...
if u not means tell me...

select t1.Orderno ,t1.Customer,t1.value,coalesce(t3.Text,'--No Text--') from
table1 t1 left join table2 t2 on
t1.Orderno=t2.Orderno left outer join
table3 t3 on
t3.TextNo=t2.TextNo

cool...,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources