Select Statement - 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)

Select Statement

Author

Topic

timothymannah
Starting Member

14 Posts

Posted - 2007-03-12 : 22:18:22

Below are 2 tables

Stockmaster
ST_ID       DESCRIPTION                PRICE    GR_ID
------------------------------------------------------
SD001	BD Safeclip Device              12.73	NULL
SD002	BD Sharps Collector 1.4L        4.19	GR05
SD003	BD Sharps Collector 3.1L        6.1	GR05
SD004	Needle Safe-Desk Top            3.73	NULL
SD005	Sharps Collector 1L             4.55	GR06
SD006	Sharps Collector  1.7L          5.19	GR07
SD007	Sharps Collector  4L - RE4LR  	6.82	GR07
SD008	Sharps Collector 4L           	6.91	GR07
SD009	Sharps Collector 10L - RE10LS	17.46	GR07
SD010	Sharps Collector 16.5L- RE15LS  20.73	NULL
SD011	Sharps Collector 0.5L         	3.73	GR06


Groups
GR_ID         Name
-----------------------------------------------------------------
GR05         BD Sharps
GR06         IDC Sharps Collector (Round)
GR07	     IDC Sharps Collector (Square)

I want to return these:

ST_ID   DESCRIPTION           PRICE     GR_ID       Name
--------------------------------------------------------------------
SD001	BD Safeclip Device          12.7       NULL       NULL  
SD002	BD Sharps Collector 1.4L    4.1        GR05       BD Sharps
SD004	Needle Safe-Desk Top        3.73       NULL       NULL
SD005	Sharps Collector 1L         4.55       GR06       IDC Sharps Collector (Round)
SD006	Sharps Collector  1.7L      5.19       GR07       IDC Sharps Collector (Square)

What I am trying to achieve is to return a set of results of all values in both tables where GR_ID is "NULL" AND the first unique instance of records that have equal values for GR_ID.

I have a solution that only half works. It returns the values in stockmaster but nothing from groups. LIKe THIS


ST_ID   DESCRIPTION           PRICE     GR_ID      
----------------------------------------------------
SD001	BD Safeclip Device          12.7       NULL     
SD002	BD Sharps Collector 1.4L    4.1        GR05     
SD004	Needle Safe-Desk Top        3.73       NULL  
SD005	Sharps Collector 1L         4.55       GR06     
SD006	Sharps Collector  1.7L      5.19       GR07


SELECT     StockMaster.ST_ID, StockMaster.DESCRIPTION , StockMaster.PRICE, StockMaster.GR_ID, 
FROM         StockMaster INNER JOIN
          (SELECT     MIN(StockCode) AS StockCode
           FROM          StockMaster AS StockMaster_1
           WHERE      (StockCode LIKE 'SD%') AND (GroupCode IS NOT NULL)
           GROUP BY GroupCode) AS tblMin ON StockMaster.StockCode = tblMin.StockCode
UNION
SELECT     ST_ID, DESCRIPTION , PRICE, GR_ID
FROM         StockMaster AS StockMaster_2
WHERE     (ST_ID LIKE 'SD%') AND (GR_ID IS NULL)
ORDER BY StockMaster.GroupCode DESC

Any one have an idea?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 23:23:20

Did you missed out SD010 ?


declare @Stockmaster table
(
	ST_ID       	varchar(10),
	[DESCRIPTION]	varchar(30),
	PRICE    	decimal(10,2),
	GR_ID		varchar(10)
)
insert into @Stockmaster
select	'SD001',	'BD Safeclip Device',              	12.73,	NULL	union all
select	'SD002',	'BD Sharps Collector 1.4L',        	 4.19,	'GR05'	union all
select	'SD003',	'BD Sharps Collector 3.1L',        	 6.1,	'GR05'	union all
select	'SD004',	'Needle Safe-Desk Top',            	 3.73,	NULL	union all
select	'SD005',	'Sharps Collector 1L',             	 4.55,	'GR06'	union all
select	'SD006',	'Sharps Collector  1.7L',          	 5.19,	'GR07'	union all
select	'SD007',	'Sharps Collector  4L - RE4LR',  	 6.82,	'GR07'	union all
select	'SD008',	'Sharps Collector 4L',           	 6.91,	'GR07'	union all
select	'SD009',	'Sharps Collector 10L - RE10LS',	17.46,	'GR07'	union all
select	'SD010',	'Sharps Collector 16.5L- RE15LS',  	20.73,	NULL	union all
select	'SD011',	'Sharps Collector 0.5L',         	 3.73,	'GR06'

