CODEWITHSUNDEEP
mysqldatabasedelphidelphi-2009mydac
Kevin Dungs
Kevin Dungs

Reputation: 1577

Delphi 2009, MyDAC and relational database

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:

Item

Storage

StorageItem

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 :)

Upvotes: 2

Views: 1841

Answers (3)

Mohammed Nasman
Mohammed Nasman

Reputation: 11070

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

Upvotes: 1

Mihaela
Mihaela

Reputation: 2502

  • With StorageItem you created a
    many-to-many relationship. If you
    need just one-to-many (many storages are related to one item, but you
    don't need the vice versa), then you may just add another field to the Storage table (item_id) that would be a foreign key for Items table. Then you create an index on item_id in Storage table, and connect the two tables in
    master-detail relationship.
  • If you do need many-to-many then you may add a query component with SQL
    (select * from StorageItem where
    item_id := :current_storage_id), and current_storage_id is your query's
    parameter.

Upvotes: 2

Mason Wheeler
Mason Wheeler

Reputation: 84650

I'm not familiar with MyDAC personally, but most dataset components have some way to establish master-detail relationships. Check if there's a MasterSource property on your dataset, or some similar way to link a detail dataset to a master dataset. If not, you could use a TDatasetField to establish a link, and filter the nested dataset to only display the right records.

As for ID column names, it's a good idea to give a descriptive name to each field, so you can tell by looking at the code that you've got your links right. If you call your id column "id", that could be any id column, and that could get confusing if you start passing around references to datasets. But if it's called item_id every time, (not item_id sometimes and id_item sometimes) then you always know exactly what you're looking at. It makes it easier to know that your code is right, too. A filter that says "master.item_id = detail.item_id" is easier to read that "master.id = detail.item_id". That could be wrong and fail silently if master is assigned to the wrong dataset, for example.

Upvotes: 1

Related Questions