Greystoke Posted June 20, 2011 Share Posted June 20, 2011 Hi, I need some help getting results from 2 tables. Table sales: Table bonus: At the moment I have: $affiliate_sales_query = tep_db_query("select * from " . TABLE_AFFILIATE_SALES . " where affiliate_payment_id = 7 order by affiliate_payment_date desc"); while ($affiliate_sales = tep_db_fetch_array($affiliate_sales_query)) { ?> <tr class="dataTableRow"> <td class="dataTableContent" align="right" valign="top"><?php echo $affiliate_sales['affiliate_orders_id']; ?></td> <td class="dataTableContent" align="center" valign="top"><?php echo tep_date_short($affiliate_sales['affiliate_date']); ?></td> <td class="dataTableContent" align="right" valign="top"><b><?php echo $currencies->display_price($affiliate_sales['affiliate_value'], ''); ?></b></td> <td class="dataTableContent" align="right" valign="top"><?php echo $affiliate_sales['affiliate_percent']; ?><?php echo ENTRY_PERCENT; ?></td> <td class="dataTableContent" align="right" valign="top"><b><?php echo $currencies->display_price($affiliate_sales['affiliate_payment'], ''); ?></b></td> </tr> <?php } ?> This outputs the sales table like: How can I get it to also output the bonus table in the same report. Quote Link to comment Share on other sites More sharing options...
fugix Posted June 20, 2011 Share Posted June 20, 2011 Use a union. For correct syntax refer here Quote Link to comment Share on other sites More sharing options...
Adam Posted June 21, 2011 Share Posted June 21, 2011 A join would be more fitting here, fugix. Greystoke I'm guessing the "bonus" table isn't always going to have a matching row for each affiliate? (Thinking logically about a bonus anyway...) If that's the case you should use a LEFT JOIN, in which the query will return entries from the first/left table, even if there's not a matching row in the second/right table: select sales.*, bonus.* from sales left join bonus on (sales.affiliate_id = bonus.affiliate_id) To avoid any ambiguity errors, you'll need to reference the table name (or create an alias to each table) for each column you use in the query. Also it's worth noting that you should avoid using "select *" - here's why. Quote Link to comment Share on other sites More sharing options...
Greystoke Posted June 21, 2011 Author Share Posted June 21, 2011 Hi, Thanks for your replies. I went with the UNION option first: $affiliate_sales_query = tep_db_query("(select affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment from " . TABLE_AFFILIATE_SIGNUP_BONUS . " where affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by affiliate_payment_date desc) UNION (select affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment from " . TABLE_AFFILIATE_SALES . " where affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by affiliate_payment_date desc)"); This worked. Then after reading MRAdam post I tried to use LEFT JOIN, but it only outputted the sales info not both. $affiliate_sales_query = tep_db_query("select asb.*, s.* from " . TABLE_AFFILIATE_SALES . " s left join " . TABLE_AFFILIATE_SIGNUP_BONUS . " asb on (s.affiliate_id = asb.affiliate_id) where s.affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by asb.affiliate_payment_date desc"); Can anyone help with this please. Quote Link to comment Share on other sites More sharing options...
fugix Posted June 21, 2011 Share Posted June 21, 2011 Unions are designed to match two tables field by field. If this is not want you want, then I would use a join as MrAdam recommended. What exactly do you need help with now Quote Link to comment Share on other sites More sharing options...
Greystoke Posted June 21, 2011 Author Share Posted June 21, 2011 Hi fugix, The "bonus" table isn't always going to have a matching row for each affiliate in the "sales" table, I was trying do what MrAdam said, but I can't figure out how to get the LEFT JOIN to work correctly. Quote Link to comment Share on other sites More sharing options...
ebmigue Posted June 21, 2011 Share Posted June 21, 2011 .... Quote Link to comment Share on other sites More sharing options...
fugix Posted June 21, 2011 Share Posted June 21, 2011 There are several different joins. Read here for syntax Quote Link to comment Share on other sites More sharing options...
Adam Posted June 21, 2011 Share Posted June 21, 2011 When I first suggested a join I thought that the tables were relative, in that the bonus table provided more details for the sales table, linked by the affiliate_id. Now after looking closer at the data I'm not so sure, and a union might the better option. What is the relationship between the two tables? How do you want the data to be merged into the report? Quote Link to comment Share on other sites More sharing options...
Greystoke Posted June 21, 2011 Author Share Posted June 21, 2011 When I first suggested a join I thought that the tables were relative, in that the bonus table provided more details for the sales table, linked by the affiliate_id. Now after looking closer at the data I'm not so sure, and a union might the better option. What is the relationship between the two tables? How do you want the data to be merged into the report? The tables are been used in the invoice report to show a breakdown of the payments to the affiliate. The 2 tables are matched using the affiliate_payment_id. In the original invoice it only showed the sales breakdown. The affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment are used to for the report. This is how it looks using this: $affiliate_sales_query = tep_db_query("(select affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment from " . TABLE_AFFILIATE_SIGNUP_BONUS . " where affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by affiliate_payment_date desc) UNION (select affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment from " . TABLE_AFFILIATE_SALES . " where affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by affiliate_payment_date desc)"); Quote Link to comment Share on other sites More sharing options...
Adam Posted June 21, 2011 Share Posted June 21, 2011 The tables are been used in the invoice report to show a breakdown of the payments to the affiliate. The 2 tables are matched using the affiliate_payment_id. In the original invoice it only showed the sales breakdown. Sorry, that's still not a very clear description of what relationship the two tables have. There's matching affiliate ID's I can see in your screen-shots. Are these actually the same affiliate, or is one table a different type of affiliate? Does the bonus table extend the sales table or is it an alternate? In the report should they show combined into the same row, or over two, or over two+ rows? Quote Link to comment Share on other sites More sharing options...
Greystoke Posted June 21, 2011 Author Share Posted June 21, 2011 Sorry, that's still not a very clear description of what relationship the two tables have. There's matching affiliate ID's I can see in your screen-shots. Are these actually the same affiliate, or is one table a different type of affiliate? Does the bonus table extend the sales table or is it an alternate? In the report should they show combined into the same row, or over two, or over two+ rows? The "sales" Table: The "bonus" Table: The affiliate_id, affiliate_billing_staus, affiliate_payment_id are the same in both tables. The affiliate _id in both table is the same person. This is how I want it displayed: Using the Union option it displays correctly, but the "bonus" table may not always have a matching row for each affiliate. If the affiliate has no sales then the bonus won't be paid as that affiliate_id won't be checked for in the "bonus" table. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.