Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    4

    Unanswered: Getting data from 2 tables

    Table A
    Table B

    Both table A and table B have a field: fileId

    I want to select the fileId’s that are in table B, but not if they are also in the following SELECT result set from table A

    SELECT fileId FROM a WHERE filetype=‘jpg’;

    I hope you understand… I have only just grasped the concepts of joins but I’m at a loss as to how to do this?… Any help would be much appreciated…

    Alternatively is there a way to create a new field on the fly so that when I am looping through the second sql result set there is field that says fileId is in both table B and also in the tableA sql result set….

    kind regards J

  2. #2
    Join Date
    Dec 2010
    Posts
    4
    Please provide table structure and example data so we have something to work with. I see what you want to do, and in that case JOIN won't do you any good.

  3. #3
    Join Date
    Dec 2010
    Posts
    4
    Thnaks Darek: this is as simple as i can make it... (plus it makes it easier for me to understand!)...

    Table A:

    fileId (primary index) - bignum
    fileType - var
    fileName - var


    Table B:

    fileId - bignum
    projectId - bignum

    fileId+projectId (unique index)

    I want to select all the fileIds in A that are not in the following sql result set:
    SELECT fileId FROM B WHERE projectId=‘1’;

  4. #4
    Join Date
    Dec 2010
    Posts
    4

    cretind tumblers

    Thanks to Subdee from another forum for that...

    SELECT b.fileId FROM tableB b
    WHERE b.fileId NOT IN
    (SELECT a.fileId FROM tableA a WHERE filetype='jpg')
    AND filetype='jpg'

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jonniejoejonson View Post
    Alternatively is there a way to create a new field on the fly so that when I am looping through the second sql result set there is field that says fileId is in both table B and also in the tableA sql result set….
    Code:
    SELECT b.fileId 
         , CASE WHEN a.fielId = b.fileId
                THEN 'yes' ELSE 'no' END   AS in_both
      FROM tableB b 
    LEFT OUTER
      JOIN tableA a 
        ON a.fileId = b.fileId 
       AND a.filetype = 'jpg'
     WHERE b.filetype = 'jpg'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2010
    Posts
    4
    Thanks r937, i will take a look at that... it looks genius!...
    kind regards Jonathan

Posting Permissions

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