Solved

Help required -Writing join queries - mysql

Posted on 2013-05-20
17
343 Views
Last Modified: 2013-07-02
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
Comment
Question by:Sreejith22
17 Comments
 
LVL 31

Expert Comment

by:awking00
ID: 39181330
>>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
 
LVL 4

Accepted Solution

by:
Kent Fichtner earned 500 total points
ID: 39181338
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
 

Author Comment

by:Sreejith22
ID: 39181534
@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
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39181558
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
 

Author Comment

by:Sreejith22
ID: 39181708
where to give the WHERE clause ?

As I have given in my question, the id of collections table would be passed to query
0
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39181736
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
 

Author Comment

by:Sreejith22
ID: 39181742
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
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39181817
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Sreejith22
ID: 39181884
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
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39181922
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
 

Author Comment

by:Sreejith22
ID: 39181932
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
 

Author Comment

by:Sreejith22
ID: 39181944
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
 
LVL 40

Expert Comment

by:Sharath
ID: 39182007
Can you post some sample data with expected result?
0
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39182100
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
 

Author Comment

by:Sreejith22
ID: 39182412
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
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39182461
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
 

Author Comment

by:Sreejith22
ID: 39182468
ok, it was my mistake
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

747 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

12 Experts available now in Live!

Get 1:1 Help Now