Master Detail

jalin · Post by **jalin** » Mon 29 Dec 2008 03:38

I have a master table:

MASTER TABLE XXXXX
Clave_Inm=PrimaryKey
.
.

A Detail Table

DETAIL TABLE YYYYY
Clave_Inm
Clave_Dep
.
.
Both are the primary key

A third table is a detail table of the YYYYY table

SUBDETAIL TABLE ZZZZZ
Clave_Inm
Clave_Dep
AutoIncField
.
.

The 3 fields are the primary key

I have 3 TmyQuery components, each for every table.

The mastersource and master fields properties works ok from table YYYYY to XXXXX

BUT I get an error "#23000 'Clave_Inm' in where clause is ambiguous" when I try to link the master source and masterfields from ZZZZZ to YYYYY

Any Idea?

Thanks
Alejandro

Dimon · Post by **Dimon** » Mon 29 Dec 2008 14:48

Please try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

waheed · Post by **waheed** » Tue 23 Feb 2010 11:36

D2007, MyDAC 5.90.0.54, Windows Vista
This is not solved till now. The similarity between the masterfield and detailfield causes a problem.
When ever I activate query AuthorOtherNames I get this error:

#23000Column 'author_id' in where clause is ambiguous.
You need to create two table in MySQL

Code: Select all

CREATE TABLE `m_author` (
  `author_id` mediumint(9) NOT NULL auto_increment,
  `full_author_name` varchar(50) collate latin1_bin default NULL,
  `mkrf_id` int(11) default NULL,
  PRIMARY KEY  (`author_id`),
  KEY `mkrf_id` (`mkrf_id`)
) ENGINE=MyISAM AUTO_INCREMENT=29264 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

CREATE TABLE `mkt_other_authors` (
  `mkcrd_id` mediumint(8) unsigned NOT NULL default '0',
  `author_id` mediumint(9) NOT NULL default '0',
  `author_txt` varchar(100) collate latin1_bin NOT NULL default '',
  PRIMARY KEY  (`mkcrd_id`,`author_id`),
  KEY `author_id` (`author_id`),
  KEY `author_txt` (`author_txt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

Here is the form

Code: Select all

object Form2: TForm2
  Left = 0
  Top = 0
  Caption = 'Form2'
  ClientHeight = 290
  ClientWidth = 190
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Authors: TMyQuery
    Connection = MyConnection1
    SQL.Strings = (
      'select mkrf_id, author_id,'
      '  full_author_name as name'
      'from m_author'
      'order by 3')
    Options.EnableBoolean = False
    Active = True
    Left = 70
    Top = 64
    object Authorsmkrf_id: TIntegerField
      FieldName = 'mkrf_id'
      Origin = 'm_author.mkrf_id'
    end
    object Authorsname: TStringField
      FieldName = 'name'
      Origin = 'm_author.full_author_name'
      Size = 200
    end
    object Authorsauthor_id: TIntegerField
      AutoGenerateValue = arAutoInc
      FieldName = 'author_id'
      Origin = 'm_author.author_id'
    end
  end
  object dsAuthors: TDataSource
    DataSet = Authors
    Left = 70
    Top = 110
  end
  object AuthorOtherNames: TMyQuery
    Connection = MyConnection1
    SQL.Strings = (
      'SELECT '
      '  same_author.author_id ,'
      '  m_author.full_author_name'
      'FROM'
      '  same_author'
      '  INNER JOIN m_author ON '
      '    (same_author.author_id = m_author.author_id)'
      'where same_author.author_id=:author_id')
    MasterSource = dsAuthors
    MasterFields = 'author_id'
    DetailFields = 'author_id'
    Left = 73
    Top = 172
    ParamData = 
  end
  object dsAuthorOtherNames: TDataSource
    DataSet = AuthorOtherNames
    Left = 73
    Top = 220
  end
  object MyConnection1: TMyConnection
    Database = 'makhtoot'
    Username = 'root'
    Server = 'localhost'
    Connected = True
    Left = 71
    Top = 10
  end
end

Dimon · Post by **Dimon** » Wed 24 Feb 2010 09:54

To solve the problem you should create master/detail relationship by adding parameters manually. For this only clear the AuthorOtherNames.MasterFields and AuthorOtherNames.DetailFields properties.
You can find detailed information about it in the "Working with Master/Detail Relationships" topic of MyDAC help.

waheed · Post by **waheed** » Wed 24 Feb 2010 13:51

Thanks, this worked.
BTW. There was a mistake in my othernames query, I didn't include the other id in the select, there.

Dimon · Post by **Dimon** » Thu 25 Feb 2010 07:27

I am happy that this problem has been solved. If any other questions come up, please contact me.

andrea.m86 · Post by **andrea.m86** » Wed 07 Mar 2012 11:40

Hello.
what do you mean with "adding parameters manually"?
I have a very similar problem.
I have a query that works normally without using any master-detail.
When I add the master and detail field i get the error
#23000Column 'progressivo' in where clause is ambiguous.
In that case i have the single parameter of the query, the master field and the detail field with the same name.

AndreyZ · Post by **AndreyZ** » Wed 07 Mar 2012 14:12

There are two ways of establishing master/detail relationship: using parameters, and using the MasterFields and DetailFields properties. You can find descriptions and examples of both ways in the "Working with Master/Detail Relationships" article of the MyDAC documentation.