MySQL JOIN

[micky007]

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!

[requinix]

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.

[micky007]

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.

[Barand]

28 minutes ago, micky007 said:

Then i need the query to match the deal_id

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?

[micky007]

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

Edited February 25, 2019 by micky007

[requinix]

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?

[micky007]

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

[requinix]

7 minutes ago, micky007 said:

Correct

So... problem solved?

[micky007]

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'

 

[micky007]

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'

 

[requinix]

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

[micky007]

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.

[requinix]

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

[micky007]

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'

 

Edited February 25, 2019 by micky007

[requinix]

Where's your JOIN for the players table?

[micky007]

3 minutes ago, requinix said:

Where's your JOIN for the players table?

I'm such an idiot!

Sorry for wasting your time just then and thanks again for helping me!