[RESOLVED] Combine rows from 2 tables?

[dee-u]

I want to be able to generate the following excel report and it appears I need to be able to combine rows from 2 tables, subjects and fees, is this possible? Or any other solution you could recommend?

The database is mysql.

[dee-u]

Here's my attempt at using UNION, how can I align their rows?

[techgnome]

That's not what a union is for... if you want the data on the same row, you use a JOIN of some sort... using a common field to join the two tables together.

That said, I'm not sure that's what you want either. It looks like you're trying to dump this data all out in one shot to Excel. That may not be the best way to go. Dump it out in chunks. Do one query to get the class data, dump it to Excel, then get the Fee data and do a second dump to Excel.

-tg

[dee-u]

Yes I am reconsidering just doing it in steps right now as it looks it may not really be possible to retrieve all those data in one sql. Just in case, I'm leaving this open for others to share their recommendations. Thanks TG!

[szlamany]

If you have a Row_Number() like function in MySql you could do it like this - here it is in MS SQL

Create Table #Tbl1 (SomeData varchar(15))
Create Table #Tbl2 (OtherData varchar(15))
Insert into #Tbl1 values ('Test Table 1'),('Test Again 1')
Insert into #Tbl2 values ('Do Table 2'),('Test More 2')


Select X1.*,X2.*
	From (Select Row_Number() Over (Order by SomeData) "RowNum",SomeData
				From #Tbl1) X1
	Left Join (Select Row_Number() Over (Order by OtherData) "RowNum",OtherData
				From #Tbl2) X2 on X2.RowNum=X1.RowNum

Drop TAble #Tbl1
Drop Table #Tbl2

And that yields the following

RowNum	SomeData	RowNum	OtherData
1	Test Again 1	1	Do Table 2
2	Test Table 1	2	Test More 2

I am not handling the situation of having different row numbers in each table. You could deal with that by doing a UNION of the two tables with DISTINCT and not UNION ALL so that becomes the FROM table.

[edit] or maybe just a different join. I've done so many left joins at this point in my life I'm blinded! [/edit]

[dee-u]

I'm still double checking but this appears to be working, thank you so much for the idea SZ! Sadly, I still need to spread.

SELECT X1.*,X2.*
	FROM (SELECT
@row_number:=CASE
        WHEN @customer_no = studentID THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=studentID AS CustomerNumber, unifastotherfees.*
FROM
    `enrollmentsystem`.`unifastotherfees`) AS X1
	LEFT JOIN (SELECT
@row_number:=CASE
        WHEN @customer_no = studentID THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=studentID AS CustomerNumber, unifastsubjects.*
FROM
    `enrollmentsystem`.`unifastsubjects`) AS X2 ON X2.studentID=X1.studentID AND X2.num=X1.num

[szlamany]

What do you mean by "still need to spread"?

[dee-u]

[szlamany]

Oh - got it! Glad it worked for you!!

[dee-u]

Yes it did, I'm really glad you guys are still around to extend some help, it is much appreciated!