is UNION the answer - 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

Transact-SQL (2000)

is UNION the answer

Author

Topic

dand

13 Posts

Posted - 2005-09-20 : 15:30:58

I want to show a list of farmers. There are two types of farmers. One type of farmer bought product by himself. The other type of farmer bought product with another farmer and this purchase is called a split.

When I list the farmers who bought product, I want to list the split farmers who bought together as one row. So, in the data below I want 3 rows to show up.

I tried this:
select name, company, address1
from growers_dd join submissions_dd on growers_dd.growerid=submissions_dd.growerid
where submissions_dd.dealerid='0793638880000' and submissions_dd.split_id is null
union
select tblsplit_dd.splitName, tblsplit_dd.company, tblsplit_dd.address1 from growers_dd join submissions_dd on growers_dd.growerid=submissions_dd.growerid
left outer join tblsplit_dd on submissions_dd.split_id=tblsplit_dd.split_id
where submissions_dd.split_id is not null and submissions_dd.dealerid='0793638880000'
group by splitName, tblsplit_dd.company, tblsplit_dd.address1
order by name

I was using different table names at the time. It works but in order for it to work, I had to put "dummy" company and address1 columns in the tblSplit table. Is there a better way to do this?

CREATE TABLE [growers_dd_test] (
[growerid] [int] NOT NULL ,
[icnumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[company] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [submissions_dd_test] (
[submitid] [int] NOT NULL ,
[split_id] [int] NULL ,
[date_submit] [smalldatetime] NULL ,
[farm_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[growerid] [int] NULL ,
[dealerid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[valor_pounds] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [tblSplit_dd_test] (
[split_id] [int] NOT NULL ,
[splitName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblSplit_dd_test] PRIMARY KEY CLUSTERED
(
[split_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into growers_dd_test (growerid,icnumber, company, name, address1, city, state, zip)
values (555,'0793638880000','Nation Split','mark nation', 'rt. 3','Pittsfield','IL','62323')
insert into growers_dd_test (growerid,icnumber, company, name, address1, city, state, zip)
values (556,'0793638880000','Farm Growers','Joe Blow','543 Warshaw Rd','Rockford','IL','54345')
insert into growers_dd_test (growerid,icnumber, company, name, address1, city, state, zip)
values (557,'0793638880000','Nation Split','bob nation', 'rt. 3','Pittsfield','IL','62323')
insert into growers_dd_test (growerid,icnumber, company, name, address1, city, state, zip)
values (558,'0793638880000','Hobbs Farms','Ben Hobbs','933 Hwy 1','Barely','IA','54788')

insert into submissions_dd_test (submitid, date_submit,growerid,dealerid,valor_pounds)
values (8441,'2005-8-20',556,'0793638880000',25.2)
insert into submissions_dd_test (submitid, date_submit,growerid,dealerid,valor_pounds)
values (8442,'2005-8-20',558,'0793638880000',80.0)
insert into submissions_dd_test (submitid, split_id,date_submit,growerid,dealerid,valor_pounds)
values (8443,505,'2005-8-20',557,'0793638880000',10.0)
insert into submissions_dd_test (submitid, split_id,date_submit,growerid,dealerid,valor_pounds)
values (8444,505,'2005-8-20',555,'0793638880000',35.2)

insert into tblSplit_dd_test values (505,'Nation Farm')

Thanks,

Dan

rheitzman
Starting Member

16 Posts

Posted - 2005-09-21 : 20:14:49

The "I tried this.." query does not match the table names in data you provided.

dand

13 Posts

Posted - 2005-09-22 : 08:09:31

I mentioned that I was using different table names than the ones that I sent.

Dan

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-22 : 23:48:47

Then send the ones you're using. Give us examples that replicate the issue you were having, along with examples of what you want the result to be, so we can help you.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources