Thread: SQL help

    #1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    266
    Rep Power
    16

    SQL help


    I have the following 2 sql strings:

    strsql = "SELECT * FROM [tbl_officer_info] WHERE [active_indr]=-1"
    strsql = "SELECT * FROM [tbl_other_received] WHERE [active_indr]=-1"

    I wish to have them combined so that they take in from bot tables at once and then order by first_name & last_name...

    ~
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    SELECT * FROM [tbl_officer_info] WHERE [active_indr]=-1
    UNION ALL
    SELECT * FROM [tbl_other_received] WHERE [active_indr]=-1
    ORDER BY [first name], [last name]

    rudy
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    266
    Rep Power
    16
    that doesn't work as the 2 tables have different structures, here is the answer:

    strsql =
    "SELECT tbl_officer_info.id, tbl_officer_info.first_name, tbl_officer_info.last_name
    FROM tbl_officer_info
    UNION ALL
    SELECT tbl_other_received.id, tbl_other_received.first_name, tbl_other_received.last_name
    FROM tbl_other_received
    WHERE active_indr=-1
    ORDER BY last_name, first_name;"

  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    yeah, UNION [ALL] requires the same structure in both SELECT lists

    now you know

IMN logo majestic logo threadwatch logo seochat tools logo