By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Tagging Database Query

P: 1
Hello,

I have a tagging database, that allows me to apply tags to transaction records. The transaction records can have none or several different tags applied to them.

I would like to be able to "Auto Tag" records with similar details - ie if they have the same Payee Name, or meet certain criteria such as same amount, and an identical reference number etc... (in effect they are repeating transactions) upon import.

My tables (simplified) are as follows:

Expand|Select|Wrap|Line Numbers
  1. transactions
  2. =================
  3. transaction_id, transaction_payee
  4. 1, A Payee
  5. 2, B Payee
  6. 3, C Payee
  7. 4, B Payee
  8. 5, B Payee
  9.  
  10. tag_link
  11. ==================
  12. id, transaction_id, tag_id
  13. 1, 1, 2
  14. 2, 2, 1
  15. 3, 1, 3
  16. 4, 5, 2
  17.  
  18. tags
  19. ==================
  20. tag_id, tag
  21. 1, Food
  22. 2, Rent
  23. 3, Car Park
So Transaction 1 is tagged as "Rent, Car Park" and Transaction 2 is tagged as "Food" etc...

I need to be able to select record ids in "transactions" that have the payee = "B Payee" only if they do not already have a record in "tag_link" that is = 1. So in the above example transactions record 4 and 5 would be selected.

This is so that I can then add two new records to tag_link to tag them (transaction_id = 4, 5) as "1"

So in summary I am tagging transaction records that have a Payee name "B Payee" that are not already tagged as Food.

I tried this but it is not working:

Expand|Select|Wrap|Line Numbers
  1. SELECT  transactions.transaction_id
  2. FROM transactions
  3. WHERE (((transactions.transaction_payee)="B Payee" Not In (SELECT tag_link.transaction_id FROM tag_link WHERE tag_link.tag_id=1)));
  4.  
Can do this all in one query?? (Or should I be?!)

Or is there a better approach?

Sorry if this has been covered but it is hard to know what to search for.

Thanks,

Joe
Sep 13 '07 #1
Share this Question
Share on Google+
1 Reply


Scott Price
Expert 100+
P: 1,384
Hello,

I have a tagging database, that allows me to apply tags to transaction records. The transaction records can have none or several different tags applied to them.

I would like to be able to "Auto Tag" records with similar details - ie if they have the same Payee Name, or meet certain criteria such as same amount, and an identical reference number etc... (in effect they are repeating transactions) upon import.

My tables (simplified) are as follows:

Expand|Select|Wrap|Line Numbers
  1. transactions
  2. =================
  3. transaction_id, transaction_payee
  4. 1, A Payee
  5. 2, B Payee
  6. 3, C Payee
  7. 4, B Payee
  8. 5, B Payee
  9.  
  10. tag_link
  11. ==================
  12. id, transaction_id, tag_id
  13. 1, 1, 2
  14. 2, 2, 1
  15. 3, 1, 3
  16. 4, 5, 2
  17.  
  18. tags
  19. ==================
  20. tag_id, tag
  21. 1, Food
  22. 2, Rent
  23. 3, Car Park
So Transaction 1 is tagged as "Rent, Car Park" and Transaction 2 is tagged as "Food" etc...

I need to be able to select record ids in "transactions" that have the payee = "B Payee" only if they do not already have a record in "tag_link" that is = 1. So in the above example transactions record 4 and 5 would be selected.

This is so that I can then add two new records to tag_link to tag them (transaction_id = 4, 5) as "1"

So in summary I am tagging transaction records that have a Payee name "B Payee" that are not already tagged as Food.

I tried this but it is not working:

Expand|Select|Wrap|Line Numbers
  1. SELECT  transactions.transaction_id
  2. FROM transactions
  3. WHERE (((transactions.transaction_payee)="B Payee" Not In (SELECT tag_link.transaction_id FROM tag_link WHERE tag_link.tag_id=1)));
  4.  
Can do this all in one query?? (Or should I be?!)

Or is there a better approach?

Sorry if this has been covered but it is hard to know what to search for.

Thanks,

Joe
Why not try two criteria? One for the transactions.transaction_payee = "B Payee" and another for the tag_link.tag_ID <>1??

Regards,
Scott
Sep 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.