• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

Help required -Writing join queries - mysql

I have 3 tables

1)collections(a music collection - id of which would be passed) - id is the relevant column name

2)collection_content_mappings(contains the columns pair - collections id(collections table above) - content id(content_details below)) - collections_id, content_id are the relevant column names in this table
3)content_details - content_id is the relevant column name in this table
4)content_additionals - content_id(of content_details above) is the relevant column name

Need to fetch all the details from content_details and content_additionals; the only input would be corresponding to the id field for 'collections' table.

I would appreciate, if someone can help me write joint queries for the same.
0
Sreejith22
Asked:
Sreejith22
1 Solution
 
awking00Commented:
>>I have 3 tables<<
Then you list four. Could you provide some relevant sample data for the tables and what your desired output should be?
0
 
Kent FichtnerInformation Technology Systems SupervisorCommented:
SELECT (your selections here)
FROM COLLECTION_CONTENT_MAPPINGS Inner Join CONTENT_DETAILS ON COLLECTION_CONTENT_MAPPINGS.CONTENT_ID = CONTENT_DETAILS.CONTENT_ID

INNER JOIN CONTENT_ADDITIONALS ON COLLECTION_CONTENT_MAPPINGS.CONTENT_ID = CONTENT_ADDITIONALS.CONTENT_ID


something like that should work.
0
 
Sreejith22Author Commented:
@Amerilabkfichtner - Looks like what I need.


"SELECT (your selections here)"

Can you please show by putting some arbitrary selection fields for content_details  and content_additionals, so that I can test and close this
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Kent FichtnerInformation Technology Systems SupervisorCommented:
SELECT COLLECTION_CONTENT_MAPPINGS.CONTENCT_ID
FROM COLLECTION_CONTENT_MAPPINGS Inner Join CONTENT_DETAILS ON COLLECTION_CONTENT_MAPPINGS.CONTENT_ID = CONTENT_DETAILS.CONTENT_ID

INNER JOIN CONTENT_ADDITIONALS ON COLLECTION_CONTENT_MAPPINGS.CONTENT_ID = CONTENT_ADDITIONALS.CONTENT_ID
0
 
Sreejith22Author Commented:
where to give the WHERE clause ?

As I have given in my question, the id of collections table would be passed to query
0
 
Kent FichtnerInformation Technology Systems SupervisorCommented:
it goes at the very bottom,  I highly recommend that you look in to SQL queries.  How to write them and better understand them.  It will help with any future projects you do with it.  My best reference is here
0
 
Sreejith22Author Commented:
ok, just a normal where clause would suffice I presume. Since we were using joins, this trivial question; as I am doing join queries for the first time
0
 
Kent FichtnerInformation Technology Systems SupervisorCommented:
Yep the normal statement goes as follows, this can be for one or 10 tables, the Where always goes at the bottom.  The catch is, with more then one table you have to preface the column name with the table name.

example:  COLLECTION_CONTENT_MAPPINGS.CONTENT_ID

Select <statement>
FROM <tables>
WHERE <statement>

Hope that makes sense.
0
 
Sreejith22Author Commented:
In short my query would look like this:

SELECT collection_content_mappings.tbl_content_common_id
FROM collection_content_mappings Inner Join tbl_content_commons ON collection_content_mappings.tbl_content_common_id = tbl_content_commons.id 

INNER JOIN tbl_content_additionals ON collection_content_mappings.tbl_content_common_id = tbl_content_additionals.content_content_code 

WHERE collection_content_mappings.collection_id = 1

Open in new window


There is a minor problem though:


This part is the problem:

collection_content_mappings.tbl_content_common_id = tbl_content_additionals.content_content_code

Open in new window




The field "collection_content_mappings.tbl_content_common_id" is not the one that need to be equated there; instead I have to take tbl_content_commons.content_common_code from tbl_content_commons where collection_content_mappings.tbl_content_common_id = tbl_content_commons.id . How do I add this to the above query ?
0
 
Kent FichtnerInformation Technology Systems SupervisorCommented:
where did "tbl" come from?  What is the error that you are getting?

The basic syntax for a join is this:

<Main_Table_Name> Inner Join <Main_table_Name>.<Column_Name> = <Secondary_Table_Name>.<Column_Name>

Open in new window


Any additional Tables are just

Inner Join <Main_table_Name>.<Column_Name> = <Secondary_Table_Name>.<Column_Name>

Open in new window

0
 
Sreejith22Author Commented:
The table name is like that only. I have not given proper name in my original post.

I do not get any errors, but the query does not give any result set. And it is due to the following which I have mentioned above:


The field "collection_content_mappings.tbl_content_common_id" is not the one that need to be equated there; instead I have to take tbl_content_commons.content_common_code from tbl_content_commons where collection_content_mappings.tbl_content_common_id = tbl_content_commons.id . How do I add this to the above query ?

Open in new window

0
 
Sreejith22Author Commented:
Please go through the comment inside the query below. Then what I am trying to explain would be clear

SELECT collection_content_mappings.tbl_content_common_id
FROM collection_content_mappings Inner Join tbl_content_commons ON collection_content_mappings.tbl_content_common_id = tbl_content_commons.id 

INNER JOIN tbl_content_additionals ON collection_content_mappings.tbl_content_common_id(here I need to write a select query on tbl_content_commons in place of this id to the left of bracket. I need to select content_content_code from tbl_content_commons where id to the left of this bracket = id in tbl_content_commons) = tbl_content_additionals.content_content_code 

WHERE collection_content_mappings.collection_id = 1

Open in new window

0
 
SharathData EngineerCommented:
Can you post some sample data with expected result?
0
 
Kent FichtnerInformation Technology Systems SupervisorCommented:
Sreejth22,

I am sorry I don't understand what you are asking for.  Please see my comments on how to make an inner join.  

Inner Join <First_Table_Name>.<Column_Name> = <Secondary_Table_Name>.<Column_Name>

If you need more assistance please write out the tables and the columns in them and I can see if there is a different way to connect them.
0
 
Sreejith22Author Commented:
SELECT tbl_content_commons.id
    FROM tbl_content_commons
    Inner Join collection_content_mappings ON                         collection_content_mappings.tbl_content_common_id = tbl_content_commons.id
    INNER JOIN tbl_content_additionals  ON tbl_content_additionals.content_content_code  = tbl_content_commons.content_common_code WHERE tbl_content_commons.id =collection_content_mappings.tbl_content_common_id and collection_content_mappings.collection_id = 1 .


 Is this syntax OK for the WHERE clause. I am not able to test this at the moment. so posting this comment prior to testing
0
 
Kent FichtnerInformation Technology Systems SupervisorCommented:
I don't think you need that first Whare statement, I believe that will be taken care of by the first inner join.  I would take it out, and see how many records it comes up with, then put it in and see if anything changes, my guess is it won't.
0
 
Sreejith22Author Commented:
ok, it was my mistake
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now