Need help with SQL query

[RightIsWrong]

I am trying to query WebTrends database tables.

Currently, when pulling active profiles names from one table and datasources from another, I get a list of each profile associated with each datasource parameter. That is not quite what I need though.

What I need is to compare those results against a third table that actually has the profile/datasource mappings so that the results show exactly which profile is using which datasource(s). Here's what I have currently:

select distinct TOP 1000 wtp.ProfileID
, wtp.ProfileName
, wtp.WRC_ProfileID
, wtp.TimeZoneID
, wtd.DataSourceID
, wtd.DCSID
, wtd.Name
From wt_profile wtp --, wt_DataSource wtd
RIGHT JOIN wt_DataSource wtd ON wtp.AccountID = wtd.AccountID
where wtp.profilestatusid like '1' or wtp.profilestatusid like '8' and wtd.DCSID like 'dcs%';
The third table only has two columns that it uses to map the profile to datasource: DataSourceID and ProfileID

I did have a LEFT JOIN and changed it to RIGHT JOIN to see if the results would be different (they weren't).

The third table is wt_ProfileDataSource which has two columns; DataSourceID and ProfileID. I don't have it in the query above b/c I am very new to SQL and I'm unsure of how to include it to compare against to get the desired results (the above query only showing a list of profiles with their actual mapped datasources (the contents of the third table) instead of a full list with every profile being displayed as if they were mapped to every datasource)

Thanks in advance for any help that comes my way.

 

[Albatross]

2 tables results LEFT JOIN third table ON common column WHERE some column(id) IS NULL-this will get you results which exist only in the first 2 tables.

 

[RightIsWrong]

2 tables results LEFT JOIN third table ON common column WHERE some column(id) IS NULL-this will get you results which exist only in the first 2 tables.

Just to be sure that I'm following....

This will take the results from the first two table (each profile mapped to every datasource) and compare them to the actual mappings in the third table and display those results instead?

For example, the query above currently results in:

ProfileID / ProfileName / DataSourceName
1 first first
1 first second
1 first third
1 first fourth
2 second first
2 second second
2 second third
2 second fourth

And so on.

What I would like is to take those results, compare them against the third table which has

ProfileID / DataSourceID
1 1
2 2
2 3
3 3
4 1
5 1
5 4

And so on.

 

[Albatross]

I have a hard time following,could you create a sql fiddle?
So on your third table ProfileID which dont exist in the first result would be 3,4,5?

 

[RightIsWrong]

I have a hard time following,could you create a sql fiddle?
So on your third table ProfileID which dont exist in the first result would be 3,4,5?

Sorry for not making this easier for you....

The third table only has two columns, ProfileID & DataSourceID. A profile usually only has a single datasource but can have multiples.

Not really knowing SQL at all, I'm able to build the query above which generates all of the fields that I need, but it assigns every profile to every datasource instead of only generating a list of profiles with only the actual mapped datasource(s) for that profile because I don't know how to compare them against the third table which has the mappings.

 

[douglasb]

Are you looking for something like this?

select distinct TOP 1000 wtp.ProfileID
, wtp.ProfileName
, wtp.WRC_ProfileID
, wtp.TimeZoneID
, wtd.DataSourceID
, wtd.DCSID
, wtd.Name
From wt_profile wtp
Inner Join wt_ProfileDataSource On wtp.WRC_ProfileID = wt_ProfileDataSource.ProfileID
Inner Join wt_DataSource wtd ON wtd.DataSourceID = wt_ProfileDataSource.DataSourceID
Where wtp.profilestatusid like '1' or wtp.profilestatusid like '8' and wtd.DCSID like 'dcs%';

 

[RightIsWrong]

Are you looking for something like this?

select distinct TOP 1000 wtp.ProfileID
, wtp.ProfileName
, wtp.WRC_ProfileID
, wtp.TimeZoneID
, wtd.DataSourceID
, wtd.DCSID
, wtd.Name
From wt_profile wtp
Inner Join wt_ProfileDataSource On wtp.WRC_ProfileID = wt_ProfileDataSource.ProfileID
Inner Join wt_DataSource wtd ON wtd.DataSourceID = wt_ProfileDataSource.DataSourceID
Where wtp.profilestatusid like '1' or wtp.profilestatusid like '8' and wtd.DCSID like 'dcs%';

I think that I am, but it is returning an error when running:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '90tfANuaUt5' to data type int.

The string '90tfANuaUt5' is the WRC_ProfileID while the ProfileID is 1-3 chars. I tried to strip out the "wtp.WRC_ProfileID" constraint and ran it but received the same error.


EDIT: I was able to get it to run. It was calling the WRC_ProfileID and trying to map it to the ProfileID. Thanks for your and Albatross' help. This is exactly what I was needing.

 

[douglasb]

Glad to help.

 

[Albatross]

Here is a little helper