Simple question on multiple INNER JOINs

• milasoft64

  SSC Rookie

  Points: 46

  More actions

  January 30, 2019 at 1:01 pm

  #381331

  Hello

  I am trying to join tables in an Access Database, which I'm reading via SQL queries under Active Server.

  One table is called "Games" which contains a list of software titles.

  

  As you can see there is a Name, YE_ID (year), Publisher (pu_id) and Genre (ie. Action, Space Invader, etc.) as GE_ID. This is in Access.

  I'm trying to display a list of these games. Now since the year, genre and publisher are all stored in other tables, I have to JOIN them to get the records.
  
  

  So for example Towers of Hanoi, we can see that the YE_ID is 1, which means I have to read YE_ID from the Years table to ascertain the year of release. Then I have to take Publisher (PU_ID) from Games and match it to PU_ID in Publishers table to find the publisher. I have to take PR_Id from Games and match it to PR_ID in Programmers to find the programmer.

  This is not hard concept wise, but in SQL I'm getting errors when I try to join my second table or third.

  My code is:
  select name,pu_id,ye_id,genre from games g inner join Genres p on g.ge_id = p.ge_id where name like 'pac man'` 

  However all other attempts to add more JOINs fails. Could anyone help? :grinning:

• pietlinden

  SSC Guru

  Points: 63519

  More actions

  January 30, 2019 at 1:21 pm

  #2020624

  Is this an Access question or a SQL Server question? I'm asking because SQL Server uses single quotes to delimit text strings and Access uses double quotes. If you're writing the query in Access, it will translate the query syntax for SQL Server for you.

• milasoft64

  SSC Rookie

  Points: 46

  More actions

  January 30, 2019 at 1:33 pm

  #2020625

  I have no issue with the text strings, it's the SQL query that's not working. The data is in Access.

• Jonathan AC Roberts

  SSCoach

  Points: 18378

  More actions

  January 30, 2019 at 2:04 pm

  #2020629

  It's helpful to read if you add a few new-lines to your code.
  To add another inner join you could put it in like this:
  select g.name,pu_id,ye_id,genre
from games g
inner join Genres p on g.ge_id = p.ge_id
inner join myTable t on g.ge_id = t.ge_id
where g.name like 'pac man'
  If you show what you've tried that doesn't work it would be easier for us to fix.

• ninjamy

  SSC Enthusiast

  Points: 117

  More actions

  January 30, 2019 at 2:19 pm

  #2020631

  What is the error you are getting?

• milasoft64

  SSC Rookie

  Points: 46

  More actions

  January 30, 2019 at 2:23 pm

  #2020632

  select g.name,scrnshotfilename,publisher,ye_id,genre from games g inner join Genres p on g.ge_id = p.ge_id inner join publishers t on g.pu_id = t.pu_id where g.name = 'guess'

  is working under SQL 2012 but when I try calling it from the Access DB (same command) it gives me internal server error. I even tried double quotes.

  UGH!

  Thank you... though I'm still pulling out my hair.

• Jonathan AC Roberts

  SSCoach

  Points: 18378

  More actions

  January 31, 2019 at 7:23 am

  #2020698

  milasoft64 - Wednesday, January 30, 2019 2:23 PM

  select g.name,scrnshotfilename,publisher,ye_id,genre from games g inner join Genres p on g.ge_id = p.ge_id inner join publishers t on g.pu_id = t.pu_id where g.name = 'guess'

  is working under SQL 2012 but when I try calling it from the Access DB (same command) it gives me internal server error. I even tried double quotes.

  UGH!

  Thank you... though I'm still pulling out my hair.

  You can rewrite a query that has inner joins to just have joins in the where clause.
  Just comma seperate the table names and put the joins in the where:
  select g.name,scrnshotfilename,publisher,ye_id,genre
from games g
inner join Genres p
         on g.ge_id = p.ge_id
inner join publishers t
         on g.pu_id = t.pu_id
where g.name = 'guess'
  Is the same as:
  select g.name,scrnshotfilename,publisher,ye_id,genre
  from games g, Genres p, publishers t
 where g.ge_id = p.ge_id
   and g.pu_id = t.pu_id
   and g.name = 'guess'

• drew.allen

  SSC Guru

  Points: 76999

  More actions

  January 31, 2019 at 8:28 am

  #2020709

  Jonathan AC Roberts - Thursday, January 31, 2019 7:23 AM

  milasoft64 - Wednesday, January 30, 2019 2:23 PM

  select g.name,scrnshotfilename,publisher,ye_id,genre from games g inner join Genres p on g.ge_id = p.ge_id inner join publishers t on g.pu_id = t.pu_id where g.name = 'guess'

  is working under SQL 2012 but when I try calling it from the Access DB (same command) it gives me internal server error. I even tried double quotes.

  UGH!

  Thank you... though I'm still pulling out my hair.

  You can rewrite a query that has inner joins to just have joins in the where clause.
  Just comma seperate the table names and put the joins in the where:
  select g.name,scrnshotfilename,publisher,ye_id,genre
from games g
inner join Genres p
         on g.ge_id = p.ge_id
inner join publishers t
         on g.pu_id = t.pu_id
where g.name = 'guess'
  Is the same as:
  select g.name,scrnshotfilename,publisher,ye_id,genre
  from games g, Genres p, publishers t
 where g.ge_id = p.ge_id
   and g.pu_id = t.pu_id
   and g.name = 'guess'

  These style joins have been deprecated and should not be used in new development.

  The problem isn't with the join, because it works in SSMS.  We don't know what the actual problem is, because the OP still hasn't posted the error message.  This isn't a game of pin the tail on the donkey where we stab in the dark hoping to hit the correct answer.  If the OP wants an answer, he'll need to post the actual error message.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Jonathan AC Roberts

  SSCoach

  Points: 18378

  More actions

  January 31, 2019 at 9:22 am

  #2020724

  drew.allen - Thursday, January 31, 2019 8:28 AM

  Jonathan AC Roberts - Thursday, January 31, 2019 7:23 AM

  milasoft64 - Wednesday, January 30, 2019 2:23 PM

  select g.name,scrnshotfilename,publisher,ye_id,genre from games g inner join Genres p on g.ge_id = p.ge_id inner join publishers t on g.pu_id = t.pu_id where g.name = 'guess'

  is working under SQL 2012 but when I try calling it from the Access DB (same command) it gives me internal server error. I even tried double quotes.

  UGH!

  Thank you... though I'm still pulling out my hair.

  You can rewrite a query that has inner joins to just have joins in the where clause.
  Just comma seperate the table names and put the joins in the where:
  select g.name,scrnshotfilename,publisher,ye_id,genre
from games g
inner join Genres p
         on g.ge_id = p.ge_id
inner join publishers t
         on g.pu_id = t.pu_id
where g.name = 'guess'
  Is the same as:
  select g.name,scrnshotfilename,publisher,ye_id,genre
  from games g, Genres p, publishers t
 where g.ge_id = p.ge_id
   and g.pu_id = t.pu_id
   and g.name = 'guess'

  These style joins have been deprecated and should not be used in new development.

  The problem isn't with the join, because it works in SSMS.  We don't know what the actual problem is, because the OP still hasn't posted the error message.  This isn't a game of pin the tail on the donkey where we stab in the dark hoping to hit the correct answer.  If the OP wants an answer, he'll need to post the actual error message.

  Drew

  I haven't used Access for donk's but I think it requires brackets in the FROM clause for more than one join. 
  Try this:
  select g.name,scrnshotfilename,publisher,ye_id,genre
  from (games g
 inner join Genres p on g.ge_id = p.ge_id)
 inner join publishers t on g.pu_id = t.pu_id
 where g.name = 'guess'

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  February 1, 2019 at 1:15 pm

  #2020900

  >> One table is called "Games" which contains a list of software titles. <<

  Where is the DDL for the table? This has been posting netiquette for all SQL forms for the last 30+ years. It's not a lot of fun to transcribe data from your pictures into DDL.

  >> As you can see there is a Name, YE_ID (year), Publisher (pu_id) and Genre (ie. Action, Space Invader, etc.) as GE_ID. This is in Access. <<

  The bad news is that you've got everything wrong for data modeling viewpoint, regardless of the language. The year is a temporal unit of measure, which means it has no identifier. There is no such thing as a generic "name"; it has be the name of something in particular, etc.

  Here's a quick attempt at redoing your table. Notice that by definition, and not as an option, a table must have a key. So we have to guess at it. I'm going to guess that it's the name of the game. I'm going to guess that what should of been a publisher code would be off of the UPC code on the box. I'm going to guess that you meant the publication or copyright year of the game. I'm not sure what all of your genres are. Is there some industry-standard agreement on the categories?

  CREATE TABLE Games
(games_name VARCHAR (20) NOT NULL,
game_category VARCHAR(20) NOT NULL,
publisher_code CHAR(5) NOT NULL
 REFERENCES Publishers(publisher_code),
publication_year CHAR(4) NOT NULL
 CHECK (publication_year LIKE '[12][0-9][0-9][0-9]'),
PRIMARY KEY (game_name, publication_year, publisher_code),
genre_category VARCHAR(15) NOT NULL
 CHECK(genre_category IN (..));

  CREATE TABLE Publishers
(publisher_code CHAR(5) NOT NULL PRIMARY KEY, --- upc code?
...);
  >> I'm trying to display a list of these games. Now since the year, genre and publisher are all stored in other tables, I have to JOIN them to get the records [sic]. <<

  Only because you have a really bad design. A year is a temporal measurement of an attribute, not another kind of entity. It should never be in a table. Unless the genres are constantly changing, they can be put in a check constraint instead of a reference. However, publishers are an actual entity and need their own table.

  SELECT G.game_name, G.publication_year, G.publisher_code, P.publisher_name, G.genre_category
  FROM Games AS G, Publishers AS P
WHERE G. game_name = 'pacpac man'
  AND G.publisher_code = P.publisher_code;


  Please post DDL and follow ANSI/ISO standards when asking for help. 

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 4, 2019 at 12:12 pm

  #2021077

  Jonathan AC Roberts - Thursday, January 31, 2019 9:22 AM

  drew.allen - Thursday, January 31, 2019 8:28 AM

  Jonathan AC Roberts - Thursday, January 31, 2019 7:23 AM

  milasoft64 - Wednesday, January 30, 2019 2:23 PM

  select g.name,scrnshotfilename,publisher,ye_id,genre from games g inner join Genres p on g.ge_id = p.ge_id inner join publishers t on g.pu_id = t.pu_id where g.name = 'guess'

  is working under SQL 2012 but when I try calling it from the Access DB (same command) it gives me internal server error. I even tried double quotes.

  UGH!

  Thank you... though I'm still pulling out my hair.

  You can rewrite a query that has inner joins to just have joins in the where clause.
  Just comma seperate the table names and put the joins in the where:
  select g.name,scrnshotfilename,publisher,ye_id,genre
from games g
inner join Genres p
         on g.ge_id = p.ge_id
inner join publishers t
         on g.pu_id = t.pu_id
where g.name = 'guess'
  Is the same as:
  select g.name,scrnshotfilename,publisher,ye_id,genre
  from games g, Genres p, publishers t
 where g.ge_id = p.ge_id
   and g.pu_id = t.pu_id
   and g.name = 'guess'

  These style joins have been deprecated and should not be used in new development.

  The problem isn't with the join, because it works in SSMS.  We don't know what the actual problem is, because the OP still hasn't posted the error message.  This isn't a game of pin the tail on the donkey where we stab in the dark hoping to hit the correct answer.  If the OP wants an answer, he'll need to post the actual error message.

  Drew

  I haven't used Access for donk's but I think it requires brackets in the FROM clause for more than one join. 
  Try this:
  select g.name,scrnshotfilename,publisher,ye_id,genre
  from (games g
 inner join Genres p on g.ge_id = p.ge_id)
 inner join publishers t on g.pu_id = t.pu_id
 where g.name = 'guess'

  That's likely the cause of the problem.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)