Having an issue with a Join query...

[Jim R]

I'm trying to get rows from one table that don't have a matching row in the other, based on first and last name.  

select * from fallLeague2016_boys p
inner join fallLeague2017_boys c
on concat (p.nameFirst,p.nameLast) != concat (c.nameFirst,c.nameLast)

It gives me 47,107 rows.  Table P has 354 rows.  Table C has 132.  It repeats a series of rows 34 times.  Not sure why.  I also tried Join, as well as Left and Right Joins, just to see.  Same result, just different order.  

 

When I switch it from != to =, it gives the rows that match, look like 61.  So my output should be 293.  

Edited September 10, 2017 by Jim R

[Barand]

To find non-matching records use a LEFT JOIN. Columns from table c will be null if there is no match, so add the condition

 

WHERE c.nameFirst IS NULL

[Jim R]

Add it where?  I added it to the end, and it didn't provide any results.  

[Jim R]

You must've also meant to change the != to = because that yielded me 299 rows, which would be about right.  

[Psycho]

A few things:

 

1. Do not use "SELECT *". List out the fields that you want returned. It is more efficient and it can lead to security problems.

 

2. If you are JOINing tables using concatenated data, it is a sure sign there is a problem with the schema. I guess it could make sense in this scenario based on the table names assuming users do not need to log in to register their kid.

 

3. Rather than using CANCAT, it would be more efficient to JOIN using both values. And, yes, I suspect you DO want to use an equal condition. The not equal condition you had would make no sense that I could see. But, since I don't know the purpose, I can't say for sure. But, using "names" is a terrible way to correlate records. I have had employees with the same name!

 

4. Instead of separate tables you should have ONE table with a column for the season.

 

Anyway, with your current structure, assuming you want a list of all players from the 2016 season that do not exist for the 2017 season, this should work

 

SELECT *
FROM fallLeague2016_boys p
LEFT JOIN fallLeague2017_boys c
  ON p.nameFirst == c.nameFirst
  AND p.nameLast == c.nameLast
WHERE c.nameFirst IS NULL

[Jim R]

1)  That was just a quick way to type it.  I reduce it to what I need after I figure it out.  

 

2)  Correct on not having to login to sign up their kid. 

 

3)  The = vs. != was me not having my head wrapped around how Left and Right Joins select the rows. 

 

4)  There was an instance where I came here looking for a way to avoid duplicates.  I'm not sure why we couldn't figure it out.  I know I couldn't, and I remember a couple of people here trying to help.  Plus, some of their information changes from year to year, such as height, school or email address.  It was easier back then to just have a new database from  year to year than to, at that time, learn more about conditional updates.  I could probably get it done now, but I never think about it until it's time to throw the registration form back up.

 

 

I like not having to concat.  Your query works great, just with one = instead of ==.  It gave me errors.

 

 

Thank you.  I love the help I get here.

Edited September 14, 2017 by Jim R

[Psycho]

4)  There was an instance where I came here looking for a way to avoid duplicates.  I'm not sure why we couldn't figure it out.  I know I couldn't, and I remember a couple of people here trying to help.  Plus, some of their information changes from year to year, such as height, school or email address.  It was easier back then to just have a new database from  year to year than to, at that time, learn more about conditional updates.  I could probably get it done now, but I never think about it until it's time to throw the registration form back up.

 

A few follow up comments/suggestions:

 

First, there is no reason to have a separate database/tables for year, gender, etc. I would highly suggest modifying a table to include additional columns for year, gender and whatever other "data" items you are creating separate tables for. You could define a constant in your configuration for the current "year" which will be used for any DB queries (Inserts, Selects, etc.). Then just change that defined value each year and there is no need to create a new database/table. But, with this approach you probably want one table for each child and then another table to have separate records for each registration from year to year for each child.

 

You can avoid duplicates by creating a uniqueness constraint on a combination of the nameFirst and nameLast fields.

 

ALTER TABLE `fallLeague2016_boys` ADD UNIQUE( `nameFirst`, `nameLast`);

With that in place you can have duplicates on the first name or the last name, but you cannot have two records that have the same values for BOTH fields. You would, of course, need to remove any duplicates before applying the above constraint. Plus, you would want to add logic to check for duplicates at the time of insert to provide the user a message.

 

However, using something like a name for determining which records may be the same is problematic. Although the likelihood of two different kids with the same name is remote in a soccer league, it is a possibility. What is a bigger possibility is that the same kid could be registered under different names. E.g. Christopher one year and Chris the next. I think the best way to handle (but would require more logic) would be to allow parents to create an account so they can re-register/update their kids one year to the next. Then if someone tries to register a new kid with a same/similar name you could give them a notice that there is a kid with the same name and give them the ability to re-register the existing kid through some means: resetting the password for that account to the email address on record, or verifying some details of the child, etc.

 

Yeah, that's starting to sound like a lot of work for something that you are probably doing for free. So, take what you will to get the results you need.

[Jim R]

We actually have kids who have the same name from time to time.

 

I really don't want to have them create an account.  I want to make it as user friendly as possible, and most of my customers use their phone.  I know they can save passwords, but I'd hate for there to be an obstacle.  

 

I kept the boys and girls separate because a different person manages that side of it, and I didn't want him screwing around.  He just has viewing privileges but almost wiped the whole thing out a few years ago.  He did, but I had backups.

 

 

I always get good help here.  I'm self taught with a lot of help from here, and while I make money off my sites, I do my own coding so it's part of my "job".