hllznh

Thank you @kick and @miko. I am reading up on some SQL JOIN to see if there is anything I can do. I had already done something like what @kick sugested for the time being, but it isn't the correct solution. This is what I had so far. Let me know if what I have is better than what @kick sugested in terms of performance. select Transaction.trans_Id, Transaction.dinheiro, Transaction.ficha, Transaction.transaction_dt as data_inserido, b.numBanco, c.numTracker from Transaction inner join (select t.trans_Id, count(t.trans_Id) as numBanco from Transaction t inner join Transaction_Banco as tb on t.trans_Id = tb.trans_id where t.transaction_type_id = 1 and t.usuario_id = $usuarioId group by t.trans_Id) as b ON Transaction.trans_Id = b.trans_id inner join (select t.trans_Id, count(t.trans_Id) as numTracker from Transaction t inner join Transaction_Tracker tt on t.trans_Id = tt.trans_id where t.transaction_type_id = 1 and t.usuario_id = $usuarioId group by t.trans_Id) as c ON Transaction.trans_Id = c.trans_id where transaction_type_id = 1 and usuario_id = 1 group by Transaction.trans_Id, dinheiro, ficha, cotacao, data_inserido; In regards to my problem I am thinking of doing the following: select * from (select 1 as querytype, a.trans_id, a.dinheiro, a.ficha, a.transaction_dt, null as banco_id, null as tracker_id from Transaction as a where a.usuario_id = 1 and transaction_type_id = 1 union select 2 as querytype, a.trans_id, b.dinheiro, null as ficha, b.trans_banco_dt, b.banco_id, null as tracker_id from Transaction as a INNER JOIN Transaction_Banco as b ON a.trans_id = b.trans_id where a.usuario_id = 1 and transaction_type_id = 1 union select 3 as querytype, a.trans_id, null as dinheiro, b.ficha, b.trans_carteira_dt, null as banco_id, b.tracker_id from Transaction as a Inner join Transaction_Tracker as b ON a.trans_id = b.trans_id Where a.usuario_id = 1 and transaction_type_id = 1) as a order by trans_id, querytype; Using this query I can use @miko's solution while looping through the dataset. Basically I will print each section based on the query type. I think the problem is solved. Just give me some feedback about the speed comparison between my query and @kick's query. @miko, what do you think?

Hi fenway, I believe I described the problem just above the bump post. Basically I don't have a clean way to return multiple datasets that are associated with a main dataset without returning multiple duplicate rows in a single query.

bump

Thank you for help. @mikosiko Thank you for your assistance. Although this is a quick and dirty solution, I was accustomed to doing the multiple datasets. In the case you provided it is simple to do if i only have one 1-many relationship in the query. In my case I have 2 1-many relationships and it's doubling everything. For example: As you can see I have the Transaction, Transaction_Tracker, Transaction_Banco tables. Transaction holds the main information, and T_Tracker holds the information as to whom I send the money on the site, and T_Banco holds where I received the information. Transaction t_id t_date t_money t_sitemoney Transaction_Banco t_banco_id t_id t_money t_bancoDate bank_id Transaction_Tracker t_tracker_id t_id t_sitemoney t_sentMoneyDate tracker_id There are cases where someone deposits in two different banks and requests that we send money in two different sites. The total transaction is R$1000 t_money received and $500 t_sitemoney sent on t_date. The actual transactions where 2 deposits one of R$ 300 on bankA and R$ 700 on bankB, although in Brazil there are banks that make the transaction on weekend actually dated on the following monday and this needs to be accounted for. And I sent 2 transfers to two trackers worth $225 and $275 on 2 different t_sentMoneyDates. As you can see in my example since I have 2 left joins and the data ends up being duplicated and the @previous example doesn't really work without getting too messy. With ADO recordsets I wouldn't really have to bring excessive data and I would be able to get rid of this problem easily. I could bring the counts on each type and loop through the dataset returned that many times. But I still hope to find an simpler, better, and more efficient solution.

Hi Judd. Thank you for getting back. I know I posted in the SQL forum but it is more of a PHP mysql question. In my days of using ADO recordsets, I would query the Transaction table with all the clauses and also query the Many Rels tables and I would use the ADO recordset to associate them and I would only have 3 connections to the database at most. Very efficient. With PHP I am not able to find a solution for the above. I have to query the Transactions table and while looping query the Transaction_Banco and Transaction_Tracker tables to get the info related to the transaction. Just feel like there should be a more efficient way of doing this. 1 + n*2 connections is just not efficient. I hope I clarified your doubt.

Thank you for the help. The problem with the double left join is that I have Transaction to Transaction_Banco being a 1 to many relationship and Transaction to Transaction Tracker being a 1 to many relationship. And it is returning way too many rows even when I group by. Select Distinct worked for the query I showed here, but when I try tro retrieve information from the Trasaction_Tracker or Transaction_Banco tables I get too many results. Here is an example: select distinct t.trans_Id, t.dinheiro, t.ficha, transaction_dt, tt.tracker_id, tb.banco_id from Transaction as t LEFT JOIN Transaction_Banco as tb on t.trans_Id = tb.trans_id LEFT JOIN Transaction_Tracker as tt on t.trans_Id = tt.trans_id where t.usuario_id = 1 and transaction_type_id = 1;

Hi all, I gave the topic a broad name because I am not sure how to name. I am now using PHP and mySQL, although I have experience with ADO and ASP.NET. Let's dive into my problem. I have a Transaction Table that has a one to many relationship with 2 different tables: Tracker and Banks. In ASP.NET and ADO I would just send 3 different queries and associate the recordsets with the columns transid. I am having trouble figuring out how to do this in PHP and MYSQL. I am struggling since I am not experienced in php and mysql. Here is the sql statement: select t.trans_Id, t.dinheiro, t.ficha, transaction_dt, transaction_ts from Transaction t LEFT JOIN Transaction_Banco as tb on t.trans_Id = tb.trans_id LEFT JOIN Transaction_Tracker as tt on t.trans_Id = tt.trans_id where t.usuario_id = 1 and transaction_type_id = 1; Basically I have two bank transactions to two different trackers, and it returns 4 results. In the past I would have 3 queries which would return each recordset filted and join them with the trans_id in each of them. I know I can query for the main transaction and loop through them and sub query each time for the info I am looking for, but it just seems like there is a more efficient way of doing this. Basically, I need to show the data like this: TransId, Dinheiro, Ficha, Bank (dinheiro)[], Tracker (ficha)[] I put brackets to explain that I need a list of Banks and Trackers for that transaction displayed on a page, remembering that i will have up to 25 transactions showing. I appreciate and thank anyone who takes the time to help me. Regards Michel