dcsimg
Results 1 to 5 of 5
  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    278

    How to query 2 tables

    I have 2 tables, see below. it doesn't show all the fields but enough for my question.

    Table Name
    Leads

    Fields
    LeadCode
    LeadTelNumber
    LeadMobileNumber


    Table Name
    LeadCalls

    Fields
    CallCode
    CallDate
    CustomerName
    Postcode
    SalesRep
    QuoteSent
    SaleCreated


    The field Callcode from the table LeadCalls is the same as the field LeadCode from the table Leads.



    The result i need is ( CallDate, CustomerName, Postcode, SalesRep, LeadTelNumber, LeadMobileNumber )

    Where QuoteSent <> '' and SaleCreated = ''


    Thanks

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    278

    Re: How to query 2 tables

    Ok, I managed it but is this the best approach ?

    "Select CallDate, CustomerName, Postcode, SalesRep, LeadTelNumber, LeadMobileNumber From LeadCalls, Leads WHERE LeadCalls.QuoteSent <> '' AND LeadCalls.SaleCreated = '' AND LeadCalls.CallCode = Leads.LeadCode"


    Thank you.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    98,681

    Re: How to query 2 tables

    Depending on your database, that may well be an older way to join tables that is not considered best practice. If your database supports explicit join statements then you should use them, e.g.
    sql Code:
    1. SELECT CallDate, CustomerName, Postcode, SalesRep, LeadTelNumber, LeadMobileNumber
    2. FROM LeadCalls INNER JOIN Leads
    3. ON LeadCalls.CallCode = Leads.LeadCode
    4. WHERE LeadCalls.QuoteSent <> '' AND LeadCalls.SaleCreated = ''
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    278

    Re: How to query 2 tables

    Using the Inner Join shows no errors and yields the same results so thank you very much for your expertise.

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,709

    Re: How to query 2 tables

    That database design look a little screwy to me.

    I'm guessing the value LeadCode is unique (possibly the primary key) to an individual lead - i.e. each lead gets it's own code. Is that right?

    Similarly, I'm guessing CallCode is unique to each call - so each individual call gets it's own code?

    If I've guessed the above correctly then joining on CallCode = LeadCode would mean you could only call each Lead once, which seems strange.

    Apologies if I've miss-understood anything but I thought I'd wave a flag in case it gives you a chance to catch a larger problem early.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.