Multiple tables with MSA

[Calaesto]

Hi,

I'm stuck on some tables from MS Access. I have the following:

Items: itemId(PK), Naam
Combo: comboId(PK), Naam
Combineren (combination): ID(PK), itemId(FK), comboId(FK)

I want my app to show ComboNaam with ItemsNaam in my dataGridView in the following form:

string queryString = "SELECT Combo.Naam FROM Combo ORDER BY Naam";
LoadDG(queryString);

*NOTE* This code already works fine so don't worry about the dataGridView itself and suchs

Any idea's??

[mikiurban]

If you are trying to figure out how to combine data from multiple tables into a single datagrid, there are two ways to do it: change your query string or manipulate the data within C#.

If you are trying to show one column with all the names from the two tables combined: (a) The SQL way is to use a union to combine the data from two tables, such as "select naam from items union select naam from combo" (b) The .Net way is to take the results from two separate executes and append one to the other like

DataTable dt1 = GetDataTableFromQuery("select ... from Items");
DataTable dt2 = GetDataTableFromQuery("select ... from Combo");
dt1.Rows.Append(dt2.rows);

(note, the above is oversimplified, but gives you the idea.)

If you want to show data from both tables in the same datagrid, such as column 1 is Items name and column 2 is the Combos Name, then (a) SQL way: join the two tables using the Combineren table like "select Combo.Naam, Items.Naam from Items Inner join combineren ... inner join Combo ..." (b) The C# way is just ugly. Trust me, just try to use joins. I'm not sure on Join syntax for Access but that's really what you want to use.

[Calaesto]

Thanks for the push in the back, because I went searching for the INNER JOIN statement as I didn't know how to use. Then I came across a piece of code that I tried and worked.

string queryString = "SELECT Combo.Naam, Items.Naam FROM Items, Combo, Combineren 
WHERE Items.itemId=Combineren.itemId AND Combo.comboId=Combineren.comboId";

It shows exactle want I wanted so thank you very much for the help.

[mikiurban]

Oh, yeah, that's how you do inner joins in access! I knew it was different from SQL but forgot how. Glad I could help!