SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jun 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Here's one for you (select from one table on cond from another)

    Working on selecting everything from (a condition in) table1 where the related row in table2 meets a certain condition.

    table1:
    Code:
    +----+-----------+------------+------+
    | id | table2_id | condition  | data |
    +----+-----------+------------+------+
    | 1  | 1         | false      | hi   |
    | 2  | 2         | true       | foo  |
    | 3  | 2         | false      | hows |
    | 4  | 3         | true       | bar  |
    | 5  | 3         | false      | good |
    +----+-----------+------------+------+
    table2
    Code:
    +----+------------+------+
    | id | condition  | data |
    +----+------------+------+
    | 1  | false      | abc  |
    | 2  | true       | bca  |
    | 3  | true       | cab  |
    +----+------------+------+
    So I'd like to select * from table1 where condition equals false, if and only if the condition in table2 (where table1.table2_id = table2.id) is true.

    desired resultset
    Code:
    +----+-----------+------------+------+
    | id | table2_id | condition  | data |
    +----+-----------+------------+------+
    | 3  | 2         | false      | hows |
    | 5  | 3         | false      | good |
    +----+-----------+------------+------+
    This doesn't seem to work:
    Code:
    SELECT table1.id, table1.table2_id, table1.condition, table1.data FROM table1, table2 WHERE table1.table2_id = table2.id AND table1.condition = 'false' AND table2.condition = 'true'
    I think it would be a more involved query than that. An example scenario is a database of computer programs (table2) and screenshots (table1) for approval. The screenshots are linked to the programs. When program condition is true, they have already been approved, as with screenshots. The query would retrieve a listing of unapproved screenshots for any computer program that is already approved. Does this make any sense?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, it makes sense, and your query looks fine.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    your query looks ok to me, but i rewrote it in to a more readable layout:
    Code:
    select t1.id
         , t1.table2_id
         , t1.condition
         , t1.data
      from table1 t1
      join table2 t2
        on t2.id = t1.table2_id
       and t2.condition = true
     where t1.condition = false
    so, what is the actual output of your query?

  4. #4
    SitePoint Member
    Join Date
    Jun 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hah, well.... i guess it does work, sheesh.... sorry about that. maybe somehow or anther the keywords on this page will help a websurfer somewhere looking for help :-)

    longneck, i like that style much better, i need to make a habit of writing things like that more...


Bookmarks

Posting Permissions

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