Joining 3 tables

[HDRG]

If I have a table like this: Table1
ID - sequence - value
1     1         6        
1     2         4           
2     1         3       
2     2         2        
3     2         6        
4     1         5    
5...

Table2:
ID - Firstname
1     Jack
2     John
3     Mark
4     Mary

Querry Result I hope for:
ID - Firstname -  seq1value - seq2value
1       Jack            6           4
2       John            3           2
3       Mark            NULL        2
4       Mary            5           NULL 
5

the statement I have is:

select Table2.ID,Table2.Firstname, day1.seq1value,day2.seq2value from Table2,
(select Table2.ID,Table2.Firstname,Table1.value as seq1value from Table1,Table2
where Table1.ID=Table2.ID and Table1.sequence='1') day1,
(select Table2.ID,Table2.Firstname,Table1.value as seq1value from Table1,Table2
where Table1.ID=Table2.ID and Table1.sequence='2') day2 
where Table1.ID=day1.ID and Table2.ID=day2.ID group by Table2.ID;

This skips the records which have a null in them and presents the other records.
How do i get my query to look like the result I hope for?

THank you

[gusano79]

That Cartesian product looks suspicious... you shouldn't have to group results for a query like this.

I'd left join on your subqueries instead.

[gusano79]

Something like this:

SELECT
    t2.ID,
    t2.Firstname,
    day1.value AS seq1value,
    day2.value AS seq2value
FROM Table2 t2
LEFT JOIN Table1 day1
    ON day1.ID = t2.ID
    AND day1.sequence = 1
LEFT JOIN Table1 day2
    ON day2.ID = t2.ID
    AND day2.sequence = 2

[1stDAN]

MySQL has group_concat() function what allows grouped values to be listed just in one row. Following is an example for group_concat() applied on your both tables:

select a.id, a.Firstname, group_concat(b.value order by b.value desc separator ' ') as SeqVal from table2 a left join table1 b on a.id = b.id group by b.id;

Result should be like:

id Firstname SeqVal
1 Jack       6   4
2 John       3   2
3 Mark       6
4 Mary       5  

Unfortunately above select will not solve your problem completely for placeholders null are missing in resultset. If you can't get your wanted result with the group_concat() function, possibly you must write a stored procedure in order to produce the missing null entries.

[HDRG]

Got it.. the table with the most data should always be befreo the table with the least data and it should always be the right combination of Right and left joins. In my case, the order was
table2
right join
table1(and its conditions, this column has the most data)
left join
table1(and its conditions, this column has less data than the previous one)