SQL Statement help required. Thanks.

Question

UPDATE

Finally managed to work it out! Thanks for all the help from everyone. If you spot any potential errors or scope for improvement in my query please let me know.

SELECT * 
FROM TBL_CAMPAIGNS C
INNER JOIN TBL_MEMBERS M
    ON C.campaign_MemberId = M.members_Id
INNER JOIN TBL_CAMPAIGNS_CHARITIES CC
    ON C.campaign_Key = CC.camchar_CampaignID
INNER JOIN TBL_CHARITIES CH
    ON CC.camchar_CharityID = CH.cha_Key
LEFT OUTER JOIN (
    select recip_Chosen, count(recip_CampaignId) as ChosenCount
    from TBL_CAMPAIGNRECIPIENTS
    WHERE recip_CampaignId =  @campaign
    group by recip_Chosen
) CRC
on CH.cha_Key = CRC.recip_Chosen
WHERE C.campaign_Key = @campaign

Thanks!!!

///////////////////

After some really useful advice i decided to implement orbMan' suggestion as follows;

SELECT * 
FROM TBL_CAMPAIGNS C
INNER JOIN TBL_MEMBERS M
    ON C.campaign_MemberId = M.members_Id
INNER JOIN TBL_CAMPAIGNS_CHARITIES CC
    ON C.campaign_Key = CC.camchar_CampaignID
INNER JOIN TBL_CHARITIES CH
    ON CC.camchar_CharityID = CH.cha_Key
WHERE C.campaign_Key = @campaign

This returns 1 row for each charity associated with a given campaign (as associated via TBL_Campaigns_Charities). However, i also have another table(TBL_CAMPAIGNRECIPIENTS CR) which details each person invited to take part in the campaign. On visiting the campaign page they can select one of the charities linked to the campaign.

Now i need to know how many people have chosen each of the associated charities(CR.recip_Chosen). Their details arent important. I just need to know how many people have selected each of the associated charities.

So something like;

COUNT CH.cha_Key, FROM CR WHERE CR.recip_Chosen = CH.cha_Key

but integrated into the statement above.

Thanks in advance.

ORIGINAL POST BELOW:

/ / / / / / / / / / / / / / / / / / /

Hi,

I need to gain data from across 3 tables. The first two are straight forward and are currently grabbed as;

 SELECT * FROM TBL_CAMPAIGNS C
 JOIN TBL_MEMBERS M
 ON C.campaign_MemberId = M.members_Id
 WHERE C.campaign_Key = @campaign

The table 'TBL_CAMPAIGNS' contains various columns, five of which hold an int. This int refers to the key of the 3rd table 'TBL_CHARITIES'. How do i return the data of the third table in combination with the above?

Ive created the following so far;

 SELECT * FROM TBL_CAMPAIGNS C
 JOIN TBL_MEMBERS M
 ON C.campaign_MemberId = M.members_Id
 JOIN TBL_CHARITIES CH
 ON CH.cha_Key = C.campaign_Char1
 WHERE C.campaign_Key = @campaign

But, as you can tell, that only returns C.campaign_Char1. What about C.campaign_Char2, C.campaign_Char3, C.campaign_Char4, C.campaign_Char5 ?????

I did try this;

 SELECT * FROM TBL_CAMPAIGNS C
 JOIN TBL_MEMBERS M
 ON C.campaign_MemberId = M.members_Id
 JOIN TBL_CHARITIES CH
 ON CH.cha_Key = C.campaign_Char1
 AND CH.cha_Key = C.campaign_Char2
 AND CH.cha_Key = C.campaign_Char3
 .......
 WHERE C.campaign_Key = @campaign

But, of course this doesnt work!

Any suggestions / help?

Thanks in advance.

Answer 1

First thought is that you'll have to join the TBL_CHARITIES table again for each reference you want to give.

SELECT * FROM TBL_CAMPAIGNS C
 JOIN TBL_MEMBERS M
 ON C.campaign_MemberId = M.members_Id
 JOIN TBL_CHARITIES CH1
 ON CH1.cha_Key = C.campaign_Char1
 JOIN TBL_CHARITIES CH2
 ON CH2.cha_Key = C.campaign_Char2
 JOIN TBL_CHARITIES CH3
 ON CH3.cha_Key = C.campaign_Char3
 JOIN TBL_CHARITIES CH4
 ON CH4.cha_Key = C.campaign_Char4
 JOIN TBL_CHARITIES CH5
 ON CH5.cha_Key = C.campaign_Char5
 WHERE C.campaign_Key = @campaign

I'm sure someone has a better solution though.

Answer 2

This is a denormalized design and that is why you are having difficulty querying it. It would be easier if (instead of columns campaign_Char1 through 5) you had a many-to-many table between TBL_CAMPAIGNS and TBL_CHARITIES. E.g., TBL_CAMPAIGNS_CHARITIES. This would contain a Campaign ID and a CharityID.

Then your query would be:

SELECT * 
FROM TBL_CAMPAIGNS C
INNER JOIN TBL_MEMBERS M
    ON C.campaign_MemberId = M.members_Id
INNER JOIN TBL_CAMPAIGNS_CHARITIES CC
    ON C.campaign_Key = CC.CampaignID
INNER JOIN TBL_CHARITIES CH
    ON CC.CharityID = CH.cha_Key
WHERE C.campaign_Key = @campaign

Update:

SELECT * 
FROM TBL_CAMPAIGNS C
INNER JOIN TBL_MEMBERS M
    ON C.campaign_MemberId = M.members_Id
INNER JOIN TBL_CAMPAIGNS_CHARITIES CC
    ON C.campaign_Key = CC.camchar_CampaignID
INNER JOIN TBL_CHARITIES CH
    ON CC.camchar_CharityID = CH.cha_Key
LEFT OUTER JOIN (
    select recip_Chosen, count(*) as ChosenCount
    from TBL_CAMPAIGNRECIPIENTS 
    group by recip_Chosen
) CRC
on CH.cha_Key = CRC.recip_Chosen
WHERE C.campaign_Key = @campaign

Answer 3

Expanding a bit on @OrbMan, run the following SQL to demonstrate how this plays out. It should show you what the tables look like, including the many-to-many @camp2char table.

set nocount on
DECLARE @camp TABLE (
    ID int,
    ID2 int,
    primary key (id)
)
DECLARE @memb table (
ID int NOT NULL,
primary key (id)
)
DECLARE @chars table (
ID int NOT NULL,
primary key (id)
)
DECLARE @camp2char table (
ID1 int NOT NULL,
ID3 int NOT NULL
)
insert into @memb (id) values(100);
insert into @memb (id) values(200);
insert into @memb (id) values(300);
insert into @chars (id) VALUES(1000);
insert into @chars (id) VALUES(2000);
insert into @chars (id) VALUES(3000);
insert into @chars (id) VALUES(4000);
insert into @chars (id) VALUES(5000);
insert into @camp (ID,ID2) VALUES(1,100);
insert into @camp (ID,ID2) VALUES(2,300);
insert into @camp2char (ID1,ID3) VALUES(1,1000);
insert into @camp2char (ID1,ID3) VALUES(1,2000);
insert into @camp2char (ID1,ID3) VALUES(1,3000);
insert into @camp2char (ID1,ID3) VALUES(1,5000);
insert into @camp2char (ID1,ID3) VALUES(2,2000);

PRINT '@camp';
select * from @camp;
PRINT '@memb';
select * from @memb;
PRINT '@chars';
select * from @chars;
PRINT '@camp2char';
select * from @camp2char;

select c.ID 'camp.id', m.ID 'memb.id', ch.id 'char.id' from @camp c
inner join @memb m
on c.id2 = m.id
inner join @camp2char c2ch
on c.id = c2ch.id1
inner join @chars ch
on c2ch.id3 = ch.id
where c.id=1

One effect of this approach is that, instead of one resultant row, your result row count will be equal to the number of matching charity rows that match the target campaign.

The benefit is that you can have any number of charities associated with any number of campaigns.

Also, if you ever have more than one member per campaign, you'll want to normalize it the same way (with a camp2memb table, for example).

Output from script run on SQL SVR 2005

@camp
ID          ID2
----------- -----------
1           100
2           300

@memb
ID
-----------
100
200
300

@chars
ID
-----------
1000
2000
3000
4000
5000

@camp2char
ID1         ID3
----------- -----------
1           1000
1           2000
1           3000
1           5000
2           2000

camp.id     memb.id     char.id
----------- ----------- -----------
1           100         1000
1           100         2000
1           100         3000
1           100         5000