SQL inner join of multiple tables and search through database

Question

I'm making a search function in PHP and I have three tables that I wish to join to a single one; the three tables looks as follow:

band
ID | bands
---+----------
 1 | Muse
 2 | Coldplay
 3 | etc.

release
ID | releases
---+----------
 1 | Showbiz
 2 | Origin of Symmentry
 3 | etc.

track
ID | tracks
---+-----------
 1 | Sunburn
 2 | Muscle Museum 
 3 | etc.

I want these tables to be put into this:

discografic
ID | band_id  | release_id  | track_id
---+----------+-------------+---------
 1 | 1        | 1           | 1
 2 | 1        | 1           | 2
 3 | etc.

So that the table with the SQL code looks like this:

discografic
ID | bands    | releases    | tracks
---+----------+-------------+---------
 1 | Muse     | Showbiz     | Sunburn
 2 | Muse     | Showbiz     | Muscle Museum
 3 | etc.

I want to INNER JOIN these tables. I joined one but I can't really figure out how the get the last joined as well.

SELECT * 
FROM band
INNER JOIN discografic 
ON band.id = discografic.band_id

This should probably have its own question; I also want to be able to search this database, but only have the result show up once, and also reference to the band every time. For example, if I search "Showbiz" it will give me "Muse", and only show it once.

Note: This is for testing purposes only, security is none of my concerns.

joker · Accepted Answer · 2014-04-07 07:23:47Z

2

Try with this query:

select d.id,b.bands,r.releases,t.tracks from discografic as d INNER JOIN band as b on 
d.band_id=b.id INNER JOIN release as r on d.release_id=r.id INNER JOIN track as t on 
d.track_id=t.id GROUP BY d.id

edited Apr 7, 2014 at 7:23

joker

9909 silver badges23 bronze badges

answered Apr 7, 2014 at 7:14

Jenz

8,3718 gold badges50 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

joker · Accepted Answer · 2014-04-07 07:28:42Z

0

Try This query

    Select a.ID,b.bands,c.releases,d.tracks from discografic as a 
    inner join band as b on a.band_id = b.ID
    inner join release as c on a.release_id = c.ID
    inner join track as d on a.track_id = d.ID
    where b.bands = 'Muse'

answered Apr 7, 2014 at 7:28

joker

9909 silver badges23 bronze badges

3 Comments

Holt Over a year ago

It's probably Wamp creating problems but shouldn't this work? WHERE b.bands = '%$searchq%' I have made $searchq the variable for anything I enter into the search field.

joker Over a year ago

if you are trying to use % then use a like keywords instead of '=' in where condition (ex:- where b.bands like '%$searchq%'

Holt Over a year ago

The query you made worked, and the table looks like it should. I'll try and get my PHP to work with what you suggest. Thanks by the way.

Angel_Boy · Accepted Answer · 2014-04-07 07:30:31Z

0

Use this query to insert the data like you wanted:

Insert into discograpy
(id,bands,releases,tracks)
SELECT band.ID,bands,releases,tracks
FROM band
INNER JOIN releases 
ON band.id = releases.id
inner join track
on band.id = track.id

Use this query to show you only one band:

Declare @releases varchar(50)
Set @releases = 'showbiz'
SElect distinct bands from discograpy where releases = @releases

Here any variable can be passed or set in place of showbiz. This is an example

answered Apr 7, 2014 at 7:30

Angel_Boy

1,0782 gold badges8 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL inner join of multiple tables and search through database

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related