I have quite a problem concerning the use of relational database concepts in Delphi 2009 with MyDAC.
I have a database structure that looks somehow like the following:
Now when I have an active dataset from "Item" how can I display all associated Storages in for example a DBGrid?
By the way: Would it be better to not use "id" in every table but to alter it and use something like for example "id_item" or "id_storage"?
Thank you in advance :)
Select a.ID, b.Name, a.Place
from StorageItem a
inner join Storage b
on (a.id = b.id)
the above query will return all the items in StorageItem table with it's name, now if you want to filter it to return only items for a specific item add where clause to be like
Select a.ID, b.Name, a.Place
from StorageItem a
inner join Storage b
on (a.id = b.id)
where a.item_id = 1 -- place the item id here
you can use where with parameters such as:
MyQuery.Sql.Text := ' Select a.ID, b.Name, a.Place from StorageItem a
+ ' inner join Storage b on (a.id = b.id) '
+ ' where a.item_id = :ItemNo ';
MyQuery.ParamByName('ItemNo').asInteger := 1;
MyQuery.Open;
and assign the query above to dbGrid
also you can use MasterSource property to make the relations without using the "where" part