SQL statement with several different tables

[manared]

Hi, I'm using VS 2008 and SQL. I have an application that uses several different tables. In my main table, I have the main/basic fields that I need. THere are several other tables that have detail information in them. So in my main table I create ONE entry that has an id. In the detail tables, there can be several entries for that one id. (meaning the tables can be linked to the main table through the one id.) What I want to do is create a datatable with all the information related to the one entry in the main table. My goal is to actually create a Crystal Report from this datatable. My problem right now is that i'm not quite sure how to create the SQL statement to achieve this or if I even can achieve this. Right now, I'm grabbing everything from every table (for that specific ID) and a lot of the information repeats itself for however many entries are in the detail tables. So, I know this is not the way to do it. Here's my sql string anyway for reference:

strSQL = "SELECT tblERmain.*, tblERdaily.*, tblERautoMileage.*, tblERentertainment.*, tblERmisc.*, tblERpersonal.*, tblERtravelDetail.*, 
tblERledger.* FROM tblERmain INNER JOIN tblERdaily ON (tblERmain.er_id = tblERdaily.er_id) INNER JOIN tblERautoMileage ON (tblERmain.er_id = tblERautoMileage.er_id) INNER JOIN tblERentertainment ON (tblERmain.er_id = 
tblERentertainment.er_id) INNER JOIN tblERmisc ON (tblERmain.er_id = tblERmisc.er_id) INNER JOIN tblERpersonal ON (tblERmain.er_id = tblERpersonal.er_id) INNER JOIN tblERtravelDetail ON (tblERmain.er_id = 
tblERtravelDetail.er_id) INNER JOIN tblERledger ON (tblERmain.er_id = tblERledger.er_id) WHERE (tblERmain.er_id = '" & lblerid.Text & "')"

I'm sure someone will need more information, but if anyone can help me out at all, I'd really appreciate it. And let me know what information/code is needed to help me achieve my goal. Thanks!

 

[giadich]

the textbox is too wide and it's very hard to follow your description let alone the code... for that I will not look at the sql statement but... I'll give you some tips to work with sql...

If you are new to using joins and stuff, try start out with small joins, 2 tables to start with...

select FieldA1, FieldB1 from TableA as A inner join TableB as B on A.ID=B.ID

You can put that in a view, then use this view yet to join another table... and add addtional column once you know for sure that the view/join is working properly...this way even if you have to join many tables to accomplish the job, your code is very clean & clear...

once you feel comfortable and get a better understanding of the whole join thingy, you can go back and clean the views up.

don't start out include four, five different tables if you are not sure how they joins together...

hope it helps...

 

[Arg81]

You are talking about Related data.

They shouldn't be in the same dataTable. You create DataRelations to link the tables. This then allows you to create the "parent-child" model on your form. Simply have 1 dataTable for each table from your DB, and if they don't automatically link, put in a DataRelation to link on the ID.

Even using Crystal Reports, it creates a relation between so that only the relevant "child" rows are shown for the "parent" ID.