...

View Full Version : Join on NOT NULL ?



bitbob
03-05-2007, 03:52 PM
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...

guelphdad
03-05-2007, 03:58 PM
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.

bitbob
03-05-2007, 04:06 PM
Man, I got confused reading your post ;)

this is my complete query:

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.

bitbob
03-06-2007, 10:18 AM
rewritten as:



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...

guelphdad
03-06-2007, 10:57 PM
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.

bitbob
03-07-2007, 07:54 AM
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:

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):


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 !

guelphdad
03-07-2007, 03:28 PM
Sorry, missed the stocks table. I would write it like this:


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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum