Jump to content

MySQL JOIN


micky007

Recommended Posts

Hi,

I'm still learning MySQL using the JOIN statements in them, i cant seem to work out whats wrong with the following query:

SELECT players_stats.player_id, players_stats.program_id, players_stats.house_earnings, players_stats.stats_date, affiliate_deals.affiliate_id, affiliate_deals.revenue_share_percentage, affiliate_deals.affiliate_deal_id, affiliate_deals.type
FROM players_stats
INNER JOIN players ON players.player_id=players_stats.player_id
INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id
WHERE affiliate_deals.type = 'RevShare' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' OR affiliate_deals.type = 'Hybrid' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28'

The query runs but the result is not correct, below is the result:

player_id		program_id		house_earnings		stats_date	affiliate_id			revenue_share_percentage				affiliate_deal_id			type 	
1			1 			8.92 			2019-02-19 	1 				30 							1 					Hybrid
2 			1 			8.92 			2019-02-19 	1 				30 							1 					Hybrid
2 			1 			8.92 			2019-02-20 	1 				30 							1 					Hybrid
1 			1 			8.92 			2019-02-19 	1 				30 							3 					RevShare
2 			1 			8.92 			2019-02-19 	1 				30 							3 					RevShare
2 			1 			8.92 			2019-02-20 	1			 	30 							3 					RevShare

So why is the result doubling and showing showing the 'type' for each of the WHERE queries? It should be one or the other based off the value of 'affiliate_deals.type' which is based off the 'affiliate_deals.affiliate_deal_id' record.

Any help would be great and much appreciated.

Thank you!

Link to comment
Share on other sites

A JOIN will find all combinations of rows that match your conditions. If you look at the results, every row has slightly different data - the player_id, stats_date, or type. Each of those rows fits your criteria, so if you don't want some of them then you need to adjust the conditions accordingly.

Link to comment
Share on other sites

7 minutes ago, requinix said:

A JOIN will find all combinations of rows that match your conditions. If you look at the results, every row has slightly different data - the player_id, stats_date, or type. Each of those rows fits your criteria, so if you don't want some of them then you need to adjust the conditions accordingly.

Yes but there are only 4 rows of data in the player_stats table. Out of those 4 only 3 of them should be showing because the program_id value is 1.

What I'm trying to do is the following:

Select the data from table 'players_stats' where the column 'stats_date' is between the beginning of this month and the end of this month AND the 'program_id' value is 1
Then i need the query to match the player_id with the same value as what's in the table called 'players' and grab the values from the following columns (affiliate_id, deal_id WHERE status=1)
Then i need the query to match the deal_id with same value as whats in the table called 'affiliate_deals" and grab the values from the following columns (revenue_share_percentage) WHERE 'start_date' is less than or equal to todays date and 'end_date' is greater or equal to todays date AND 'type' is RevShare OR Hybrid.

Does that make sense? If you need to see the tables let me know and ill upload them for you.

Link to comment
Share on other sites

1 hour ago, Barand said:

I don't see any matching on deal_id, only program_id.

You appear to have program_id in the players table and the player_stats table. Which one does it really belong to?

I have program_id in both tables.

How do i go about matching from the deal_id when i already have one matching the program_id which is needed in order to get the information from affiliate_deal based on the program ID of the affiliate?

INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id

the deal ID number is only stored in the players table as well as the affiliate_deals table which holds the information about that deals

Link to comment
Share on other sites

7 minutes ago, micky007 said:

How do i go about matching from the deal_id when i already have one matching the program_id which is needed in order to get the information from affiliate_deal based on the program ID of the affiliate?


INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id

the deal ID number is only stored in the players table as well as the affiliate_deals table which holds the information about that deals

So what you're saying is that rows in the players and affiliate_deals tables are related to each other if affiliate_deals.program_id = players.program_id AND affiliate_deals.affiliate_deal_id = players.deal_id?

Link to comment
Share on other sites

7 minutes ago, requinix said:

So what you're saying is that rows in the players and affiliate_deals tables are related to each other if affiliate_deals.program_id = players.program_id AND affiliate_deals.affiliate_deal_id = players.deal_id?

Correct

Link to comment
Share on other sites

Just now, requinix said:

So... problem solved?

No as I'm getting an error saying: #1054 - Unknown column 'affiliate_deals.program_id' in 'on clause'

 

SELECT players_stats.player_id, players_stats.program_id, players_stats.house_earnings, players_stats.stats_date, affiliate_deals.affiliate_id, affiliate_deals.revenue_share_percentage, affiliate_deals.affiliate_deal_id, affiliate_deals.type
FROM players_stats
INNER JOIN players ON players.program_id=affiliate_deals.program_id
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE affiliate_deals.type = 'RevShare' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' OR affiliate_deals.type = 'Hybrid' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28'

 

Link to comment
Share on other sites

I think I've managed to do it, will do some testing but tell me you see anything wrong with it.

SELECT players_stats.player_id, players_stats.program_id, players_stats.house_earnings, players_stats.stats_date, affiliate_deals.affiliate_id, affiliate_deals.revenue_share_percentage, affiliate_deals.affiliate_deal_id, affiliate_deals.type, players.status
FROM players_stats
INNER JOIN players ON players.player_id=players_stats.player_id
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE affiliate_deals.type = 'RevShare' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' OR affiliate_deals.type = 'Hybrid' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28'

 

Link to comment
Share on other sites

It occurs to me that maybe you don't know you can combine multiple conditions into an ON clause. It's not just one single thing. It's a full expression. You can put whatever you want in there.

I had capitalized that "AND" in my reply hoping maybe you would connect the dots...

Link to comment
Share on other sites

8 minutes ago, requinix said:

It occurs to me that maybe you don't know you can combine multiple conditions into an ON clause. It's not just one single thing. It's a full expression. You can put whatever you want in there.

I had capitalized that "AND" in my reply hoping maybe you would connect the dots...

I apologise, I'm still learning MySQL so unfortunately i didn't know . Can you provide an example of how you would do it please and leave some stuff for me to fill in/do so i can learn on the go instead of just having it done for me and not learning.

Link to comment
Share on other sites

It's more obvious than you think:

INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id AND affiliate_deals.affiliate_deal_id=players.deal_id

(I know you wanted to fill in some details, and that's great, but this is literally just a matter of adding the "AND...")

Link to comment
Share on other sites

10 minutes ago, requinix said:

It's more obvious than you think:


INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id AND affiliate_deals.affiliate_deal_id=players.deal_id

(I know you wanted to fill in some details, and that's great, but this is literally just a matter of adding the "AND...")

Thanks for that, ah, i thought there might of been more to it than that. Based of what you said I'm still getting the unknown column issue.

Quote

#1054 - Unknown column 'players.status' in 'where clause'

SELECT players_stats.player_id, players_stats.program_id, players_stats.house_earnings, players_stats.stats_date, affiliate_deals.affiliate_id, affiliate_deals.revenue_share_percentage, affiliate_deals.affiliate_deal_id, affiliate_deals.type
FROM players_stats
INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id AND affiliate_deals.affiliate_deal_id=players.deal_id
WHERE affiliate_deals.type = 'RevShare' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' OR affiliate_deals.type = 'Hybrid' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28'

EDIT: If i remove that bit from there where clause i then get this error:

Quote

#1054 - Unknown column 'players.program_id' in 'on clause'

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.