Going round in circles!!!

[RzB]

I have been going round and round in circles...

I have Table tabA - fields Aid and Atxt

I have Table tabB - fields Bid and Btxt

I have Table tabC - fields Cid , Ctxt, Aid, Bid

I have Table tabD - fields Did , Dtxt, Aid, Bid

I want the SQL that will give me the Btxt's for all the
Bid's used in Table C or Table D where Aid is a specific value...

Please help before I go completely loopy...

Thanks,
Roy

 

[Marshall Barton]

I have been going round and round in circles...

I have Table tabA - fields Aid and Atxt

I have Table tabB - fields Bid and Btxt

I have Table tabC - fields Cid , Ctxt, Aid, Bid

I have Table tabD - fields Did , Dtxt, Aid, Bid

I want the SQL that will give me the Btxt's for all the
Bid's used in Table C or Table D where Aid is a specific value...

What purpose does Table D serve? Not that it makes much
difference, but table C could contain the Dtxt field without
any loss of information/integrity.

I think, maybe you need this kind of air query:

SELECT tabC.Bid, tabB.Btxt
FROM tabC INNER JOIN tabB ON tabC.Bid = tabB.Bid
INNER JOIN tabA ON tabC.Aid = tabA.Aid
WHERE tabA.Aid = somevalue
UNION ALL
SELECT tabD.Bid, tabB.Btxt
FROM tabD INNER JOIN tabB ON tabD.Bid = tabB.Bid
INNER JOIN tabA ON tabD.Aid = tabA.Aid
WHERE tabA.Aid = somevalue

 

[RzB]

What purpose does Table D serve? Not that it makes much
difference, but table C could contain the Dtxt field without
any loss of information/integrity.

I think, maybe you need this kind of air query:

SELECT tabC.Bid, tabB.Btxt
FROM tabC INNER JOIN tabB ON tabC.Bid = tabB.Bid
INNER JOIN tabA ON tabC.Aid = tabA.Aid
WHERE tabA.Aid = somevalue
UNION ALL
SELECT tabD.Bid, tabB.Btxt
FROM tabD INNER JOIN tabB ON tabD.Bid = tabB.Bid
INNER JOIN tabA ON tabD.Aid = tabA.Aid
WHERE tabA.Aid = somevalue

Marshall,
Many thanks for your assistance...
Yes I understand your concern about Table C & D....
However - I have tried to reduce things to a minimum
so that's why it might look a bit strange. There are other
fields in C & D that make it sensible to have different tables
from a normalisation point of view...

I agree with your soluton but it is still not working for me !
Perhaps I'm just missing something real simple. I have been
fighting with this for so long that I'm probably just tooo close to it!

I'm getting a "syntax Error..." so let me come clean...

Table A = tblExpenseDescriptions - fields - ID and ExpenseDescription...
Table B = tblProjects - fields - ID and ProjectName...
Table C = tblPrjLinesCapital - fields - ID, SomeText, ExpenseDescriptionID,
ProjectID ...
Tabe D = tblPrjLinesNonCapital - fields - ID, SomeText,
ExpenseDescriptionID, ProjectID...

so when I try to run .....

SELECT tblPrjLinesCapital.ProjectID, tblProjects.ProjectName
FROM tblPrjLinesCapital INNER JOIN tblProjects
ON tblPrjLinesCapital.ProjectID = tblProjects.id INNER JOIN
tblExpenseDescriptions
ON tblPrjLinesCapital.ExpenseDescriptionID = tblExpenseDescriptions.ID
WHERE tblExpenseDescriptions.ID = somenumber
UNION ALL
SELECT tblPrjLinesNonCapital.ProjectID, tblProjects.ProjectName
FROM tblPrjLinesNonCapital INNER JOIN tblProjects
ON tblPrjLinesNonCapital.ProjectID = tblProjects.id INNER JOIN
tblExpenseDescriptions
ON tblPrjLinesNonCapital.ExpenseDescriptionID = tblExpenseDescriptions.ID
WHERE tblExpenseDescriptions.ID = somenumber

I get a Syntax error (missing operator) in expression ....
"tblPrjLinesCapital.ProjectID = tblProjects.id INNER JOIN
tblExpenseDescriptions
ON tblPrjLinesCapital.ExpenseDescriptionID = tblExpenseDescriptions.ID"

I have been painsaikingly through the syntax and I just don't see whats
wrong...
as I said - probably too close to it...

Many thanks,
Roy

 

[Marshall Barton]

Yes I understand your concern about Table C & D....
However - I have tried to reduce things to a minimum
so that's why it might look a bit strange. There are other
fields in C & D that make it sensible to have different tables
from a normalisation point of view...

I agree with your soluton but it is still not working for me !
Perhaps I'm just missing something real simple. I have been
fighting with this for so long that I'm probably just tooo close to it!

I'm getting a "syntax Error..." so let me come clean...

Table A = tblExpenseDescriptions - fields - ID and ExpenseDescription...
Table B = tblProjects - fields - ID and ProjectName...
Table C = tblPrjLinesCapital - fields - ID, SomeText, ExpenseDescriptionID,
ProjectID ...
Tabe D = tblPrjLinesNonCapital - fields - ID, SomeText,
ExpenseDescriptionID, ProjectID...

so when I try to run .....

SELECT tblPrjLinesCapital.ProjectID, tblProjects.ProjectName
FROM tblPrjLinesCapital INNER JOIN tblProjects
ON tblPrjLinesCapital.ProjectID = tblProjects.id INNER JOIN
tblExpenseDescriptions
ON tblPrjLinesCapital.ExpenseDescriptionID = tblExpenseDescriptions.ID
WHERE tblExpenseDescriptions.ID = somenumber
UNION ALL
SELECT tblPrjLinesNonCapital.ProjectID, tblProjects.ProjectName
FROM tblPrjLinesNonCapital INNER JOIN tblProjects
ON tblPrjLinesNonCapital.ProjectID = tblProjects.id INNER JOIN
tblExpenseDescriptions
ON tblPrjLinesNonCapital.ExpenseDescriptionID = tblExpenseDescriptions.ID
WHERE tblExpenseDescriptions.ID = somenumber

I get a Syntax error (missing operator) in expression ....
"tblPrjLinesCapital.ProjectID = tblProjects.id INNER JOIN
tblExpenseDescriptions
ON tblPrjLinesCapital.ExpenseDescriptionID = tblExpenseDescriptions.ID"

Arrggghhhh, I left out the parenthesis:

FROM ( tblPrjLinesNonCapital
INNER JOIN tblProjects
ON tblPrjLinesNonCapital.ProjectID = tblProjects.id )
INNER JOIN tblExpenseDescriptions
ON tblPrjLinesNonCapital.ExpenseDescriptionID =
tblExpenseDescriptions.ID

But, I think your field lists are short some fields or have
a wrong field because both SELECT's have the same list.

OTOH, I'm not really sure that you want the data in Union
kind of records,

 

[RzB]

Marshall,
Many thanks, That fixed it...

I have my head stuck in an SQL book at the
moment trying to get my head round
/LEFT/RIGHT/INNER/OUTER/SHAKE IT ALL ABOUT etc...
Why, oh why do I have so much trouble with this!
I'm sure some day something will click and I'll realise
where I have been going wrong all this time...

Until then it's a struggle!

Many thanks for your help,
Roy