Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Question Unanswered: Join on multiple columns

    I have 2 tables, Licenses and Prices as below

    Licenses
    User Tariff Licensetype
    User1 Tariff1 License1
    User2 Tariff2 License1
    User3 Tariff1 License2
    User4 Tariff2 License2
    etc

    Prices
    Tariff LicenseType Cost
    Tariff1 License1 1
    Tariff1 License2 2
    Tariff2 License1 1.50
    Tariff2 License2 2.50

    I need to create a query that will produce a table as follows:

    User LicenseType Cost


    I have tried the following:

    Select Licenses.User, Licenses.LicenseType, Prices.Cost
    from Licenses
    inner join Prices
    on Licenses.Tariff = Prices.Tariff
    and Licenses.LicenseType = Prices.LicenseType


    Select Licenses.User, Licenses.LicenseType, Prices.Cost
    from Licenses
    Left join Prices
    on Licenses.Tariff = Prices.Tariff
    and Licenses.LicenseType = Prices.LicenseType


    Select Licenses.User, Licenses.LicenseType, Prices.Cost
    from Licenses
    Right join Prices
    on Licenses.Tariff = Prices.Tariff
    and Licenses.LicenseType = Prices.LicenseType

    But all three output multiple lines for each user.

    Can anyone provide a solution?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Che3zy View Post
    But all three output multiple lines for each user.
    not with the sample data you provided, no

    by the way, you do want INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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