Query for Joins

[sireesha]

HI,



I have 4 tables with relations. in 3 tables i have a data and in 4th table no data. i have to write a query for to display data from 3 tables if 4th table is not necessary. if 4th table has a data need to check the relations then display otherwise 3 tables data is shown as a result. 4th table fields also included in the query.

My query is:

FROM
(a INNER JOIN (b INNER JOIN( c ON (b.ID = c.ID)
AND (b.ID1 = c.ID1)) ON (a.ID1 = c.ID1)
AND (a.iD1 = b.ID1))
left JOIN d ON (c.PURCHASE_ID = d.PURCHASE_ID)
AND (b.PURCHASE_ID = d.PURCHASE_ID)

i want to exclude d table if no data is there. if data is there then check above conditions.

so i'm using left join.

but it gives error.

when in leftjoin and condition is removed then it will display result.

but i want after and condition also.

Please help me.

Regards,
Sireesha.

[rpeare]

You can't have an if/then in your query if it's an actual database object. You'd basically have to open the query modify the SQL statement, save the query then re-open the query (if you were doing it with code).

If you're willing to see blank fields where the D table would be that's a different matter and you can do it in your query.

if However you're creating a dynamic dataset in some VB script you can certainly modify the SQL statement to properly create a set of data.

[sireesha]

You can't have an if/then in your query if it's an actual database object. You'd basically have to open the query modify the SQL statement, save the query then re-open the query (if you were doing it with code).

If you're willing to see blank fields where the D table would be that's a different matter and you can do it in your query.

if However you're creating a dynamic dataset in some VB script you can certainly modify the SQL statement to properly create a set of data.

Hi,

I have given only join conditions (from clause only). if i have used left join it gives error how do rectify it. all 3 tables are inner join i.e., includes data. in the 4th table not mandatory. if the data is in that table it will take otherwise it shows blank, these table fields are includes in this query.

Please help me asap

Regards,
Sireesha.

[offie]

I think that (in design view) if you set up the tables and drag relationships between ID1 you could make the joins left (double click the join)
This is kind of similar to what I have. The one the farthest to the left has all of the project codes that I will use. Maybe you want to leave the first 2 normal, play around with it.

[rpeare]

You can do this with a 'pre' query.

Create your inner join query first (between dbo_project, dbo_vw_projecteeic and dbo_vw_projectrema...) Save that query first.

Then create a second query based on your Inner query and the table dbo_vw_projectteam.

This way you'll get only the records that have values in all three tables and any relevant data in table dbo_vw_projectteam that relates to those items.