SQL showing more than wanted

[romerz]

The code that works is

"SELECT * FROM Orders WHERE ( (orderStatusId >=3) AND (orderStatusId <=5) ) AND (john_text3 <> 'TDC') AND (john_text3 <> '')"
"ORDER BY john_text3, orderId"

But i need to join the orders table onto the transactionEntryItems table.

it goes this way

Orders -> Transactions -> TransactionEntries -> TransactionEntryItems

So i used

"SELECT Orders.john_text3, Orders.orderDate, Orders.orderId, Orders.john_tex10, Orders.total"
" 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"
" WHERE ( (Orders.orderStatusId >=3) AND (Orders.orderStatusId <=5) ) AND (Orders.john_text3 <> 'TDC') AND (Orders.john_text3 <> '')"
"ORDER BY Orders.john_text3, Orders.orderId"

However, in the orders table, say order number 100 bought 4 things, transactionentryitems would have 4 listings for the 4 items. im just wanting to know if order 100 has had any returns - which is done by checking a field in the transactionentryitems called returnDate. When i do this however, the sql makes all 4 items show up.

I have a little bit of code that basically prints out some fields from the database and im wanting to check if an order has had any items returned. Is it the sql that has something wrong with it ?

 

[JarlH]

SELECT Orders.john_text3,
       Orders.orderDate,
       Orders.orderId,
       Orders.john_tex10,
       Orders.total
FROM Orders
WHERE (Orders.orderStatusId >=3 AND Orders.orderStatusId <=5)
  AND Orders.john_text3 <> 'TDC'
  AND Orders.john_text3 <> ''
  AND Orders.orderId IN (SELECT Transactions.orderId
                         FROM Transactions etc... <-- PUT SOM CODE HERE)
ORDER BY Orders.john_text3, Orders.orderId

 

[romerz]

I dont understand how that has connected to the other tables though :x

 

[PHV]

the sql makes all 4 items show up
you may use the DISTINCT predicate:
"SELECT DISTINCT Orders.john_text3, ...

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