Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    23

    Smile Unanswered: Join one to many relationship query

    I have 2 tables 'contacts' and 'history' I want to search 3 fields the body fields in the contacts table... and also the subject and body field in the history table all looking for like '%Winn%' and where contacts.address is not null.. I am having a heck of a time trying to do this... can anyone help please I want to return address and contactID.

    Ghornet
    "Everything is possible, somethings are just less likely then others"

  2. #2
    Join Date
    Sep 2003
    Posts
    10

    Re: Join one to many relationship query

    I think that this should work... I am a little new at this though...

    Select contact.contactId, contact.address from contacts
    inner join contacts
    where (history.body = contacts.body and contacts.address is NOT NULL) and history.body like '%Winn%'


    Good luck.

  3. #3
    Join Date
    Sep 2003
    Posts
    10
    Sorry I meant:

    inner join history

  4. #4
    Join Date
    Jul 2003
    Posts
    23

    hmm not quite I think

    Won't that join where the bodys are the same.. I don't want that...
    Thanks

    I have 2 tables 'contacts' and 'history'
    I want to search 3 fields the body fields in the contacts table with like '%Winn%'
    and also the subject and body field in the history table also looking for like '%Winn%'
    I want to make sure that contacts.address and contacts.city is not null..
    I want to return firstname, lastname, spouse, salutation, address, city, state, zip and contactID.

    Ghornet
    "Everything is possible, somethings are just less likely then others"

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    something like this maybe:

    select c.contactid, c.address from contacts c
    where exists (select 1 from ((select contactid from history where subject like '%Winn%') union (select contactid from history where body like '%Winn%')) x where c.contactid = x.contactid)

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    oh, and of course:

    ...and c.address is not null

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    hmmm, I think I missed the body field in the contacts table. Anybody?

  8. #8
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    How do they join? By contactID? If so, also, you mention the columns (there are no fields in relational theory, but we all know what you mean) you want to return but not from which table so I assume from the contacts table.

    select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
    from contacts a
    join history b on a.contactid = b.contactid
    where b.subject not '%Winn%' and
    a.body like '%Winn%' and
    b.body like '%Winn%' and
    a.address is not null
    and a.city is not null

    Did I miss anything?
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  9. #9
    Join Date
    Jul 2003
    Posts
    23
    i think that is what I want thanks a bunch for the help

    select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
    from contacts a
    join history b on a.contactid = b.contactid
    where b.subject like '%Winn%' or
    a.body like '%Winn%' or
    b.body like '%Winn%' and
    a.address is not null
    and a.city is not null
    "Everything is possible, somethings are just less likely then others"

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think it is not.

    Better put some parenthesis around that complicated WHERE clause just to be sure:

    select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city,
    a.state, a.zip, a.contactID
    from contacts a
    inner join history b on a.contactid = b.contactid
    where (b.subject like '%Winn%' or a.body like '%Winn%' or b.body like '%Winn%')
    and a.address is not null
    and a.city is not null

    blindman

Posting Permissions

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