how to select from two tables by first one?

[cristi08]

I have
tbl 1 id_column(1 2 3 )
tbl 2 id_column(1 1 1 2 2 3 3 3)

I need result tbl2 join with tbl 1 values (3 3 3)

Thank you in advance i'm strugling from two days for this.

[tesuji]

If your given example means that table 1 has 3 rows where 1st row ID is 1, 2nd row id is 2 etc and table 2 has 8 rows (!) then you can apply simple inner join to get the result:

select some_rows_from_table1, some_from_table_2 from table1 a join table2 b on a.idcolumn = b.idcolumn where a.idcolumn = 3;

(I have got that funny feeling that the keys of your given example are collected on one row only, isn't it?)

Btw, id of id_column suggests that id_column is kind of key, primary key? If so, id is somewhat misleading for the duplicates 1 1 1, 2 2, 3 3 3. Ok, this is possible if id_column is foreign key.

-- tesu

[cristi08]

If your given example means that table 1 has 3 rows where 1st row ID is 1, 2nd row id is 2 etc and table 2 has 8 rows (!) then you can apply simple inner join to get the result:

select some_rows_from_table1, some_from_table_2 from table1 a join table2 b on a.idcolumn = b.idcolumn where a.idcolumn = 3;

(I have got that funny feeling that the keys of your given example are collected at one row only, isn't it?)

-- tesu

yes and is something more to sey. the first table sum the rows from the second by some criteria. Now I wish to join them together to se the first table results with the second table details about the result in the first one.
I made more tables with result and now I wish to create a sword off report that show echo the detail order and the sum of orders

[cristi08]

other row is holding the primary key this are foreigner

[tesuji]

Sorry, to ask frankly: All your keys are in ONE row, is that so?

[cristi08]

Sorry, to ask frankly: All your keys are in ONE row, is that so?

Of course not the keys are in the key id_column
I wish to show the order total sum from the first table with the detail of order from the second table by the corespondent foreigner key
first table second table
id_f sumofvalu id_s id_f value
1 15 1 1 10
2 10 2 1 5
3 2 5
4 2 5

[tesuji]

Good, now I got it. (If you had the keys arranged as in first example, it wouldn't have been that easy). Sorry, I had mistaken you at first.

Now, pls give clear examples for your both tables, master table and detail table where a one-to-many relationship exists.

You can use CODE-tags for better reading, example using code tags:

d_f     sum of valu id_s id_f value
-----------------------------------
1       15           1    1    10
2       10           2    1    5
3        2           5    ?    ?
4        2           5    ?    ?

Click on (CODE) and put your table between both tags.

Please give meaningful examples for both tables where one can understand the relationship, primary keys and FOREIGN KEYS. Thanks!

-- tesu

[cristi08]

first table		
id_proiect	Values	
1	15	
2	10	
3	5	
second table		
		
id_proiect	primarikey	Values
1                 1                 5
1	          2                 5
1	          3                 5
2	          4	            6
2	          5	            4
3	          6	            5

[cristi08]

the foregneir key for the second table si column id_proiect similar with id_proiect in first table

[cristi08]

I wish to create sword of report with grand total , subtotal 1, subtotal2 and order detail. I have tables readi for each subtotal but I do not know how to put it all togheter on one page and with related data . Thank you for your time I really apreciate

[cristi08]

primarikey	Values
         1	5
         2	5
         3	5
Total idproiect1	15
        4	6
        5	4
Total idproiect2	10
        6	5
Total idproiect3	5
Grand total 1+2+3	30

[cristi08]

this is what i need

[tesuji]

These are meaningful examples, thank you.

Now you can sum-up the values of second table grouped by id proiect, for example:

select idproiect, sum(stvalues) from secondtable group by idproiect order by idproiect ;

/*
Should give:

id_proiect   sum(stvalues)
--------------------------
1              15
2              10
3               5
*/

So what else?

-- tesu

[cristi08]

These are meaningful examples, thank you.

Now you can sum-up the values of second table grouped by id proiect, for example:

select idproiect, sum(stvalues) from secondtable group by idproiect order by idproiect ;
let me try litle wizard that you are

[cristi08]

Tesu you I have table that already has the totals. I need to output several of them like a tree relation

[cristi08]

I need subtotal with criteria from two or many tables

[tesuji]

Well, any idea how to get this?

Hint: You may start from my grouping-by select and combine some selects by UNION.

The first part could look like

select cast(idproject as varchar(10)) || '. Project total: ' as "Projects" , 
sum(stval) as "Totals" from secondtable group by idproject order by "Projects"

what is rather identical with my earlier select. I put the project number 1,2,3 in front of each line to allow easier sorting after union with grand total)

-- tesu

[tesuji]

Tesu you I have table that already has the totals. I need to output several of them like a tree relation

Can you give examples?

[cristi08]

This is a problem for php and mysql, I have in several tables related like a tree by key and foreign columns with total. Now I try to echo on the index page a report that choose total from table 2 ,the field that gave the totalsum for table1, and the Grand total in table 1.
HOW TO CONECT GRAND TOTAL FROM TABLE 1 WITH THE SUBTOTALS IN TABLE 2?

[cristi08]

Example of GRAND TOTAL, TOTAL1 TOTAL2 TOTAL 3 SUBTOTAL 1, SUBTOTAL 2 SUBTOTAL 3
How many chairs we have in our village?
Grand total chairs in houses =total chair in house A + total chair in house B
Total chair in house A = TOTAL CHAIR IN ROOM 1 + TOTAL CHAIR IN ROOM 2
TOTAL CHAIR IN ROOM1 = SUBTOTAL FOR REED CHAIR + SUBTOTAL FOR GREEN CHAIR
TOTAL CHAIR IN ROOM 2 = SUBTOTAL FOR REED CHAIR + SUBTOTAL FOR GREEN CHAIR
---------<AND SO ON FOR HOUSE B ----->---------------------------------------------------------------------------------------I have the totals each one in his own table with his own id I wish to echo them by needs in one final report
Detailed report with chair by color, for each room or village or house
Detailed report with house by nr of chairs in it for each house

[cristi08]

I have tables with totals for each group. now I nedd to put them toghether by diferent criteria, please say you know how to do it.