Need help with SQL statement ...

[TheBionicOrange]

OK here goes ....


I need to look in 2 files, say FileA and FileB for an item, say a lump of cheese.

If I "drive" my SQL from FileA and "lump of cheese" does not exist, is there a way of making the SQL look in FileB ?

At the moment if it can't find "lump of cheese" in File A it DOESN'T look in FileB (which as it happens is exactly where "lump of cheese" lives !)

I've tried a left outer join, a right outer join, and nested select statements, but it seems to me that if the item your looking for doesn't exist in the file you are driving your statement from, then it stops right there and WON'T look in any other files regardless.

Is that a correct assumption to make ?

Am I making any sense ?

Cheese haters need not apply !

[Wokawidget]

Can you post an example of a FileA???

I love cheese.

[TheBionicOrange]

Not really ... its too flippin big.

Basically FileA and FileB are identical, only FileA holds "current" cheese and FileB holds "out of date cheese".

The structure is the same for both, with field names prefixed with "FIN" for FileB.

IS that any help at all ?

[TalonSoftware]

Stored Procedure or no?

If so, then I'd use an If EXISTS on it:
IF EXISTS (SELECT * FROM FileA WHERE Field = 'Lump OF Cheese')
SELECT ... FROM FileA
ELSE
SELECT ... FROM FileB

[TheBionicOrange]

No its NOT a stored procedure

That would have been helpful though if it was

but not

no cheese for me it seems

[Wokawidget]

Why can't you look in FileA...if no records then look in FileB.
Why can't this be done using ADO and VB code?

Woof

[TheBionicOrange]

I could look in FileA and then FileB, but I was trying to cut down the number of SQL statements I would need to execute.

We are talking about potentially 70,000+ lumps of cheese here !

[Wokawidget]

I see...
hmmm...is it possible to be in both files???

Woka

[TheBionicOrange]

No .. its either finished or its not.

Its OK I got it now (though best not to say where from )

I needed the UNION command. That doesn't tell me WHICH file it came from, but thats not important, so long as I get a valid return thats fine.

Cheers for your input though Woka

[TheBionicOrange]

Originally posted by TheBionicOrange
OK here goes ....


I need to look in 2 files, say FileA and FileB for an item, say a lump of cheese.

If I "drive" my SQL from FileA and "lump of cheese" does not exist, is there a way of making the SQL look in FileB ?

At the moment if it can't find "lump of cheese" in File A it DOESN'T look in FileB (which as it happens is exactly where "lump of cheese" lives !)

I've tried a left outer join, a right outer join, and nested select statements, but it seems to me that if the item your looking for doesn't exist in the file you are driving your statement from, then it stops right there and WON'T look in any other files regardless.

Is that a correct assumption to make ?

Am I making any sense ?

Cheese haters need not apply !

[Frans C]

If you want to put the check icon on the thread you need to EDIT your first post, and add the check icon. So don't quote but edit the first post.

[TheBionicOrange]

Ha Ha !

Oops ... and I was looking for my ost with a tick mark against it too.

Sorry Dad

[leinad31]

Place an additional field with hardcoded values to tell you from which table the records came from

SELECT "FROM A" AS SrcTable, field1, field2 FROM tableA WHERE blah blah
UNION
SELECT "FROM B", field1, field2 FROM tableB WHERE blah blah AND (SELECT COUNT(*) FROM tableA WHERE blah blah) = 0


AND (SELECT COUNT(*) FROM tableA WHERE blah blah) = 0 is so you'll have TbaleB records if there are no records in tableA since records will only be returned if clause blah blah and AND (SELECT COUNT(*) FROM tableA WHERE blah blah) = 0 both return True

Edit: Oh and you need to do FROM tableA IN databasepathinfo to b able to look in two files, I dont have a link to asite with the syntax here at home.

[Wokawidget]

Originally posted by TheBionicOrange
No .. its either finished or its not.

Its OK I got it now (though best not to say where from )

I needed the UNION command. That doesn't tell me WHICH file it came from, but thats not important, so long as I get a valid return thats fine.

Cheers for your input though Woka

That is exact WHAT i WAS GOING TO SUGGEST...damn caps.
Should have said in my last post. Anyhooos, u have it now.

woof