SQL Inner join + normal 1

[romerz]

SELECT Orders.orderId, Orders.total, Orders.orderDate, Orders.name, Orders.city, Orders.orderStatusId, TransactionEntryItems.garmentSizeId, GarmentStockCodes.stockLevel, GarmentStockCodes.CJC_sub_weight2, TransactionEntryItems.garmentId

FROM (((Orders INNER JOIN Transactions ON Orders.orderId = Transactions.orderId) INNER JOIN TransactionEntries ON Transactions.transactionId = TransactionEntries.transactionId) INNER JOIN TransactionEntryItems ON TransactionEntries.transactionEntryId = TransactionEntryItems.transactionEntryId) INNER JOIN GarmentStockCodes ON TransactionEntryItems.garmentStockCodeId = GarmentStockCodes.garmentStockCodeId

WHERE (((TransactionEntryItems.actionDate)=0))

GROUP BY Orders.orderId, Orders.total, Orders.orderDate, Orders.name, Orders.city, Orders.orderStatusId, TransactionEntryItems.garmentSizeId, GarmentStockCodes.stockLevel, TransactionEntryItems.garmentId

HAVING ( ((Orders.orderStatusId)=3) OR ((Orders.orderStatusId)=4) OR ((Orders.orderStatusId)=0) )

Basically, i was to be able to take information from CJC_sub_weight2 which is in the GarmentStockCodes table - but i dont seem to be able to figure out how. I dont just put GarmentStockCodes.CJC_sub_weight2 at the end of the FROM - what should i do?

Thanks

 

[Golom]

You have GROUP BY every field but that one which means that you would need some aggregate function like MIN, MAX, SUM, AVG, etc. to select the value for GarmentStockCodes.CJC_sub_weight2 that you want to see.

It is unclear as to exactly what you want to accomplish. are you trying to get ALL the values for that field? Only a particular value like MAX or MIN?

 

[romerz]

aha, forgot to add it to the group by, spot on mate!

 

[PHV]

Why using a GROUP BY clause without any aggregate function ?
Have a look at the DISTINCT predicate ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886