is sub select the solution? - 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 2000 Forums

SQL Server Development (2000)

is sub select the solution?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-10-04 : 10:36:42

I am stuck in the middle of this select statement, and would like to ask for help. I am sure to many of you, there must be more than one solution out there, but I just could not get it.

By the way, I fully understand both of my tables here are not normalized to the appropriate level. I just have to live with them.

CREATE TABLE [dbo].[test_order_header] (
[head_order_nbr] [varchar] (20),
[account_number] [varchar] (20) ,
[head_date] [datetime] NULL ,
[head_order_status] [varchar] (50),
[head_conds] [text],
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[test_line_dtl] (
[header_nbr] [varchar] (20) ,
[line_number] [int] NOT NULL ,
[line_enditem] [varchar] (40) ,
[line_description] [varchar] (40)
)
GO

insert into test_order_header values ('123','555','2005-09-01','shppied','***shipping instruction follow: **credit info')
insert into test_order_header values ('456','666','2005-08-01','canceled','~C: customer service note:')
insert into test_order_header values ('789','777','2005-07-01','unkown','')
insert into test_order_header values ('321','888','2005-06-01','completed','Congrat, we have a happy customer!')

insert into test_line_dtl values ('123',11,'Print','On bothe side')
insert into test_line_dtl values ('123',12,'Cut','use 2" knife')
insert into test_line_dtl values ('123',13,'Die','xyz')
insert into test_line_dtl values ('456',14,'mill','On both side')
insert into test_line_dtl values ('456',15,'Print','On one side')
insert into test_line_dtl values ('789',16,'','')
insert into test_line_dtl values ('321',17,'QA','super quality')

This select gives me every fields, but some rows are unwanted:
select h.head_order_nbr, d.line_number,h.account_number, d.line_enditem, d.line_description, h.head_conds
from test_order_header h join test_line_dtl d on h.head_order_nbr=d.header_nbr

But if I added
where d.line_enditem ='print'
It only returned two rows, not enough.

Next one is close but with duplicate.

select h.head_order_nbr, d.line_number,h.account_number, d.line_enditem,
case d.line_enditem when 'print' then d.line_description else '' end, h.head_conds
from test_order_header h join test_line_dtl d on h.head_order_nbr=d.header_nbr

However, when I added Distinct to above, it errored out as:
The text, ntext, or image data type cannot be selected as DISTINCT.

Here is what they have in mind
123 11 555 On both side ***shipping instruction follow: **credit info
456 15 666 On one side ~C: customer service note:
789 16 777
321 17 888 Congrat, we have a happy customer!

To clean up,
drop table test_order_header
drop table test_line_dtl

Thanks!

hommer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 10:46:35

Try this

select h.head_order_nbr, d.line_number,h.account_number, d.line_enditem, d.line_description, h.head_conds
from test_order_header h join
(Select head_order_nbr, min(line_number) as line_number from test_line_dtl group by head_order_nbr) d
on h.head_order_nbr=d.header_nbr where h.line_number=d.line_number

Madhivanan

Failing to plan is Planning to fail

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-10-04 : 11:13:45

Thanks for your prompt reply Madhivanan!

But maybe my example data misled you. I did not see how min(line_number) works here.

I modified your post in two places ( join on header_nbr instead of line_number, and included two columns in sub select). But it did not return the right result.

select h.head_order_nbr, d.line_number,h.account_number, d.line_enditem, d.line_description, h.head_conds
from test_order_header h join
(Select header_nbr, min(line_number) as line_number, line_enditem, line_description from test_line_dtl
group by header_nbr, line_enditem, line_description) d
on h.head_order_nbr=d.header_nbr where h.head_order_nbr=d.header_nbr

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-10-04 : 11:25:23

Or this one will show why min() is not working.

select h.head_order_nbr, d.line_number,h.account_number, h.head_conds
from test_order_header h join
(Select header_nbr, min(line_number) as line_number from test_line_dtl
group by header_nbr) d
on h.head_order_nbr=d.header_nbr where h.head_order_nbr=d.header_nbr

Notice, order 456 returned order detail line 14 instead of 15, which is the one should be included.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-10-04 : 12:16:59

Or in plain English,
I want every thing from parent table, but only those fields from child table when a none key field in child table has a specific value.
And, when the child table has nothing to return, I don't want parent table to return duplicate records for each child record.
Maybe our users have asked too much. But I couldn't help asking the question before I say no to them.

I finally got this ugly union select works. Don't know if there is more elegant way.

select h.head_order_nbr, d.line_number,h.account_number, d.line_enditem, d.line_description, h.head_conds
from test_order_header h join test_line_dtl d on h.head_order_nbr=d.header_nbr
where d.line_enditem ='print'
union all
select h.head_order_nbr, d.line_number,h.account_number, '' as line_enditem,
'' as line_description , h.head_conds
from test_order_header h join test_line_dtl d on h.head_order_nbr=d.header_nbr
where d.line_enditem <>'print' and h.head_order_nbr not in (
select h.head_order_nbr
from test_order_header h join test_line_dtl d on h.head_order_nbr=d.header_nbr
where d.line_enditem ='print')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources