SQL Queries

Oct 13, 2010 at 3:10 PM

Great product! I am close to finishing my requirements for a huge project.

Few questions. I got everything up and running, working smooth. When I create my SQL query, I get weird results....

Here is my setup.

I have 2 lists, one has a look up of "media outlets" to the other.

My config:

<DataMapping DataSchema="SLAM">
    <List Site="MMT" Name="Media Outlets" ActivationOrder="1">
      <Fields>
        <Field Name="Region" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="States Used In" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Counties Used In" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Media Type" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Media Outlet Name" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Circulation/Viewership" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Number of Stories" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
      </Fields>
    </List>
    <List Site="MMT" Name="Media Tracker" ActivationOrder="2">
      <Fields>
        <Field Name="Story Title/Headline" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Date Ran/Aired" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Tonality" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Description of The Story" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Coverage Type" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Program Used In" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Media Outlet" SPType="Lookup" AssociatedList="Media Outlets" AssociationName="MediaOutletLookup" Required="true"></Field>
      </Fields>
    </List>

My SQL Query:

select *

from SLAM.MediaOutlets,SLAM.MediaTracker,SLAM.MediaOutletLookup

where SLAM.MediaTracker.ListItemID = SLAM.MediaOutletLookup.MediaTrackerListItemID

 

I am JUST testing at this point, but the results I get are 4 rows.... there are only 2 items in the list.... Thanks for you help in advance!

Oct 13, 2010 at 4:45 PM

Solved.

I changed the query to:

select *
from SLAM.MediaOutletLookup mol,SLAM.MediaOutlets mo,SLAM.MediaTracker mt
where mt.ListItemID = mol.MediaTrackerListItemID and
mo.ListItemID = mol.MediaOutletsListItemID

Works AWESOME!

This product saved me in so many ways!

Developer
Oct 13, 2010 at 6:45 PM

Glad to hear it!

Allan