SQL query

[localzuk]

I am trying to craft a report for one of our databases here, and my brain is failing me this morning.

 

I have 2 tables -

 

Transaction

ID Amount Date

 

TransactionItem

ID ProductID TransactionID Name Amount

 

Transaction is related to TransactionItem, and can have many TransactionItem records for each Transaction.

 

I want to create a query which returns all the Transaction records that don't have a TransactionItem.ProductID = 251.

 

A join is the wrong thing to use, as it'll give me all the non-251 TransactionItem records, also.

 

Any hints?

[Spl1t]

If I am understanding you correctly you could try this:

 

select * from Table 1

Where ID IN

(

Select ID from Table 2 where TransactionItem.ProductID <> 251

)

 

This is similar to a join but matching based on the ID which would need to be the same in both tables. Apologies if I've misunderstood what you're seeking.

[localzuk]

As far as I can tell, that would still return all the transactions that have any other productID in, even if the transaction also contains the productID 251.

 

Eg.

 

If a transaction has the following transactionitems, I'd still get that transaction back, as it contains a product 150 as well.

 

ID ProductID TransactionID Name Amount

1 251 2 Apple 0.25

2 150 2 Milk 0.30

 

I want that transaction to not be returned at all, because it contains that 251 item.

[pcstru]

Something like :

 

SELECT Trans.Id, Trans.Amount, Item.ID, Item Name
   from Transaction Trans left outer join TranscationItem Item on (Trans.ID=Item.TransactionID and Item.ProductId = 251)
 where Item.ProductID is null

 

 

should work or at least point in the right direction.

[localzuk]

That got it. Thanks @pcstru.

[synaesthesia]

Spambot reported