LEFT JOIN Return Multiple Rows

[hllznh]

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;

 

 

+----------+-----------+----------+---------------------+---------------------+

| trans_Id | dinheiro  | ficha    | transaction_dt      | transaction_ts      |

+----------+-----------+----------+---------------------+---------------------+

|        1 |  205.0000 | 100.0000 | 2011-01-31 20:33:24 | 2011-02-04 19:27:00 |

|        2 | 1205.0000 | 593.5000 | 2011-02-04 19:27:37 | 2011-02-04 19:27:37 |

|        2 | 1205.0000 | 593.5000 | 2011-02-04 19:27:37 | 2011-02-04 19:27:37 |

|        2 | 1205.0000 | 593.5000 | 2011-02-04 19:27:37 | 2011-02-04 19:27:37 |

|        2 | 1205.0000 | 593.5000 | 2011-02-04 19:27:37 | 2011-02-04 19:27:37 |

+----------+-----------+----------+---------------------+---------------------+

 

 

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

[sunfighter]

I am not guru here, but maybe this will help.

 

Do not use capitols Transaction_Banco to transaction_banco

 

and from Transaction t  remove the t.

 

see what that does for you because just looking at it I think it should work.

[awjudd]

I'm guessing that the left join is returning multiple rows.  You could either GROUP BY all of the fields or SELECT DISTINCT.

 

Either way will work at killing the duplicates.

 

~judda

[hllznh]

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;

+----------+-----------+----------+---------------------+------------+----------+

| trans_Id | dinheiro  | ficha    | transaction_dt      | tracker_id | banco_id |

+----------+-----------+----------+---------------------+------------+----------+

|        1 |  205.0000 | 100.0000 | 2011-01-31 20:33:24 |          1 |        2 |

|        2 | 1205.0000 | 593.5000 | 2011-02-04 19:27:37 |          1 |        1 |

|        2 | 1205.0000 | 593.5000 | 2011-02-04 19:27:37 |          2 |        1 |

|        2 | 1205.0000 | 593.5000 | 2011-02-04 19:27:37 |          1 |        3 |

|        2 | 1205.0000 | 593.5000 | 2011-02-04 19:27:37 |          2 |        3 |

+----------+-----------+----------+---------------------+------------+----------+

[awjudd]

If you have a 1-many relationship as you do, including the values will expand based on fact that those are different values, therefore making the sets different.

 

You could do a GROUP_CONCAT if you wanted to in order to combine them into one record ... but now I'm not sure what you are trying to do.

 

~judda

[hllznh]

If you have a 1-many relationship as you do, including the values will expand based on fact that those are different values, therefore making the sets different.

 

You could do a GROUP_CONCAT if you wanted to in order to combine them into one record ... but now I'm not sure what you are trying to do.

 

~judda

 

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.

[mikosiko]

the first select that you have seems to be correct..... few days ago I did answer a question almost identical as yours.... look here

http://www.phpfreaks.com/forums/mysql-help/problem-with-multiple-result-sets/

 

and look for the code I did provide

[Philip]

Can you provide us with an example of a sample result set you are looking for?

[hllznh]

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.

[hllznh]

bump

[fenway]

What's the issue now?

[hllznh]

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.

 

 

[mikosiko]

I strongly recommend you to read how SQL JOINS work..

[kickstart]

Hi

 

The joins are working as I would expect.

 

However for what you want something like this would do it:-

 

SELECT t.trans_Id, t.dinheiro, t.ficha, transaction_dt, transaction_ts 
FROM Transaction t 
LEFT JOIN (SELECT trans_id, MAX(transaction_dt) FROM Transaction_Banco GROUP BY trans_id) AS tb ON t.trans_Id = tb.trans_id 
LEFT JOIN (SELECT trans_id, MAX(transaction_ts) FROM Transaction_Tracker GROUP BY Trans_id) as tt ON t.trans_Id = tt.trans_id 
WHERE t.usuario_id = 1 
AND transaction_type_id = 1; 

 

Not tested so probably some typos but I hope that gives you the idea.

 

All the best

 

Keith

[mikosiko]

@Keith

 

your query doesn't solve what the OP requested in his first post, which was:

 

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.

[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?

[mikosiko]

If I understood correctly your problem I believe that you are extremely over thinking the problem/solution... but just to be sure why you don't post:

 

- Real data example of your Transaction table.

- Real data example of your Transaction_Banco table (choose records related to Transaction)

- Real data example of your Transaction_Tracker table (choose records related to Transaction)

 

-and finally using the posted data required before post the data output that you want to have.

 

that way we can clarify better the problem and help you more.

 

BTW: Those select look very nasty to me 

[kickstart]

Hi

 

Fair point. In which case it is just a php issue to suppress the output of repeating values in columns.

 

All the best

 

Keith