chiprivers Posted April 12, 2010 Share Posted April 12, 2010 I have three tables... table 'transactions' which includes columns 'transactionID' and 'date' table 'analysis' which includes columns 'transactionID' and 'categoryID' table 'categories' which includes columns 'categoryID' and 'category' A very basic summary of this database is that the 'transactions' table will record individual financial transactions, with the 'analysis' table storing more detailed information about the breakdown of each transaction. The 'categories' table stores the category headings for analysis. For example there may be a record in the 'transactions' table recording a transaction for 100.00 and two further entries in the 'analysis' table breaking this down into two categories showing 50.00 for each. So for each entry in the 'transactions' table there will be atleast one entry in the 'analysis' table and each entry in the 'analysis' table will reference one entry in the 'categories' table. The query I am trying to build at the moment is to establish which of the 'categories' have been reference during a set time period. ie if I am building a table to display the transactions for a given time period, which entries from the 'categories' table do I need to use as headers for the table columns as I will not bother showing a column for a category which has not been used during that time period. So I need to query DISTINCT values of 'category' from the 'categories' table where their record has a 'categoryID' equal to 'categoryID' of all entries in the 'analysis' table which have a 'transactionID' equal to the 'transactionID' of all entries in the 'transactions' table which has a 'date' value between my set range. I am really struggling with the JOIN syntax here and which order to get the joins in to ensure I get the correct result. I have tried various combinations with LEFT JOINS and RIGHT JOINS but no joy! Link to comment https://forums.phpfreaks.com/topic/198285-cant-get-my-head-around-this-join-query/ Share on other sites More sharing options...
chiprivers Posted April 12, 2010 Author Share Posted April 12, 2010 Don't panic guys! I've susssed it! I actually had it right in the first place but I made an entry error in my test database in one of the ID columns so it was not referencing properly! Link to comment https://forums.phpfreaks.com/topic/198285-cant-get-my-head-around-this-join-query/#findComment-1040409 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.