Inner Joins while using a distinct

#1

Joins in general are still hard for me to figure out, and I don’t know why. I have the following code in my PHPMyAdmin console, and keep getting an error message:

select DISTINCT login.username, login.login_time, players.Player_ID, players.Player_name, players.Player_First_Name, Players.Player_Last_Name, players.player_email INNER JOIN players on login.username=players.Player_ID where login.login_time > ‘2019-09-09’

Basically I am trying to figure out the last people that have logged in to our system within the past year and then will max it out to a certain number like 1000, or 500 depending on what mail server we use.

Any help is greatly appreciated.

#2

Well, do you want to select all those fields and then sort them all to be distinct? Confused on why you don’t just use a GROUP BY…

So, you never created a table called “login”. When you JOIN two tables, you can not do login.something unless you have created “login” table. Here is an example of how one would look, not your version…
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders.
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

As you see, you must FROM the first table and then JOIN the second one. You never set up the first one.
Hope that helps…

#3

Ok, that part makes sense and I have fixed the code to include the from login table. The issue I am still running into is the Distinct values are not distinct values are still not producing individual values. For example, to 5 users with the username of aadams or asmith have both logged in multiple times within the last year. I can post my new code with the correction if that would help?

#4

Well, you could use DISTINCT(login.username), loging.login_time, etc, but a GROUP-BY would work better. Something loosely like this:
`

SELECT login.username, login.login_time, players.Player_ID, players.Player_name, players.Player_First_Name, Players.Player_Last_Name, players.player_email
FROM login
INNER JOIN players
ON login.username=players.Player_ID
WHERE login.login_time >= DATE_SUB(NOW(),INTERVAL 1 YEAR)
GROUP BY login.username
`
NOTE: I changed your WHERE line to be more up to date. You can use “intervals” like 1 year, 1 month, 4 week, 7 day, etc… Easier to read and you don’t have to manually add in the current date.

I do not have your data, so can not test it, but should work. Hope this helps!

#5

Using the DISTINCT keyword removes duplicate rows from the result set. Since you are selecting login.login_time, every row with a different login_time for a user, matching the WHERE clause, will remain in the result set. Do you want/need to select the last/highest login_time? If so, you would need to use MAX() around the column and an alias name for the resultant value, use GROUP BY as @ErnieAlex has stated, and forget about using DISTINCT.

Using table alias names will also reduce the amount of typing, simplify, and clean up the sql query.

#6

Yes, I was going to mention alias but forgot that…

Sponsor our Newsletter | Privacy Policy | Terms of Service