Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Trouble with many to many

    Hi, I'm having some trouble with a query and was hoping for some help with this.

    I have 4 tables,

    Inventory
    * idInventory

    Feature_X_Inventory
    * idFeature_X_Inventory
    InventoryID
    featureGroupID
    featureID

    featureGroups
    *idFeatureGroups
    featureGroupName

    Features
    * idFeatures
    featureGroupID
    featureName

    I can create inner joins and get the inventory items that contain a single feature by name but if there are multiple features in different feature groups I can't figure out how to handle that. There can be any number of feature groups and features.

    for example
    FeatureGroup = Colors
    Feature = White
    Feature = Black
    Feature = Blue

    Feature Group = Interface
    Feature = Serial
    Feature = USB

    So I can select a inventory item that is white or that is serial but I cant figure out how to select a inventory item that is both White and Serial or any combination like that

    Thanks in advance for any help
    -Keith

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT InventoryID
      FROM Feature_X_Inventory
     WHERE featureGroupID = 
           ( SELECT idFeatureGroups
               FROM featureGroups
              WHERE featureGroupName = 'Colors' )
       AND featureID = 
           ( SELECT Features.idFeatures
               FROM featureGroups
             INNER
               JOIN Features
                 ON Features.featureGroupID = featureGroups.idFeatureGroups
                AND Features.featureName = 'White'
              where featureGroups.featureGroupName = 'Colors' )
        OR featureGroupID = 
           ( SELECT idFeatureGroups
               FROM featureGroups
              WHERE featureGroupName = 'Interface' )
       AND featureID = 
           ( SELECT Features.idFeatures
               FROM featureGroups
             INNER
               JOIN Features
                 ON Features.featureGroupID = featureGroups.idFeatureGroups
                AND Features.featureName = 'Serial'
              where featureGroups.featureGroupName = 'Interface' )
    GROUP
        BY InventoryID
    HAVING COUNT(*) = 2
    if this seems complex, it's because your X table has a redundant column

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    Hey thanks,

    Do you mean the featureGroupID, its there so I can point a feature group to a inventory item, what I do is display the feature groups for a inventory item as a header then each feature below that. I suppose I could get the features for a inventory item then get the group that feature points to and not have to have that column in the cross table.

  4. #4
    Join Date
    Jun 2009
    Posts
    3
    That worked great and is returning the proper inventoryID's

    How can I do a further select statement to return from the Inventory Table, I have a final select to do which is like this

    Select IDInventory, active , inventoryType from Inventory where (active = -1) and (inventoryType = 'product')

    can i take the results from your query and further select them or would it be better to do my query first to narrow down the results so the larger query has less records to deal with?

    Thanks
    -Keith

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT IDInventory
         , active 
         , inventoryType 
      FROM Inventory 
     WHERE active = -1
       AND inventoryType = 'product'
       AND IDInventory IN 
           ( put original query here )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •