SQL 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

General SQL Server Forums

New to SQL Server Programming

SQL Help

Author

Topic

phanicrn
Starting Member

42 Posts

Posted - 2007-10-08 : 11:21:24

Hi

I two tables, channel,code.fact table.

memberkey from fact table, channel name from channel table, transaction description from code table,,
=
now in my first sql

select transaction_description,count(distinct member_key) from
transaction_fact, transaction_cod
where code.key = code.key
and transaction_description ='cc'

this sql gets me all member keys who have description of cc.

sql 2

select channel_name, count(distinct member_key) from
channel_dim, transaction_fact
where channel_key =channel_key
and channel_name ='eft'
this sql gives me all members who are eft.

now how can i look for common memberkeys from sql1 & sql2..

now how do i write my sql too look for members who have channel_name ='eft' and code='cc'

Thanks
Phani

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 11:38:53

[code]SELECT Member_Key
FROM (
SELECT DISTINCT <table alias here>.Member_Key
FROM Transaction_Fact AS tf
INNER JOIN Transaction_Cod AS tc ON tc.Key = tf.Key
WHERE <table alias here>.Transaction_Description = 'cc'

UNION ALL

SELECT DISTINCT <table alias here>.Member_Key
FROM Channel_Dim AS cd
INNER JOIN Transaction_Fact AS tf ON tf.Channel_Key = cd.Channel_Key
WHERE <table alias here>.Channel_Name = 'eft'
) AS d
GROUP BY Member_Key
HAVING COUNT(*) > 1[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 11:40:13

Or this?

SELECT DISTINCT	<table alias here>.Member_Key
FROM		Transaction_Fact AS tf
INNER JOIN	Transaction_Cod AS tc ON tc.Key = tf.Key
INNER JOIN	Channel_Dim AS cd ON cd.Channel_Key = tf.Channel_Key
WHERE		<table alias here>.Transaction_Description = 'cc'
		AND <table alias here>.Channel_Name = 'eft'

E 12°55'05.25"
N 56°04'39.16"

phanicrn
Starting Member

42 Posts

Posted - 2007-10-08 : 12:12:35

let me try this..thanks guys

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 12:21:49

"guys"

That'll be Peso and his twin brother ... errrmmm ... Peso

phanicrn
Starting Member

42 Posts

Posted - 2007-10-11 : 09:48:05

Hi
Thanks for reply.
when i run this query
select transaction_description,count(distinct member_key) from
transaction_fact, transaction_cod
where code.key = code.key
and transaction_description ='cc',

i get record count of 7554.

and when i run the second sql i get record count as 41874
select channel_name, count(distinct member_key) from
channel_dim, transaction_fact
where channel_key =channel_key
and channel_name ='eft'

when join both queries and i get membercount as 3661

how do i write sql, saying i want all members who were in sql but not in sql 2

Thanks guys
phani

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 10:06:23

quote:
Originally posted by phanicrn

select transaction_description,count(distinct member_key) from
transaction_fact, transaction_cod
where code.key = code.key
and transaction_description ='cc'

What? Please revise the part in red.

quote:
Originally posted by phanicrn

select channel_name, count(distinct member_key) from
channel_dim, transaction_fact
where channel_key = channel_key
and channel_name ='eft'

Same thing here! Please revise the part in red.

Also, PLEASE PLEASE PLEASE ALWAYS PREFIX YOUR COLUMN NAMES WITH TABLE NAMES when using more than one table in a query!
How do you expect us to know in which table a column is stored?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 10:10:22

"How do you expect us to know in which table a column is stored?"

I would expect you to look in the DDL

phanicrn
Starting Member

42 Posts

Posted - 2007-10-11 : 10:13:52

Sorry guys, Take it Easy.

select b.transaction_description,count(distinct a.member_key) from
transaction_fact a, transaction_code b
where a.code.key = b.code.key
and b.transaction_description ='cc',

i get record count of 7554.

and when i run the second sql i get record count as 41874
select a.channel_name, count(distinct b.member_key) from
channel_dim a, transaction_fact b
where a.channel_key = b.channel_key
and a.channel_name ='eft'

anything let me know

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 10:14:24

Where can I find that in this problem?
Because code.key (table code) is not even references in the first query.

So I doubt he will get a result at all with that one...

E 12°55'05.25"
N 56°04'39.16"

phanicrn
Starting Member

42 Posts

Posted - 2007-10-11 : 10:25:32

I got the query working

Thanks
phani

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 10:41:13

Please post the query here so that other can benefit and learn too.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources