Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple table join help 3

Status
Not open for further replies.
EchoAlertcom

EchoAlertcom

IS-IT--Management
Oct 8, 2002
239
US
Hello,

I am trying to write a multiple table join that will show data like:

Name Sales Opens Clicks Unsubs
---------------------------------------
Test 1 3 10 2

My query now returns even though the sales, opens, and unsubs have records:

Name Sales Opens Clicks Unsubs
---------------------------------------
Test 0 0 10 0

My query is now:

Code: 
SELECT Count(dbo.Clicks.CID) As ClickCount, Count(dbo.Opens.OID) As OpenCount, Count(dbo.Unsubs.UID) As UnsubCount, Count(dbo.Sales.SID) As SalesCount, dbo.Links.LID, dbo.Links.LinkName
FROM dbo.Links         
Left Outer Join dbo.Clicks on dbo.Links.LID = dbo.Clicks.LID  
Left Outer Join dbo.Opens on dbo.Links.LID = dbo.Opens.OID  
Left Outer Join dbo.Unsubs on dbo.Links.LID = dbo.Unsubs.UID  
Left Outer Join dbo.Sales on dbo.Links.LID = dbo.Sales.SID
WHERE dbo.Links.UID = varLink  Group By dbo.Links.LID, dbo.Links.LinkName
ORDER BY dbo.Links.LID Desc

Can anyone help me straighten this mess out please?

Regards,
Steve
 
Sort by date Sort by votes
I don't know your database. I did notice this...

Code: 
Left Outer Join dbo.Clicks on dbo.Links.[green]LID[/green] = dbo.Clicks.[green]LID[/green]  
Left Outer Join dbo.Opens on dbo.Links.[blue]LID[/blue] = dbo.Opens.[blue]OID[/blue]  
Left Outer Join dbo.Unsubs on dbo.Links.[red]LID[/red] = dbo.Unsubs.[red]UID[/red]  
Left Outer Join dbo.Sales on dbo.Links.LID = dbo.Sales.SID

Are you linking the proper fields?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Thanks for your responses.

I guess I should tell you what I'm trying to achieve first. I have a campaign summary page that each record in the record set should count the number of records in each of the following tables: Clicks, Opens, Sales and Unsubs. In other words, each time a campaign gets a click, open or otherwise it inserts a record into the appropriate table. My summary page lists the number of each of those for every campaign that is a record in the Links table.

I'm not sure if I'm clearing it up or turning it to mud. :)

Here is my query after correcting the fields:

Code: 
SELECT 
	Count(dbo.Clicks.CID) As ClickCount, 
	Count(dbo.Opens.OID) As OpenCount, 
	Count(dbo.Unsubs.UID) As UnsubCount, 
	Count(dbo.Sales.SID) As SalesCount, 
	dbo.Links.LID, 
	dbo.Links.LinkName
FROM dbo.Links
Left Outer Join dbo.Clicks on dbo.Links.LID = dbo.Clicks.LID
Left Outer Join dbo.Opens on dbo.Links.LID = dbo.Opens.LID
Left Outer Join dbo.Unsubs on dbo.Links.LID = dbo.Unsubs.LID
Left Outer Join dbo.Sales on dbo.Links.LID = dbo.Sales.LID
WHERE dbo.Links.UID = 1  
Group By dbo.Links.LID, LinkName
ORDER BY dbo.Links.LID Desc

For this query I know that there are:

8 Opens in the Opens table
21 Clicks in the Clicks table

But when I run the above query I get:
168 Opens from the OpenCount value
168 Clicks from the ClickCount value.

I obviously have my joins incorrect. I have spent a long time changing the joins but I can't get it right.

I hope I have given some better info for your help. Do the best you can. :)

Regards,
Steve
 
Upvote 0 Downvote
Could you please send some sample data and row names for each talble.. (it makes it alot easier to spot your problem - and I don't have time to think of all hte strange ways you could have built your data)

 
Upvote 0 Downvote

I see what you mean now. I have posted the tables and data below. What I need from this join is the number of Clicks and opens by LID

So for the sample data below I would like to end up with a join statement that returns:

LID Clicks Opens
10001 20 8
10002 3 1

Table Structures:
Code: 
CREATE TABLE [dbo].[Links] (
	[LID] [int] IDENTITY (10000, 1) NOT NULL ,
	[LGID] [int] NULL ,
	[UID] [int] NULL ,
	[SLID] [int] NULL ,
	[LinkName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LinkDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FromDomain] [int] NULL ,
	[RedirectedURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LinkType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DateCreated] [datetime] NULL ,
	[Circulation] [int] NULL ,
	[SaleAmount] [money] NULL ,
	[CPC] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Clicks] (
	[CID] [int] IDENTITY (1, 1) NOT NULL ,
	[UID] [int] NULL ,
	[LID] [int] NULL ,
	[ClickDate] [datetime] NULL ,
	[SourceIP] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SourceOS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SourceBrowser] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Referrer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[QueryString] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Opens] (
	[OID] [int] IDENTITY (1, 1) NOT NULL ,
	[UID] [int] NULL ,
	[LID] [int] NULL ,
	[OpenDate] [datetime] NULL ,
	[SourceIP] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SourceOS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SourceBrowser] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Referrer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[QueryString] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]

Sample data in the same order as above tables:

Code: 
Links Table Data (Select LID, UID, LinkName from dbo.Links)
LID    UID      LinkName
10001	1	Test 2
10002	2	Test 3
10003	1	Test 12345

Clicks Table Data (Select CID, UID, LID from dbo.Clicks)
CID    UID      LID
1	2	10002
2	1	10001
3	2	10002
4	1	10001
5	1	10001
6	2	10002
7	1	10001
8	1	10001
9	1	10001
10	1	10001
11	1	10001
12	1	10001
13	1	10001
14	1	10001
15	1	10001
16	1	10001
17	1	10001
18	1	10001
19	1	10001
20	1	10001
21	1	10001
22	1	10001
23	1	10001

Opens Table Date (Select OID, UID, LID from dbo.Opens)
OID    UID      LID
1	1	10001
2	2	10002
3	1	10001
4	1	10001
5	1	10001
6	1	10001
7	1	10001
8	1	10001
9	1	10001


Thank you very much for your patience and your help.

Regards,
Steve
 
Upvote 0 Downvote
Select Links.LID,
Count(Distinct OID) As Opens,
Count(Distinct CID) As Clicks
from Links
Left Join Opens On Links.LID = Opens.LID
Left Join Clicks On Links.LID = Clicks.LID
Group By Links.LID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
I'm still not much familiar with some parts of that table design (btw. why is UID present in all 3 tables?)... other than that, there are many ways to produce expected results. For example:

Code: 
select * from 
(	select L.LID, 
		( select count(*) from clicks C where C.LID=L.LID ) as clicks,
		( select count(*) from opens  O where O.LID=L.LID ) as opens
	from Links L
) X 
where X.clicks<> 0 or X.opens<> 0

Or:
Code: 
select L.LID, count(distinct C.CID) as clicks, count(distinct O.OID) as opens
from Links L
left outer join Clicks C on C.LID=L.LID
left outer join Opens O on O.LID=L.LID
group by L.LID
having count(distinct C.CID) > 0 or count(distinct O.OID) > 0

Or:
Code: 
select isnull(C.LID, O.LID) as LID, isnull(C.clicks, 0) as clicks, isnull(O.opens, 0) as opens
from
(	select LID, count(*) as clicks
	from Clicks
	group by LID
) C
full outer join
(	select LID, count(*) as opens
	from Opens
	group by LID
) O
on C.LID=O.LID

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Thanks everyone those worked great.

The UID is in each table to tie it in with the Users table which was out of the scope of this query other than in the "Where" clause to constrain the results to a specific user.

Thanks again,
Steve
 
Upvote 0 Downvote
WOW thanks for the star.. And all i did was ask your for information..

Thanks

Rob
 
Upvote 0 Downvote
NoCoolHandle:
Star was for the second post you made. It made me realize that I was not giving good enough info (when i thought i was) to get the help I needed. Thanks, Steve
 
Upvote 0 Downvote
It is always nice to see enough information to make an easy diagnosis. Normally here you work on hints.

As you can tell. Good info = Good advice.


Glad I could be of help, but nice job to Von and George for using the info.

Rob
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
2K
Chris Miller
Chris Miller
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
894
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
1K
Chris Miller
Chris Miller
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
579
f0rg3tfu1
f0rg3tfu1
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
917
BobSuruncle
BobSuruncle

Part and Inventory Search

Sponsor

Back
Top