Sub Query 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 2000 Forums

SQL Server Development (2000)

Sub Query Help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-16 : 14:02:33

--Please help in getting the output
Rule:Query should exclude the records which has more than 1 custid in @prod2 table..

declare @Cust1 table
(
Custid int,
Csid bigint,
name1 varchar(5),
state varchar(4),
zip varchar(8)
)
insert into @Cust1
select 4102,103, 'Sam', 'NY', '2356' union all
select 4101,110, 'Pat', 'CA', '9526' union all
select 4102,106, 'Rob', 'NY', '2356'

declare @Prod2 table
(

Csid bigint,
Prod1 varchar(4),
Prod2 varchar(4),
EPRT int,
createddate datetime
)
insert into @Prod2
select 103, '11', '03', 1, '2007-02-02 14:13:43.000' union all
select 106, '01', '04', 2, '2007-02-02 14:13:31.000' union all
select 110, '11', '03', 0, '2007-02-02 14:13:31.000'

Desired Results:

Custid csid state name1 zip prod1 prod2 EPRT createddate
----------- -------------------- ----- ----- -------- ----- ----- ----------- ------------------------
4101 110 CA Pat 9526 11 03 0 2007-02-02 14:13:31.000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-16 : 14:12:15

Here's what I came up with:


SELECT c.Custid, c.csid, c.state, c.name1, c.zip, p.prod1, p.prod2, p.eprt, p.createddate
FROM @Cust1 c
INNER JOIN @Prod2 p
ON c.Csid = p.Csid
INNER JOIN
(
	SELECT c.Custid, COUNT(*) AS CustidCount
	FROM @Cust1 c
	INNER JOIN @Prod2 p
	ON c.Csid = p.Csid
	GROUP BY c.Custid
	HAVING COUNT(c.Custid) = 1
) t
ON c.Custid = t.Custid

Tara Kizer

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-16 : 14:22:16

Thanks Tara Kizer!.

How about below one...

select t1.Custid,t2.csid, t1.state, t1.name1, t1.zip, t2.prod1, t2.prod2, t2.EPRT,t2.createddate
from @Cust1 t1 inner join @Prod2 t2
on t1.Csid = t2.Csid
where t1.Custid not in
(select t1.Custid from @Cust1 t1 inner join @Prod2 t2
on t1.Csid = t2.Csid GROUP BY t1.Custid HAVING COUNT(*) > 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 14:23:33

[code]-- prepare sample data
declare @Cust1 table (Custid int, Csid bigint, name1 varchar(5), state varchar(4), zip varchar(8))

insert into @Cust1
select 4102,103, 'Sam', 'NY', '2356' union all
select 4101,110, 'Pat', 'CA', '9526' union all
select 4102,106, 'Rob', 'NY', '2356'

declare @Prod2 table (Csid bigint, Prod1 varchar(4), Prod2 varchar(4), EPRT int, createddate datetime)

insert into @Prod2
select 103, '11', '03', 1, '2007-02-02 14:13:43.000' union all
select 106, '01', '04', 2, '2007-02-02 14:13:31.000' union all
select 110, '11', '03', 0, '2007-02-02 14:13:31.000'

-- Show the result
SELECT t1.CustID,
t1.CsID,
t1.State,
t1.Name1,
t1.Zip,
t2.Prod1,
t2.Prod2,
t2.Eprt,
t2.CreatedDate
FROM @Cust1 AS t1
INNER JOIN @Prod2 AS t2 ON t2.CsID = t1.CsID
INNER JOIN (
SELECT e.CustID
FROM @Cust1 AS e
INNER JOIN @Prod2 AS t ON t.CsID = e.CsID
GROUP BY e.CustID
HAVING COUNT(*) = 1
) AS x ON x.CustID = t1.CustID[/code]

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-16 : 14:29:54

quote:
Originally posted by sqldba2k6

Thanks Tara Kizer!.

How about below one...

select t1.Custid,t2.csid, t1.state, t1.name1, t1.zip, t2.prod1, t2.prod2, t2.EPRT,t2.createddate
from @Cust1 t1 inner join @Prod2 t2
on t1.Csid = t2.Csid
where t1.Custid not in
(select t1.Custid from @Cust1 t1 inner join @Prod2 t2
on t1.Csid = t2.Csid GROUP BY t1.Custid HAVING COUNT(*) > 1)

I prefer to use joins rather than subqueries.

Tara Kizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-16 : 14:30:29

Peter, isn't that what I posted already?

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 14:36:13

Of course. I opened the topic and copied the code to SSMS. When done, I pasted the suggestion back to SQLTeam without first refreshing the webpage.

Sorry if I offended you...

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-16 : 14:38:21

No problem, I just wanted to make sure I wasn't going blind! I see very slight differences between the two, but nothing that would change the execution plan.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 14:46:15

Happened here too, today
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79245

Peter Larsson
Helsingborg, Sweden

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-16 : 19:09:51

Is it possible to dynamically exported the results with column headers and data of the sql query to csv file?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-17 : 02:14:54

BCP.

Peter Larsson
Helsingborg, Sweden

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-17 : 10:19:35

Thanks peter!!
Is there any limitation of the query size which can be used in BCP..
Can i make the query as stored procedure.So that i can call the sp in BCP..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-17 : 13:13:07

I think it is best you place the code in a VIEW, and do a simple

SELECT * FROM MyView

in the BCP commmand

Peter Larsson
Helsingborg, Sweden

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-20 : 14:20:22

Peter!
I am using temporary tables in my query which i can't use the query in view.So can i use sp's in the bcp..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources