Solved

Help required writing join query

Posted on 2013-05-23
1
240 Views
Last Modified: 2013-06-08
I need to get name(of a collection) from Table1 and name of all the songs in that collection from table 3 . Table 2 is the connection between table 1 and table 3. music_playlist_id connects table 2 to table 1. tbl_content_common_id connects table 2 with table 3.

Table 1

CREATE TABLE IF NOT EXISTS `music_playlists` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `collection_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
)

Open in new window


Table 2

CREATE TABLE IF NOT EXISTS `playlist_song_mappings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `music_playlist_id` int(11) NOT NULL,
  `tbl_content_common_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

Open in new window



Table 3

CREATE TABLE IF NOT EXISTS `tbl_content_commons` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `content_common_code` varchar(20) CHARACTER SET utf8 NOT NULL,
  `content_common_name` varchar(100) CHARACTER SET utf8 NOT NULL
)

Open in new window


Any help is much appreciated
0
Comment
Question by:Sreejith22
1 Comment
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 39190506
SELECT m.[name] AS CollectionName, c.content_common_name
FROM music_playlists m INNER JOIN
    playlist_song_mappings p ON m.collection_id = p.music_playlist_id INNER JOIN
    tbl_content_commons c ON p.tbl_content_common_id = c.id
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now