Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41

    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

    Any help would be much appreciated

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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?

  3. #3
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    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

  4. #4
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    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

  5. #5
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    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

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You could also use the "Join" predicate or "where <table1>.<columnx> = <table2>.<columny> althought the Join is the more 'preferred' route on modern implementations

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •