Jump to content

Can't get my head around this 'JOIN' query!


chiprivers

Recommended Posts

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.