Finding Overlap Between Five Lists of Items

[Xiphoid]

Hi there,

I'm working on a project where I have five different lists of stock tickers in tables in a mysql database. I'd like to find which stocks occur in more than one table and assign those stocks a higher numerical weighting.

My first question: is there a way to approach this using mysql queries or should I pull the lists of stocks from the database and put them into arrays to handle with php?

Second question: depending on which route you suggest above, any guidance on the best way to do this?

Right now, I'm using an array:

while(loop through the first table's entries)
--dump into stocks_array

while(loop through second table's entries)
--for(loop through entries in stocks_array)
----if(current table entry == stocks_array entry)
------increase stock_weighting
----else
------add to end of stock_array

repeat with other tables

This probably isn't the most optimal though. 🙂

Thanks for any help!

[The_Chancer]

Do you have the table layout, as well as how the stocks will be stored within it ? There should be a way of doing this as a count of some sort dependant on how the data is stored.

[Xiphoid]

Hi Chancer,

Thanks for the response. Here's how the database is set up:

database: stock_picks

table: picks_source_1 - fields: id, ticker, buys, shorts, date
table: picks_source_2 - fields: id, ticker, buys, year_return, date
table: picks_source_3 - fields: id, ticker, month_return, year_return, date
table: picks_source_4 - fields: id, ticker, date
table: picks_source_5 - fields: id, ticker, buys, date

The five lists of stocks have slightly different info stored with them; however, they all share a ticker field, which is what I want to use to check for overlap.

[HalfaBee]

You should be able to do this in one query.
Something like

SELECT * from picks_source_1,picks_source_2,picks_source_3,picks_source_4,picks_source_5
where
picks_source_1.ticker = picks_source_2.ticker OR
picks_source_1.ticker = picks_source_3.ticker OR
picks_source_1.ticker = picks_source_4.ticker OR
picks_source_1.ticker = picks_source_5.ticker OR
picks_source_2.ticker = picks_source_3.ticker OR
picks_source_2.ticker = picks_source_4.ticker OR
picks_source_2.ticker = picks_source_5.ticker OR
picks_source_3.ticker = picks_source_4.ticker OR
picks_source_3.ticker = picks_source_5.ticker OR
picks_source_4.ticker = picks_source_5.ticker

I am sure a mysql guru will have a better way.

[laserlight]

I am not a database guru, but I suspect that this will work better:

First, create a stocks table to hold the id, ticker, and date, if the date is shared.
stocks: id, ticker, date

The idea is to reduce data duplication by factoring out the shared columns. Now the picks_source tables can have those columns removed:
picks_source_1: id, buys, shorts
picks_source_2: id, buys, year_return
picks_source_3: id, month_return, year_return
picks_source_4: id
picks_source_5: id, buys

A possible query would then be:

SELECT id, SUM(id_count) AS id_count FROM (
    SELECT id, 1 AS id_count FROM picks_source_1
    UNION ALL
    SELECT id, 1 AS id_count FROM picks_source_2
    UNION ALL
    SELECT id, 1 AS id_count FROM picks_source_3
    UNION ALL
    SELECT id, 1 AS id_count FROM picks_source_4
    UNION ALL
    SELECT id, 1 AS id_count FROM picks_source_5
) picks_sources GROUP BY id ORDER BY id_count DESC;