ODBC -- Call Failed with error # 547

[Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
statement conflicted with COLUMN FOREIGN KEY
constraint 'Attachment_FK00'. The conflict occured in
database 'assay', table 'suborders', column 'suborderid'.
(#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
statement has been terminated (#3621)
when user try to upload the document thought Access 2002
to SQL database, occasionally user could get this error.
did someone experience this error before'
thanks,
wilsonHi
You need to look at the data or insert order/method as your FK constraint is
not being satisfied.
John
"Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:1a51b01c44e4d$4543dc20$a501280a@.phx
.gbl...
> [Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
> statement conflicted with COLUMN FOREIGN KEY
> constraint 'Attachment_FK00'. The conflict occured in
> database 'assay', table 'suborders', column 'suborderid'.
> (#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
> statement has been terminated (#3621)
> when user try to upload the document thought Access 2002
> to SQL database, occasionally user could get this error.
> did someone experience this error before'
> thanks,
> wilson|||Hi john,
thanks for your quick response.
why it is working sometimes, but sometimes it is not.
wilson
>--Original Message--
>Hi
>You need to look at the data or insert order/method as
your FK constraint is
>not being satisfied.
>John
>"Wilson" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1a51b01c44e4d$4543dc20$a501280a@.phx
.gbl...
column 'suborderid'.[vbcol=seagreen]
The[vbcol=seagreen]
2002[vbcol=seagreen]
error.[vbcol=seagreen]
>
>.
>|||Hi
That is a feature of the data you are inserting. Try loading this into a
copy of the table (i.e. without the FK) and then check the data with a
statement similar to:
SELECT * FROM TmpTable
WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1a63301c44e5b$c829ce70$a401280a@.phx
.gbl...[vbcol=seagreen]
> Hi john,
> thanks for your quick response.
> why it is working sometimes, but sometimes it is not.
>
> wilson
> your FK constraint is
> message
> column 'suborderid'.
> The
> 2002
> error.|||thanks, John.
But now we got another error:
Syntax error (missing operator) in query
expression '[SubOrderID]='
any ideas'
thanks,
wilson
>--Original Message--
>Hi
>That is a feature of the data you are inserting. Try
loading this into a
>copy of the table (i.e. without the FK) and then check
the data with a
>statement similar to:
>SELECT * FROM TmpTable
>WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
>John
><anonymous@.discussions.microsoft.com> wrote in message
> news:1a63301c44e5b$c829ce70$a401280a@.phx
.gbl...
in[vbcol=seagreen]
INSERT[vbcol=seagreen]
in[vbcol=seagreen]
Server][vbcol=seagreen]
>
>.
>|||Hi
You may want to start ODBC logging and see what the statements are. At a
guess you either are creating the statements dynamically and that is not
working correctly e.g. as missing 'AND' or alternatively that your data has
a character that needs escaping in it e.g as a quote.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1aa3301c44f0b$bc9fcb80$a301280a@.phx
.gbl...[vbcol=seagreen]
> thanks, John.
> But now we got another error:
> Syntax error (missing operator) in query
> expression '[SubOrderID]='
> any ideas'
> thanks,
> wilson
> loading this into a
> the data with a
> in
> INSERT
> in
> Server]

ODBC -- Call Failed with error # 547

[Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
statement conflicted with COLUMN FOREIGN KEY
constraint 'Attachment_FK00'. The conflict occured in
database 'assay', table 'suborders', column 'suborderid'.
(#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
statement has been terminated (#3621)
when user try to upload the document thought Access 2002
to SQL database, occasionally user could get this error.
did someone experience this error before?
thanks,
wilson
Hi
You need to look at the data or insert order/method as your FK constraint is
not being satisfied.
John
"Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:1a51b01c44e4d$4543dc20$a501280a@.phx.gbl...
> [Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
> statement conflicted with COLUMN FOREIGN KEY
> constraint 'Attachment_FK00'. The conflict occured in
> database 'assay', table 'suborders', column 'suborderid'.
> (#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
> statement has been terminated (#3621)
> when user try to upload the document thought Access 2002
> to SQL database, occasionally user could get this error.
> did someone experience this error before?
> thanks,
> wilson
|||Hi john,
thanks for your quick response.
why it is working sometimes, but sometimes it is not.
wilson
>--Original Message--
>Hi
>You need to look at the data or insert order/method as
your FK constraint is
>not being satisfied.
>John
>"Wilson" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1a51b01c44e4d$4543dc20$a501280a@.phx.gbl...
column 'suborderid'.[vbcol=seagreen]
The[vbcol=seagreen]
2002[vbcol=seagreen]
error.
>
>.
>
|||Hi
That is a feature of the data you are inserting. Try loading this into a
copy of the table (i.e. without the FK) and then check the data with a
statement similar to:
SELECT * FROM TmpTable
WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1a63301c44e5b$c829ce70$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi john,
> thanks for your quick response.
> why it is working sometimes, but sometimes it is not.
>
> wilson
> your FK constraint is
> message
> column 'suborderid'.
> The
> 2002
> error.
|||thanks, John.
But now we got another error:
Syntax error (missing operator) in query
expression '[SubOrderID]='
any ideas?
thanks,
wilson
>--Original Message--
>Hi
>That is a feature of the data you are inserting. Try
loading this into a
>copy of the table (i.e. without the FK) and then check
the data with a[vbcol=seagreen]
>statement similar to:
>SELECT * FROM TmpTable
>WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:1a63301c44e5b$c829ce70$a401280a@.phx.gbl...
in[vbcol=seagreen]
INSERT[vbcol=seagreen]
in[vbcol=seagreen]
Server]
>
>.
>
|||Hi
You may want to start ODBC logging and see what the statements are. At a
guess you either are creating the statements dynamically and that is not
working correctly e.g. as missing 'AND' or alternatively that your data has
a character that needs escaping in it e.g as a quote.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1aa3301c44f0b$bc9fcb80$a301280a@.phx.gbl...[vbcol=seagreen]
> thanks, John.
> But now we got another error:
> Syntax error (missing operator) in query
> expression '[SubOrderID]='
> any ideas?
> thanks,
> wilson
> loading this into a
> the data with a
> in
> INSERT
> in
> Server]

Obtaining the latest record by date

Hi,

I have two tables, one stores items, and another one that stores history for the items from first table. They are related by a foreign key from items table.

In one of my queries, I need to obtain the latest history entry from table 2. For this I use MAX(RecordDate) aggregate. Another way that I know is:

SELECT TOP(1) RecordDate, HistoryID, ....
FROM ProductHistory
WHERE (SerialNumber = '20070101000010')
ORDER BY RecordDate DESC, HistoryID DESC

The query is part of a larger query where I obtain more data from other tables.

The reason I prefer the second option is that, at times I have multiple entries for the same item on the same date (no time info). In such cases, the data returned is the latest entry (HistoryID) in to the system of the rows with same date, thanks to ORDER BY clause. Another reason is that I can select any columns, whereas in the first query type I need to use subquery to obtain other fields after I find my relevant row.

My question is, is there any problem using the second method that I am not aware of? Is this method reliable (TOP(1))? I appreciate if you can guide me to the right method. Thanks!

You're absolutely fine to use TOP in the way that you have done.

The most common mistake when using TOP is to omit an ORDER BY clause where it should really be included, which can make the results unpredicatable and inconsistent. As you have included an ORDER BY clause then you'll be fine.

Chris

|||

Thank you for your answer Chris. My main concern was whether TOP was applied before ORDER BY or to the result of the query at the end. It seems it is applied after all the filters are applied, showing only the portion of the result matching the whole query. Please corrent me if I am wrong. I will mark your response as the answer.

Migrant

Obtaining Primary and Foreign Key information from a table

Hi,
I would like to query the system tables in order to obtain all the Primary
and Foreign keys for any particular table.
I have tried using the syscolumns.colstat field but it only outputs zero. I
understand it should output 1 if the column is a primary key.
Also I do not know I do not know how to obtain Foreign key info on a table.
All help is much appreciated.
Kind regards,
Polly AnnaWhat version of SQL Server are you using?
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> Hi,
> I would like to query the system tables in order to obtain all the Primary
> and Foreign keys for any particular table.
> I have tried using the syscolumns.colstat field but it only outputs zero.
> I
> understand it should output 1 if the column is a primary key.
> Also I do not know I do not know how to obtain Foreign key info on a
> table.
> All help is much appreciated.
> Kind regards,
> Polly Anna|||Hi Aaron,
I am using my query against both a SQL2000 and SQL2005 db.
I attach the query below.
Many thanks for your help.
Polly Anna
select distinct column_name as Column_Name,
data_type,
c.length,
CASE c.colStat
WHEN 1 THEN 'Y' ELSE ''
END AS PrimaryKey
from information_schema.columns as isc
left join sysobjects o
on isc.table_name = o.name
left join syscolumns c
on o.id = c.id and c.name = isc.column_name
where table_name = 'Dentist'
"Aaron Bertrand [SQL Server MVP]" wrote:
> What version of SQL Server are you using?
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> > Hi,
> >
> > I would like to query the system tables in order to obtain all the Primary
> > and Foreign keys for any particular table.
> >
> > I have tried using the syscolumns.colstat field but it only outputs zero.
> > I
> > understand it should output 1 if the column is a primary key.
> >
> > Also I do not know I do not know how to obtain Foreign key info on a
> > table.
> >
> > All help is much appreciated.
> >
> > Kind regards,
> >
> > Polly Anna
>
>|||Polly
SS2005
select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
ORDINAL_POSITION;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> Hi Aaron,
> I am using my query against both a SQL2000 and SQL2005 db.
> I attach the query below.
> Many thanks for your help.
> Polly Anna
> select distinct column_name as Column_Name,
> data_type,
> c.length,
> CASE c.colStat
> WHEN 1 THEN 'Y' ELSE ''
> END AS PrimaryKey
> from information_schema.columns as isc
> left join sysobjects o
> on isc.table_name = o.name
> left join syscolumns c
> on o.id = c.id and c.name = isc.column_name
> where table_name = 'Dentist'
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> What version of SQL Server are you using?
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> > Hi,
>> >
>> > I would like to query the system tables in order to obtain all the
>> > Primary
>> > and Foreign keys for any particular table.
>> >
>> > I have tried using the syscolumns.colstat field but it only outputs
>> > zero.
>> > I
>> > understand it should output 1 if the column is a primary key.
>> >
>> > Also I do not know I do not know how to obtain Foreign key info on a
>> > table.
>> >
>> > All help is much appreciated.
>> >
>> > Kind regards,
>> >
>> > Polly Anna
>>|||Hi Uri,
yes thank you, that is brilliant. It works in SS2005. You don't happen to
have the script for SQL2000?
Thank you very much indeed.
Kind regards,
Polly Anna
"Uri Dimant" wrote:
> Polly
> SS2005
> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> from sys.key_constraints as k
> join sys.tables as t
> on t.object_id = k.parent_object_id
> join sys.schemas as s
> on s.schema_id = t.schema_id
> join sys.index_columns as ic
> on ic.object_id = t.object_id
> and ic.index_id = k.unique_index_id
> join sys.columns as c
> on c.object_id = t.object_id
> and c.column_id = ic.column_id
> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> ORDINAL_POSITION;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> > Hi Aaron,
> >
> > I am using my query against both a SQL2000 and SQL2005 db.
> >
> > I attach the query below.
> >
> > Many thanks for your help.
> >
> > Polly Anna
> >
> > select distinct column_name as Column_Name,
> > data_type,
> > c.length,
> > CASE c.colStat
> > WHEN 1 THEN 'Y' ELSE ''
> > END AS PrimaryKey
> >
> > from information_schema.columns as isc
> > left join sysobjects o
> > on isc.table_name = o.name
> > left join syscolumns c
> > on o.id = c.id and c.name = isc.column_name
> > where table_name = 'Dentist'
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> What version of SQL Server are you using?
> >>
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> > Hi,
> >> >
> >> > I would like to query the system tables in order to obtain all the
> >> > Primary
> >> > and Foreign keys for any particular table.
> >> >
> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> > zero.
> >> > I
> >> > understand it should output 1 if the column is a primary key.
> >> >
> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> > table.
> >> >
> >> > All help is much appreciated.
> >> >
> >> > Kind regards,
> >> >
> >> > Polly Anna
> >>
> >>
> >>
>
>|||SELECT t.TABLE_SCHEMA,
t.TABLE_NAME, c.CONSTRAINT_NAME,
c.CONSTRAINT_TYPE, k.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
AND c.TABLE_NAME = k.TABLE_NAME
WHERE
c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY 1,2,4 DESC,3;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> Hi Uri,
> yes thank you, that is brilliant. It works in SS2005. You don't happen to
> have the script for SQL2000?
> Thank you very much indeed.
> Kind regards,
> Polly Anna
> "Uri Dimant" wrote:
>> Polly
>> SS2005
>> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
>> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
>> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
>> from sys.key_constraints as k
>> join sys.tables as t
>> on t.object_id = k.parent_object_id
>> join sys.schemas as s
>> on s.schema_id = t.schema_id
>> join sys.index_columns as ic
>> on ic.object_id = t.object_id
>> and ic.index_id = k.unique_index_id
>> join sys.columns as c
>> on c.object_id = t.object_id
>> and c.column_id = ic.column_id
>> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
>> ORDINAL_POSITION;
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
>> > Hi Aaron,
>> >
>> > I am using my query against both a SQL2000 and SQL2005 db.
>> >
>> > I attach the query below.
>> >
>> > Many thanks for your help.
>> >
>> > Polly Anna
>> >
>> > select distinct column_name as Column_Name,
>> > data_type,
>> > c.length,
>> > CASE c.colStat
>> > WHEN 1 THEN 'Y' ELSE ''
>> > END AS PrimaryKey
>> >
>> > from information_schema.columns as isc
>> > left join sysobjects o
>> > on isc.table_name = o.name
>> > left join syscolumns c
>> > on o.id = c.id and c.name = isc.column_name
>> > where table_name = 'Dentist'
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> >> What version of SQL Server are you using?
>> >>
>> >>
>> >>
>> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I would like to query the system tables in order to obtain all the
>> >> > Primary
>> >> > and Foreign keys for any particular table.
>> >> >
>> >> > I have tried using the syscolumns.colstat field but it only outputs
>> >> > zero.
>> >> > I
>> >> > understand it should output 1 if the column is a primary key.
>> >> >
>> >> > Also I do not know I do not know how to obtain Foreign key info on a
>> >> > table.
>> >> >
>> >> > All help is much appreciated.
>> >> >
>> >> > Kind regards,
>> >> >
>> >> > Polly Anna
>> >>
>> >>
>> >>
>>|||Hi Aaron,
it works like a charm. Thank you so much for your help.
Kind regards,
Polly Anna
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT t.TABLE_SCHEMA,
> t.TABLE_NAME, c.CONSTRAINT_NAME,
> c.CONSTRAINT_TYPE, k.COLUMN_NAME
> FROM INFORMATION_SCHEMA.TABLES t
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
> ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
> AND t.TABLE_NAME = c.TABLE_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
> ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
> AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
> AND c.TABLE_NAME = k.TABLE_NAME
> WHERE
> c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
> ORDER BY 1,2,4 DESC,3;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> > Hi Uri,
> >
> > yes thank you, that is brilliant. It works in SS2005. You don't happen to
> > have the script for SQL2000?
> >
> > Thank you very much indeed.
> >
> > Kind regards,
> >
> > Polly Anna
> >
> > "Uri Dimant" wrote:
> >
> >> Polly
> >> SS2005
> >> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> >> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> >> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> >> from sys.key_constraints as k
> >> join sys.tables as t
> >> on t.object_id = k.parent_object_id
> >> join sys.schemas as s
> >> on s.schema_id = t.schema_id
> >> join sys.index_columns as ic
> >> on ic.object_id = t.object_id
> >> and ic.index_id = k.unique_index_id
> >> join sys.columns as c
> >> on c.object_id = t.object_id
> >> and c.column_id = ic.column_id
> >> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> >> ORDINAL_POSITION;
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> >> > Hi Aaron,
> >> >
> >> > I am using my query against both a SQL2000 and SQL2005 db.
> >> >
> >> > I attach the query below.
> >> >
> >> > Many thanks for your help.
> >> >
> >> > Polly Anna
> >> >
> >> > select distinct column_name as Column_Name,
> >> > data_type,
> >> > c.length,
> >> > CASE c.colStat
> >> > WHEN 1 THEN 'Y' ELSE ''
> >> > END AS PrimaryKey
> >> >
> >> > from information_schema.columns as isc
> >> > left join sysobjects o
> >> > on isc.table_name = o.name
> >> > left join syscolumns c
> >> > on o.id = c.id and c.name = isc.column_name
> >> > where table_name = 'Dentist'
> >> >
> >> >
> >> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >> >
> >> >> What version of SQL Server are you using?
> >> >>
> >> >>
> >> >>
> >> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I would like to query the system tables in order to obtain all the
> >> >> > Primary
> >> >> > and Foreign keys for any particular table.
> >> >> >
> >> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> >> > zero.
> >> >> > I
> >> >> > understand it should output 1 if the column is a primary key.
> >> >> >
> >> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> >> > table.
> >> >> >
> >> >> > All help is much appreciated.
> >> >> >
> >> >> > Kind regards,
> >> >> >
> >> >> > Polly Anna
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>

Obtaining data from different tables in the same database

Hi

I've been doing a website with ajax controls and I have tables that has foreign key relationships and hence the main page requires the data from the tables from the foreign keys stored in a separate table. Can anyone help me out with the proper way of querying the tables. Am not able to work out the way with the joins that SQL express provides.

Thanks

Here's a sample query:

TableA contains 3 columns: Data1, Data2, ForeignKey
TableB contains 2 columns: RecordId, SomeOtherData

The RecordId value from TableB has been stored in the ForeignKey column of TableA

SELECT TableA.Data1, TableA.Data2, TableB.SomeOtherDataFROM TableAINNERJOIN TableBON TableA.ForeignKey = TableB.RecordId

Each row of data that is retrieved will look like:

Data1,Data2, [ForeignKey] <--> [RecordId],SomeOtherData