January 5, 2010 at 6:56 am
I have one table where I can do a
SELECT count(*) FROM schema.table1 where contact_type_id = 'Email1';
and I get good results.
Now I need to match up the contact_type_id with a member_number
I can trace the FK relationships from schema.table1 to the table that I want to join to in order to do my select for Contact_type_id = 'Email1' AND Member_id but I can't figure out how to prepare the SQL. Can anyone help?
January 5, 2010 at 6:59 am
What have you tried so far? Read the link in my sig.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 5, 2010 at 7:00 am
Are you trying to get a count as your final output, or something else?
If it's a count, then it might look something like this:
select count(*)
from schema.table1
inner join schema.table2
on table1.column = table2.column -- based on FK columns
where table1.contact_type_id = 'Email1'
and table2.member_id = 1;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2010 at 7:01 am
This is the query from what I can understand what you need
SELECT count(*)
FROM schema.table1
INNER JOIN schema.table2
ON contact_type_id = member_number
where contact_type_id = 'Email1'
IF you can post table schema, sample data and result that you expect then we can help you more.
-Vikas Bindra
January 5, 2010 at 7:31 am
Thanks for all you help. it is not just counts that I need. I need to extract Client_number, Member_number and contact_type_id (with a where clause for Email1)
I've attached the DDL for all four tables
January 5, 2010 at 7:57 am
check this
SELECT c.Client_number
,m.Member_number
,contact_type_id
FROM CLIENT c
INNER JOIN MEMBER m
ON c.CLIENT_ID = m.CLIENT_ID
INNER JOIN CLIENT_CONTACTS cc
ON cc.CLIENT_ID = c.CLIENT_ID
INNER JOIN CONTACTS ct
ON ct.CONTACT_ID = cc.CONTACT_ID
-Vikas Bindra
January 5, 2010 at 8:21 am
Thank you!!!!!!
This actaully works:
SELECT
a.CONTACT_TYPE_ID
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
BUT I can't figure out how to incorporte my where clause
SELECT * FROM FRATERNAL.CONTACTS where contact_type_id = 'Email1'; so that it actually displays the email addresses? ??????
January 5, 2010 at 8:27 am
Add "a.*" to your Select clause, and put the Where clause at the end exactly as you have written it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2010 at 8:36 am
what is it that you are trying to count. The query above does not have your where clause which is easy to add but is missing the count. If you tell us what you are trying to count we can include that in your query if you need using an in-line view
January 5, 2010 at 8:47 am
Jpotucek (1/5/2010)
Thank you!!!!!!This actaully works:
SELECTa.CONTACT_TYPE_ID
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
BUT I can't figure out how to incorporte my where clause
SELECT * FROM FRATERNAL.CONTACTS where contact_type_id = 'Email1'; so that it actually displays the email addresses? ??????
IS this what you want
SELECT
a.CONTACT_TYPE_ID
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE contact_type_id = 'Email1'
-Vikas Bindra
January 5, 2010 at 11:48 am
Thank you all for your guidance!!!! This worked :
SELECT
a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE a.CONTACT_TYPE_ID in ('Email1', 'EMail2', 'Email3')
January 7, 2010 at 8:45 am
Jpotucek (1/5/2010)
Thank you all for your guidance!!!! This worked :SELECT
a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE a.CONTACT_TYPE_ID in ('Email1', 'EMail2', 'Email3')
Are you sure? It doesn't return email address, which you asked for, and it doesn't do a count, which you asked for!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 20, 2010 at 6:40 am
Actually I don't need a count and it does return Email address in the contact_info column
SELECT
a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE a.CONTACT_TYPE_ID in ('Email1')
I do need some more help though.. I'm struggling with using the above query but returning only one particular Member_Number??????
January 20, 2010 at 6:48 am
SELECT
a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE a.CONTACT_TYPE_ID in ('Email1')
AND b.MEMBER_NUMBER = [Some value]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 20, 2010 at 7:05 am
awesome.. it's actually all coming together in my head 🙂
another dilema...
Add a History_YN column to the contacts table and Select where not equal to Y
I thought it would be something like this but it blows up:
SELECT a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
,d.history_YN
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE a.CONTACT_TYPE_ID in ('Email1')
AND b.MEMBER_NUMBER = 1980261
AND d.History_YN <> Y
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply