Kyle.Belanger Kyle.Belanger - 2 months ago 8
MySQL Question

Simplifying an inner join query

I have a MySQL inner join query that I'm trying to simplify using the

as
keyword.

Original query:

SELECT u.user
, g.group
, b.type
, b.name
, b.count
, b.date
FROM profile.user u
JOIN profile.group g
ON g.GroupID = u.GroupID
JOIN main.set b
ON b.ID = u.GroupID
GROUP
BY u.username
, u.GroupID


Simplified query:

# Returns user and ID
select profile.user as user,
profile.group as group,
main.set as b
from user,
inner join group on group.ID = user.ID
inner join b on b.ID = user.ID
group by user.name


Error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax.

Answer
  1. Table aliases occur in the FROM portion.
  2. Column/Field Aliases occur in the SELECT portion.
  3. the schema.TableName is what's in the FROM you originally had which can be aliased as tables...

.

SELECT UM.username
     , GM.GroupID
     , B.Type
     , B.BatchName
     , B.Count
     , B.Date
FROM inno_Profiles.pUserMatch UM
INNER JOIN inno_Profiles.pGroupMatch GM 
   on GM.GroupID = UM.GroupID
INNER JOIN inno_donorsearch.batches  B
   on B.ID = UM.GroupID
GROUP BY UM.username, UM.GroupID