Hello
I have a master/detail data.
Now I want to get all the detail data based on the result from the master query
Something like:
"SELECT * FROM DETAIL WHERE MainId IN " MainId in the master query result
What sql command will return this result?

Thanks Sorin

Hershcu

>I have a master/detail data.
>Now I want to get all the detail data based on the result from the master query
>Something like:
>"SELECT * FROM DETAIL WHERE MainId IN " MainId in the master query result
>What sql command will return this result?

SELECT * FROM detail WHERE foreignkey IN (SELECT masterid FROM master)

Roy Lambert

Roy Lambert wrote:

SELECT * FROM detail WHERE foreignkey IN (SELECT masterid FROM master)

Thanks Roy

I try this but it's raise error #700
Sorin

I found the problem
I try
SELECT * FROM detail WHERE foreignkey IN (SELECT * FROM master)

Sorin

Hershcu


>SELECT * FROM detail WHERE foreignkey IN (SELECT * FROM master)

No that is totally wrong! You are asking for any detail record where the foreignkey matches any piece of data held in the master table. Its pretty much guaranteed to give you wrong data.

700 is a syntax error so I'd guess you typed it in incorrectly. Just in case its terminology

Just checking with a couple of my tables I'm surprised that the statement you quote worked - I get

"ElevateDB Error #700 An error was found in the statement at line 1 and column 28 (Expected row value but instead found "_fkContacts")"


foreignkey is the column in the detail table that hold the information that point to the primary key (or id) in the master table

masterid is the primary key of the master table.

I'm assuming single column keys. If you're using multi column keys it gets a bit more difficult.

If you can't get it to work please post the table definitions here.

Roy Lambert