none
Collect data from 4 tables to 1 dataset

    Question

  • Hi, I have a 4 tables with some columns:

    1) Brands: CatalogueIndex, BrandIndex, BrandName

    2) Models: CatalogueIndex, ModelIndex, ModelName

    3) Statistic: CatalogueIndex, BrandIndex, ModelIndex, AvrPrice, MarketDate

    4) Images: CatalogueIndex, ModelIndex, ModelImage

    All I need is to select some data for CatalogIndex = 1 with my date, and combine it to dataset by the following order:

    Brands.BrandIndex, Brands.BrandName, Models.ModelIndex, Models.ModelName, Statistic.AvrPrice, Images.ModelImage

    with sorting by BrandName and ModelName

    I have this command, but it's not working( ((

    cmd.CommandText = "SELECT Brands.BrandIndex, Brands.BrandName, Models.ModelIndex, Models.ModelName, Statistic.AvrPrice, Images.ModelImage" & _
         " FROM (Brands INNER JOIN Models ON Brands.BrandIndex = Models.BrandIndex)" & _
         " INNER JOIN Statistic ON (Models.ModelIndex = Statistic.ModelIndex)" & _
         " AND (Models.BrandIndex = Statistic.BrandIndex)" & _
         " AND INNER JOIN Images (ON Models.ModelIndex = Images.ModelIndex)" & _
         " WHERE Statistic.MarketDate = #" & AllowedDay.ToString("yyyy-MM-dd") & "#" & _
         " AND Brands.CatalogueIndex=1" & _
         " AND Models.CatalogueIndex=1" & _
         " AND Statistic.CatalogueIndex=1" _
         " ORDER BY Brands.BrandName, Models.ModelName"


    Aleksey

    Wednesday, June 12, 2013 4:16 PM

Answers

  • I believe this is what you are looking for. If you add CatalogueIndex to the joins and add a filter in the join clause you should get the expected results. I don't see CatalogueIndex in Images in your query. If it exists, add the appropriate join.

    SELECT  Brands.BrandIndex, Brands.BrandName,
                Models.ModelIndex, Models.ModelName,
                Statistic.AvrPrice,
                Images.ModelImage
    FROM Brands
    INNER JOIN Models
      ON Brands.BrandIndex = Models.BrandIndex
      AND Brands.CatalogueIndex = Models.CatalogueIndex --Added this join
      AND Brands.CatalogueIndex = 1 --------------------------Added this join condition
    INNER JOIN Statistic
      ON Models.ModelIndex = Statistic.ModelIndex
      AND Models.BrandIndex = Statistic.BrandIndex
      And Models.CatalogueIndex = Statistic.CatalogIndex ---Added this join
    INNER JOIN Images
      ON Models.ModelIndex = Images.ModelIndex --Does CatalogueIndex exist in Images? If so...add the join here too--
    WHERE     Statistic.MarketDate = '<Substitute your date variable here>'
    ORDER BY Brands.BrandName, Models.ModelName

     


    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz
    • Marked as answer by Newfriend Tuesday, June 18, 2013 5:52 PM
    Tuesday, June 18, 2013 5:21 PM