Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Join on NOT NULL ?

    Code:
    SELECT company.isin
    FROM company
    LEFT JOIN stocks ON company.isin = stocks.isin 
    WHERE stocks.isin IS NOT NULL
    This gets the company.isin field once for every time it exists in the stocks table. I only want it once for each company. Using DISTINCT(company.isin) works, but is this the correkt way to go?

    The query should work with MySQL 4.1...

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    You don't need a LEFT join if you are only looking for matching companies. If the stock has to be listed, which you seem to need then you need the LEFT JOIN but you need to change the WHERE to an AND. Leaving it as you have turns your query into an INNER JOIN.

    Yes you can use DISTINCT in this case but write it as
    DISTINCT company.isin
    to remind you that DISTINCT is NOT a function, it works on all columns selected in the row.

    So if you have more than the company.isin selected then you would end up with

    DISTINCT
    company.isin,
    datecolumn

    for instance and that DISTINCT would return company.isin for each separate date.

  • #3
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Man, I got confused reading your post

    this is my complete query:
    Code:
    SELECT company.isin, longName, shortName, tradeMarket
    FROM tradeMarkets, company
    LEFT JOIN stocks ON company.isin = stocks.isin 
    WHERE stocks.isin IS NOT NULL
    AND company.tradeMarketID = tradeMarkets.tradeMarketID
    In company isin is the primary key.
    In stocks isin is primary key along with a date field.
    (tradeMarkets is just a simple id => name table, with the id for the tradeMarket in the company field)

    What I want to do is to list all companies that has at least one entry in the stocks table.

  • #4
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts
    rewritten as:

    Code:
    SELECT DISTINCT(company.isin), longName, shortName, tradeMarket
    FROM company, stocks, tradeMarkets
    WHERE company.isin = stocks.isin 
    	AND company.tradeMarketID = tradeMarkets.tradeMarketID
    ORDER BY longName
    but I still feel that there is something I'm missing since I need to use DISTINCT...

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Code:
    SELECT company.isin, longName, shortName, tradeMarket
    FROM tradeMarkets
    INNER JOIN company
    ON company.isin = stocks.isin
    is all you need. If you don't have a listing in your stocks table then the company won't show up.

  • #6
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As written, your query doesn't work. MySQL can't find the stocks table. If I add stocks to the FROM-clause, I get the same result as with my own queries (without the DISTINCT keyword)... I still get all the info from company as many times as `isin` appears in the stocks table.

    I often get confused when it comes to JOIN-syntax. I use LEFT JOIN when I want to get all results from a table where some key in that table does not exist in another table. But that is as far as it goes.

    Where can I get a good introduction to JOIN syntax? And what is the difference between my query:
    Code:
    SELECT DISTINCT(company.isin), longName, shortName, tradeMarket
    FROM company, stocks, tradeMarkets
    WHERE company.isin = stocks.isin 
    	AND company.tradeMarketID = tradeMarkets.tradeMarketID
    ORDER BY longName
    and yours (with the stocks table added):

    Code:
    SELECT company.isin, longName, shortName, tradeMarket
    FROM tradeMarkets, stocks
    INNER JOIN company
    ON company.isin = stocks.isin
    Any speed benefits?

    Thanks for all your help guelphdad !

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Sorry, missed the stocks table. I would write it like this:
    Code:
    SELECT company.isin, longName, shortName, tradeMarket
    FROM company
    INNER JOIN
    tradeMarkets
    ON company.tradeMarketID = tradeMarkets.tradeMarketID
    INNER JOIN stocks 
    ON company.isin = stocks.isin 
    ORDER BY longName
    The benefit is that you should always know your joins are INNER by specifying them and always use an ON clause.

    DISTINCT is not a function and can't be used the way you are doing so.

    DISTINCT will not work on the one column when you have other columns selected, the DISTINCT will work across all four columns.

    Lets say you have Royal Bank and it is listed on the TSX and NYSX

    then you will end up with two rows because the trademarkets are different. Is this what you want?


  •  

    Posting Permissions

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