Ok, I have an SQL statement to get all files from a table thats is linked to another table when a certain condition is true. Now I also need to get all the other data from the table that it is linked to if a different condition is true. I will provide somewhat generic code so that I dont confuse anyone (hopefully)

SELECT table1.field1, table1.field2, table1.field3, table2.field1, table2.field2, table2.field3
FROM table1, table2
WHERE table2.field0 = table1.field0 AND table2.field3 = 1

Now this code provides me with everything in table2 where table2.field3 is 1, but I also need to get all the other data from table1 that isnt in table2 where table1.field3 = YES

Any help would be much appreciated

You could use UNION operator to merge results of two queries. First one would be the one you wrote, and the other one the one that'll meet new conditions you mentioned. Something like

SELECT fields FROM tab1, tab2
WHERE condition1
UNION
SELECT fields FROM tab1, tab2
WHERE condition2;

Would that be OK, or would you rather have all the conditions in one WHERE clause?

I have no preference as to how it is achieved as long as it is just one output recordset because I am calling it from a program.

as to using UNION, I am unfamiliar with that option as I am still new to SQL language so I will read up on it and see if that is what I am looking for and will post again in this same thread if I have any further questions.

many thanks for your quick resonse

Ok, reading what the UNION does makes it sound like this is exactly the kind of method I want to use, however the UNION method needs to have the same number of fields and same type of data within each field and this is not the case in this situation

Table1
SkillID, SkillName, Untrained, KeyAbility, ArmorCheck

Table2
Skill, CharacterID, Ranks

Forgive me if the names of my fields lead to any confusion, but I figured if I show you exactly what I want to do then maybe we can figure out how to do it. So In table2 Skill is linked to SkillID in table1 then my current SELECT statement gets me all the Skills in table2 where CharacterID is equal to the number I give it. Now I also need all the other skills from table1 which arent duplicated in table2 and which the Untrained is equal to YES. So like I said, the UNION sounds like the right sort of thing as far as not getting duplicates, but I dont know if I can get it to work cos of the differences in fields

Again any help would be much appreciated

Ok, so for now I have managed to come up with my own solution, but am still looking to find out if there is a better way. I used the UNION to select each set, basically setting up the second requirement's SELECT statement to have the same fields as the first SELECT statment. I then created a 2nd recordset just to grab the extra field from the second table and then used a Do Until loop to fill in the extra field where it applied. I would much rather use just one recordset to do this, but for now this method achieves the results I want, so I can deal with it for now

You could also use the "Join" predicate or "where <table1>.<columnx> = <table2>.<columny> althought the Join is the more 'preferred' route on modern implementations