select query from three tables

[bibiki]

Hey there,
I have these three tables: A, B, and C.

A has a one-to-many relation with both B and C.

Right now, what I am doing is:

I run a select query on A. I put the results on an array, lets say aArray.

For each row in the aArray, I select the corresponding rows from table B and put the results in a two dimensional array bArray.

I do the same for each row in aArray, selecting the corresponding rows from table C.

What I have now is: on array with results from table A, and two two-dimensional arrays with results from B and C.

I do this in my controller. I pass the arrays to my view, and everything is messy. I can make things work this way, but this feels wrong.

Anyone has an idea how I should do this. What I am using is PHP.

thank you in advance.

[Biiim]

select * from `tablea`
left join `tableb` on `tablea`.`id` = `tableb`.`id`
left join `tablec` on `tablea`.`id` = `tablec`.`id`

result set will have the fields of tableb & c attached as columns on the right of tablea matched up by the `id` fields matching.

[bibiki]

alright Biiim,
now I put the result on an array. Because tablea returns at most 1 result, and let us assume that tableb and tablec return, 2 and 3 results correspondingly, I end up with 6 results in total.

Now, I want to have in my view something like this:
[for the first table]1 result
[for the second table]1 result, 2 result
[for the third table]1 result, 2 result, 3 result
In case of more results from table one, proceed the same as above.

Can you give me some tips on how to do this. Should I split the array of six elements as mentioned above into two arrays... one containing only the results from table1, and then for each element in this new array I'd post the elements from other columns in the initial array as I see fit... or is there perhaps a better way?

What do you think Biiim?

[Biiim]

It's all based on what data you are wanting, your request doesn't really make much sense

row 1 contains data from table 1
row 2 contains different data from table 2?

how are the columns supposed to match up it sounds like you really need the 3 separate queries doing it that way.

You need to try and work out what it is you are wanting eg.

selecting data for a customers orders you could do this

SELECT customers.id,customers.name,orders.orderid,orders.value from customers LEFT JOIN orders ON customers.id = orders.custid
WHERE customers.id = 1;

this would pull a result set like so

id,name,orderid,value
1,john,5,50.00
1,john,12,35.00
1,john,25,5.00
1,john,42,60.00

the data we are pulling is the data on each order by client id = 1.

if your table b and c are 2 different things with different fields, eg orders and contacts, you will need 2 queries, not for each row but 2 queries to get all rows from b then all rows from c cause they just can't be matched up they have no relation to each other

[bibiki]

alright, let us say I have this same table as the one you posted:

id, name, orderid, value
1, john, 5, 50.00
1, john, 12, 35.00
1, john, 25, 5.00
1, john, 42, 60.00

Now, assuming that that is on a two-dimensional array, I want to have in my view some thing like this:

User: John
Orders: 5 12 25 42
Values: 50 35 5 60.

Note, John should only display once. Now, one way to do this is for me to put column id on a separate array $ids and then have the loops:

foreach($ids as $i){
     echo $i
     echo "Orders";
     foreach($mainArray as $row){
              echo $row['orderid']
     }
     echo "Values";
     foreach($mainArray as $row){
              echo $row['value']
     }
}

Now, do you know of a better way to do this, or should I just proceed with this idea?

[Biiim]

Ah, you do that like this

<?php
$Q = "SELECT customers.id,customers.name,orders.orderid,orders.value from customers LEFT JOIN orders ON customers.id = orders.custid WHERE customers.id = 1";
$R = mysql_query($Q);
$data = array();

while($row = mysql_fetch_assoc($R)){
	$data[$row['name']] = $row; 
}

foreach($data as $k=>$v){
	echo "Orders for {$k}<br/>\r\n";
	foreach($v as $key=>$value){
		echo "id: {$value['orderid']} value: {$value['orderid']}";
	}
}
?>

john's in there still but no one will ever know ;)

[bibiki]

:)
sounds like that is exactly what I was looking for. thank you Biiim. kind regards.

[nduduzo]

please can you user either C# or VS Biiim