10 Replies - 1146 Views - Last Post: 01 September 2016 - 02:00 PM Rate Topic: -----

#1 Eight405   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 22
  • Joined: 30-August 16

Filtering SQL query results with JOIN

Posted 31 August 2016 - 12:42 PM

I've been trying to get this to work for a while but I don't think I have a very firm grasp of how this works so I have no idea what to do.

I currently have three different tables. The first called "games" contains an A_I id and a bunch of titles, the second called "genres" contains an A_I id and a bunch of genres, and the third one called "games_genres" that contains an A_I id and two columns, one with an id of the game and the second with the id of the genre, both of these have Foreign Keys constraints to the id columns in the other two tables , like so:
0001|0005
0001|0003
0002|0003
Where the numbers on the left are games and the ones on the right are genres

Now, by using SQL queries I was at least able to make it display only the results that I wanted

SELECT *
FROM games_genres
WHERE genresid_FK=5


However I've been trying to use JOIN to instead make it show the title the id is connected to and the genre the second id is connected to, but I just can't wrap my head around how JOIN works. I'm not even sure if what I'm trying to do is possible at all.

Is JOIN what I need to transform those numbers into the text they're referring to? Can someone point me in the right direction?

Is This A Good Question/Topic? 0
  • +

Replies To: Filtering SQL query results with JOIN

#2 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6554
  • View blog
  • Posts: 26,569
  • Joined: 12-December 12

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 12:06 AM

Yes, you need to continue to wrestle with joins. Then also, importantly, to show your attempt to use them.

Table Joins
Join Three Tables
Was This Post Helpful? 1
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 593
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 04:57 AM

select book.name,book.authorId author.name
from author
inner join book on author.id = book.authorId
where author like '%morgan freeman%'


should return something like

Galaxy, 00MF, Morgan Freeman


all the above is just made up but you should get an idea on how to use inner joins

This post has been edited by DarenR: 01 September 2016 - 06:11 AM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6554
  • View blog
  • Posts: 26,569
  • Joined: 12-December 12

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 05:05 AM

I know that is just a brief example but the use of Like without a wildcard should be discouraged (we see it too much here). Without a wildcard you are just testing for equality and should use '='. author = 'Morgan Freeman'.

(I didn't know Morgan Freeman was an author?)
Was This Post Helpful? 0
  • +
  • -

#5 DarenR   User is offline

  • D.I.C Lover

Reputation: 593
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 06:11 AM

fixed
Was This Post Helpful? 0
  • +
  • -

#6 Eight405   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 22
  • Joined: 30-August 16

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 08:09 AM

View Postandrewsw, on 01 September 2016 - 12:06 AM, said:

Yes, you need to continue to wrestle with joins. Then also, importantly, to show your attempt to use them.

Table Joins
Join Three Tables

These two guides helped a lot, I wonder why they're not among google's results for JOIN tutorials.
I managed to get it to show me the title of a game by using the actual word of the genre instead of its identification, which is really neat

SELECT games.titles
FROM games_genres
INNER JOIN games ON games_genres.gamesid_FK=games.id
INNER JOIN genres ON games_genres.genresid_FK=genres.id
WHERE genre = 'FPS'


I'm not sure I understand 100% how they work, but at least I now have it working, so I can go from here.
Thank you so much :D
Was This Post Helpful? 1
  • +
  • -

#7 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6554
  • View blog
  • Posts: 26,569
  • Joined: 12-December 12

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 08:56 AM

That's nice to hear, thank you :)

(A few more visits and votes would probably help to push the tutorials higher in the results ;))
Was This Post Helpful? 0
  • +
  • -

#8 DarenR   User is offline

  • D.I.C Lover

Reputation: 593
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 09:12 AM

the way they work is sort of like this:

go to a grocery store
find apples
you will see a sign with apples
on that sign there is a price, name, and some sort of code
the apple has a name
the computer system has all the above information
so when they look up the apple by name it will relate to the sign and the apple
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6554
  • View blog
  • Posts: 26,569
  • Joined: 12-December 12

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 01:24 PM

I really like the description here:

How to join three tables in SQL query

Quote

First JOIN statement will join Employee and Register and create a temporary table which will have dept_id as another column. Now second JOIN statement will join this temp table with Department table on dept_id to get the desired result.

Imagine that, here JOIN Department d ON r.dept_id=d.dept_id;, r.dept_id is not referring to the original Register table, but to an additional column (from the Register table) that appears alongside the temporary resultset from the first join.

Anyway, I thought it helpful ;)
Was This Post Helpful? 0
  • +
  • -

#10 Eight405   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 22
  • Joined: 30-August 16

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 01:48 PM

As I use them I'm starting to get the gist of it. My main trouble was coming from thinking that I had to select all the tables I needed, which basically meant I was receiving a whole lot of data that I didn't need.

Also the fact that I had I wasn't sure which table I should call right after the JOIN. But once I realized that I didn't need to actually select all the tables I wanted to use but only the ones I wanted to receive the data from it made more sense.

I gave you a + on the guides you posted btw, hopefully that helps with Google :D
Was This Post Helpful? 0
  • +
  • -

#11 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2542
  • View blog
  • Posts: 10,187
  • Joined: 03-December 12

Re: Filtering SQL query results with JOIN

Posted 01 September 2016 - 02:00 PM

It depends on how the relation is defined. For instance, I have an employees table, a group table, and a partners table. The partners can have many groups and the employee is under a group.

If I want all of the employees under a specific partner, I would have to join the group, but don't need any information other than, the group#.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1