Unanswered: Need help with an SQL statement to get data from 2 tables
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
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.
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
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
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