J'ai deux instruction SQL select qui dit
SELECT TOP (150) FoodID, COUNT(*) AS NumberOfFood FROM FoodTable WHERE FoodID IS NOT NULL AND FoodDate >= '2015-10-01' GROUP BY FoodID ORDER BY NumberOfFood DESC
J'ai aussi une autre instruction SQL
SELECT FoodSellerID, Market1, SellerLastName, SellerFirstName, PrimaryAddress1, PrimaryAddress2, PrimaryCity, PrimaryState, PrimaryZip FROM SellerTable, MarketTable WHERE Market1= MarketTable.MarketID
Quelques notes à signaler, search FoodID à SellerTable, Market1 Lookup à MarketTable. Ma question est comment puis-je combiner les deux et returnner les arrays comme
FoodID | Nombre de nourriture | SellerLastName | SellerFirstName | PrimaryAddress1 | PrimaryAddress2 | Ville Primaire | PrimaryState | PrimaryZip
Manny, vous ne serez pas en mesure de joindre ces deux requêtes sans un champ commun dans les deux. Si vous avez un champ commun, vous pouvez les créer en tant que table temporaire et les joindre avec une troisième requête. Il semble que vous ayez 'Food ID' dans le FoodTable (à partir de la première requête). Si cela existe sur le SellerTable, alors vous pourriez join les deux (et aussi join le MarketTable pour get des lignes de cette table).
Cela ressemblerait à ceci:
create temp table food_nums_temp as SELECT TOP (150) FoodID, COUNT(*) AS NumberOfFood FROM FoodTable WHERE FoodID IS NOT NULL AND FoodDate >= '2015-10-01' GROUP BY FoodID ORDER BY NumberOfFood DESC ; Create temp table seller_temp as SELECT FoodSellerID, FoodID, Market1, SellerLastName, SellerFirstName, PrimaryAddress1, PrimaryAddress2, PrimaryCity, PrimaryState, PrimaryZip FROM SellerTable, MarketTable WHERE Market1= MarketTable.MarketID ; select a.*, b.SellerLastName , b.SellerFirstName, b.PrimaryAddress1, b.PrimaryAddress2 , b.PrimaryCity, b.PrimaryState, b.PrimaryZip from food_nums_temp a inner join seller_temp b on a.FoodID = b.FoodID
EDIT: sachant que l'access db complet (create temp table) n'est pas autorisé, utilisons 'WITH' (aka expressions de table communes ou CTE) pour mettre en scène nos données avant la requête désirée:
with food_nums_temp as ( SELECT TOP (150) FoodID, COUNT(*) AS NumberOfFood FROM FoodTable WHERE FoodID IS NOT NULL AND FoodDate >= '2015-10-01' GROUP BY FoodID ORDER BY NumberOfFood DESC ), seller_temp as SELECT FoodSellerID, FoodID, Market1, SellerLastName, SellerFirstName, PrimaryAddress1, PrimaryAddress2, PrimaryCity, PrimaryState, PrimaryZip FROM SellerTable, MarketTable WHERE Market1= MarketTable.MarketID ) select a.*, b.SellerLastName , b.SellerFirstName, b.PrimaryAddress1, b.PrimaryAddress2 , b.PrimaryCity, b.PrimaryState, b.PrimaryZip from food_nums_temp a inner join seller_temp b on a.FoodID = b.FoodID
Basé sur votre description vague de la search de FoodID à SellerTable , essayez ceci
SELECT FoodSellerID, Market1, SellerLastName, SellerFirstName, PrimaryAddress1, PrimaryAddress2, PrimaryCity, PrimaryState, PrimaryZip FROM SellerTable ST INNER JOIN MarketTable MT ON ST.Market1 = MT.MarketID inner join ( SELECT FoodID, COUNT(*) AS NumberOfFood FROM FoodTable WHERE FoodID IS NOT NULL AND FoodDate >= '2015-10-01' GROUP BY FoodID ) a1 on a1.FoodID = ST.FoodID
Essayez ceci:
SELECT TOP (150) a.FoodID, COUNT(*) AS a.NumberOfFood,b.SellerLastName, b.SellerFirstName, b.PrimaryAddress1, b.PrimaryAddress2, b.PrimaryCity, b.PrimaryState, b.PrimaryZipFROM FoodTable a inner join SellerTable b on a.FoodID = b.FoodSellerID WHERE a.Market1= b.MarketTable.MarketID IS NOT NULL AND FoodDate >= '2015-10-01' GROUP BY a.FoodID, b.SellerLastName, b.SellerFirstName, b.PrimaryAddress1, b.PrimaryAddress2, b.PrimaryCity, b.PrimaryState, b.PrimaryZip ORDER BY a.NumberOfFood DESC
Note: pourrait être une autre solution.