declare @Groups table
(
	GR_ID	varchar(10),
	[Name]	varchar(30)
)
insert into @Groups
select	'GR05', 'BD Sharps'			union all
select	'GR06', 'IDC Sharps Collector (Round)'	union all
select	'GR07',	'IDC Sharps Collector (Square)'

select	s.ST_ID, s.[DESCRIPTION], s.PRICE, s.GR_ID, g.[Name]
from	@Stockmaster s 
	left join @Groups g
	on	s.GR_ID	= g.GR_ID
where	s.GR_ID	is null
or	s.ST_ID	= (select min(ST_ID) from @Stockmaster x
			where	x.GR_ID	= s.GR_ID)

/*
ST_ID      DESCRIPTION                    PRICE        GR_ID      Name                           
---------- ------------------------------ ------------ ---------- ------------------------------ 
SD001      BD Safeclip Device             12.73        NULL       NULL
SD002      BD Sharps Collector 1.4L       4.19         GR05       BD Sharps
SD004      Needle Safe-Desk Top           3.73         NULL       NULL
SD005      Sharps Collector 1L            4.55         GR06       IDC Sharps Collector (Round)
SD006      Sharps Collector  1.7L         5.19         GR07       IDC Sharps Collector (Square)
SD010      Sharps Collector 16.5L- RE15LS 20.73        NULL       NULL
*/

timothymannah
Starting Member

14 Posts

Posted - 2007-03-12 : 23:36:03

I gave you just one example of the tables I have over 20 to do so I really cant hardcode the solution into a store procedure.

This is what I am using now

SELECT     s.StockCode, s.StockName1, s.StockName2, s.PD1DateCreatedBrief, s.PD2DateCreatedDetailed, s.PD3DateCreatedFeatures, 
                      s.ProductDescriptionBrief, s.ProductDescriptionDetailed, s.ProductDescriptionFeatures, s.StockCategory, s.StockUnit, s.StockSellingPrice1, 
                      s.StockSellingPrice2, s.StockSellingPrice3, s.StockSellingPrice4, s.StockSellingPrice5, s.StockSellingPrice6, s.SpecialFlag, s.SpecialStartDate, 
                      s.SpecialEndDate, s.NewProductFlag, s.NewProductStartDate, s.NewProductEndDate, s.Image1Small, s.Image2Large, s.NonOnlineProduct, 
                      s.DeleteFlag, s.GroupCode, s.GroupInclude, s.GroupExclude, s.CategoryInclude, s.CategoryExclude, s.ClassInclude, s.ClassExclude, s.StockWeight, 
                      s.Imported
FROM         StockMaster AS s INNER JOIN
                          (SELECT     GroupCode, MIN(StockCode) AS min_ST_ID
                            FROM          StockMaster
                            WHERE      (GroupCode IS NOT NULL)
                            GROUP BY GroupCode) AS m ON s.StockCode = m.min_ST_ID
WHERE     (s.StockCode LIKE 'SD%')
UNION ALL
SELECT     StockCode, StockName1, StockName2, PD1DateCreatedBrief, PD2DateCreatedDetailed, PD3DateCreatedFeatures, ProductDescriptionBrief, 
                      ProductDescriptionDetailed, ProductDescriptionFeatures, StockCategory, StockUnit, StockSellingPrice1, StockSellingPrice2, StockSellingPrice3, 
                      StockSellingPrice4, StockSellingPrice5, StockSellingPrice6, SpecialFlag, SpecialStartDate, SpecialEndDate, NewProductFlag, NewProductStartDate, 
                      NewProductEndDate, Image1Small, Image2Large, NonOnlineProduct, DeleteFlag, GroupCode, GroupInclude, GroupExclude, CategoryInclude, 
                      CategoryExclude, ClassInclude, ClassExclude, StockWeight, Imported
FROM         StockMaster AS s
WHERE     (StockCode LIKE 'SD%') AND (GroupCode IS NULL)

I just want to add Groups.GR_ID to the selection.

Is there any way to edit the above query to include the field Groups.GR_ID and have the final selection of


ST_ID   DESCRIPTION           PRICE     GR_ID       Name
--------------------------------------------------------------------
SD001	BD Safeclip Device          12.7       NULL       NULL  
SD002	BD Sharps Collector 1.4L    4.1        GR05       BD Sharps
SD004	Needle Safe-Desk Top        3.73       NULL       NULL
SD005	Sharps Collector 1L         4.55       GR06       IDC Sharps Collector (Round)
SD006	Sharps Collector  1.7L      5.19       GR07       IDC Sharps Collector (Square)
SD007   BLAH                        BLAH       BLAH       BLAH

the value of SD is changes in my code.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 23:39:32

have you tried the query ?


select	s.ST_ID, s.[DESCRIPTION], s.PRICE, s.GR_ID, g.[Name]
from	Stockmaster s 
	left join Groups g
	on	s.GR_ID	= g.GR_ID
where	s.GR_ID	is null
or	s.ST_ID	= (select min(ST_ID) from Stockmaster x
			where	x.GR_ID	= s.GR_ID)

What do you mean by hardcode the solution in stored procedure ?

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-12 : 23:51:06

[code]
declare @Stockmaster table
(
ST_ID varchar(10),
[DESCRIPTION] varchar(30),
PRICE decimal(10,2),
GR_ID varchar(10)
)
insert into @Stockmaster
select 'SD001', 'BD Safeclip Device', 12.73, NULL union all
select 'SD002', 'BD Sharps Collector 1.4L', 4.19, 'GR05' union all
select 'SD003', 'BD Sharps Collector 3.1L', 6.1, 'GR05' union all
select 'SD004', 'Needle Safe-Desk Top', 3.73, NULL union all
select 'SD005', 'Sharps Collector 1L', 4.55, 'GR06' union all
select 'SD006', 'Sharps Collector 1.7L', 5.19, 'GR07' union all
select 'SD007', 'Sharps Collector 4L - RE4LR', 6.82, 'GR07' union all
select 'SD008', 'Sharps Collector 4L', 6.91, 'GR07' union all
select 'SD009', 'Sharps Collector 10L - RE10LS', 17.46, 'GR07' union all
select 'SD010', 'Sharps Collector 16.5L- RE15LS', 20.73, NULL union all
select 'SD011', 'Sharps Collector 0.5L', 3.73, 'GR06'

declare @Groups table
(
GR_ID varchar(10),
[Name] varchar(30)
)
insert into @Groups
select 'GR05', 'BD Sharps' union all
select 'GR06', 'IDC Sharps Collector (Round)' union all
select 'GR07', 'IDC Sharps Collector (Square)'

select s.*,g.name
from @Stockmaster s
inner join
(select GR_ID,ST_ID from @Stockmaster where GR_ID IS NULL
UNION ALL
select GR_ID,MIN(ST_ID) from @Stockmaster where GR_ID IS NOT NULL
Group by GR_ID) A on s.ST_ID=A.ST_ID
LEFT JOIN @Groups g on s.GR_ID=g.GR_ID

[/code]

mk_garg

timothymannah
Starting Member

14 Posts

Posted - 2007-03-14 : 17:37:45

That seems to work fine. The example table i gave you is much smaller than what i'm really using. I need the "Where" clause to give me the freedom to set record parameters.

Is there a way I can make your query:


select	s.ST_ID, s.[DESCRIPTION], s.PRICE, s.GR_ID, g.[Name]
from	Stockmaster s 
	left join Groups g
	on	s.GR_ID	= g.GR_ID
where	s.GR_ID	is null
or	s.ST_ID	= (select min(ST_ID) from Stockmaster x
			where	x.GR_ID	= s.GR_ID)

To give me the option of adding criteria in the where clause to also include a selection of ST_ID's between a range. I am working with 1000 records and they have stockcodes that can be chosen easily if i can add


WHERE (s.ST.ID BETWEEN 'CH001 ' AND 'CH006')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 18:31:47

[code]-- prepare sample data
declare @Stockmaster table (ST_ID varchar(10), [DESCRIPTION] varchar(30), PRICE decimal(10,2), GR_ID varchar(10))

insert @Stockmaster
select 'SD001', 'BD Safeclip Device', 12.73, NULL union all
select 'SD002', 'BD Sharps Collector 1.4L', 4.19, 'GR05' union all
select 'SD003', 'BD Sharps Collector 3.1L', 6.1, 'GR05' union all
select 'SD004', 'Needle Safe-Desk Top', 3.73, NULL union all
select 'SD005', 'Sharps Collector 1L', 4.55, 'GR06' union all
select 'SD006', 'Sharps Collector 1.7L', 5.19, 'GR07' union all
select 'SD007', 'Sharps Collector 4L - RE4LR', 6.82, 'GR07' union all
select 'SD008', 'Sharps Collector 4L', 6.91, 'GR07' union all
select 'SD009', 'Sharps Collector 10L - RE10LS', 17.46, 'GR07' union all
select 'SD010', 'Sharps Collector 16.5L- RE15LS', 20.73, NULL union all
select 'SD011', 'Sharps Collector 0.5L', 3.73, 'GR06'

declare @Groups table (GR_ID varchar(10), [Name] varchar(30))

insert @Groups
select 'GR05', 'BD Sharps' union all
select 'GR06', 'IDC Sharps Collector (Round)' union all
select 'GR07', 'IDC Sharps Collector (Square)'

-- show the expected output
select m.st_id,
m.[description],
m.price,
m.gr_id,
g.[name]
from @stockmaster as m
left join @groups as g on g.gr_id = m.gr_id
left join (
select gr_id,
min(st_id) AS st_id
from @stockmaster
group by gr_id
) as x on x.st_id = m.st_id
where nullif(m.gr_id, x.gr_id) is null
and m.ST_ID BETWEEN 'SD001' AND 'SD006' -- add additional filtering here[/code]

Peter Larsson
Helsingborg, Sweden

timothymannah
Starting Member

14 Posts

Posted - 2007-03-14 : 18:50:21

This seems to work fine at first glance.....thanks....I'll try it out further........Thank you all for your efforts

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 21:07:15

received from email

quote:
That seems to work fine. The example table i gave you is much smaller than what i'm really using. I need the "Where" clause to give me the freedom to set record parameters.

Is there a way I can make your query:

select s.ST_ID, s.[DESCRIPTION], s.PRICE, s.GR_ID, g.[Name]
from Stockmaster s
left join Groups g
on s.GR_ID = g.GR_ID
where s.GR_ID is null
or s.ST_ID = (select min(ST_ID) from Stockmaster x
where x.GR_ID = s.GR_ID)

To give me the option of adding criteria in the where clause to also include a selection of ST_ID's between a range. I am working with 1000 records and they have stockcodes that can be chosen easily if i can add

WHERE (s.ST.ID BETWEEN 'CH001 ' AND 'CH006')

Yes


select	s.ST_ID, s.[DESCRIPTION], s.PRICE, s.GR_ID, g.[Name]
from	Stockmaster s 
	left join Groups g
	on	s.GR_ID	= g.GR_ID
where	(
        s.GR_ID	is null
or	s.ST_ID	= (select min(ST_ID) from Stockmaster x
			where	x.GR_ID	= s.GR_ID)
        )
and     (s.ST.ID BETWEEN 'CH001 ' AND 'CH006')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources