Solved

Help required -Writing join queries - mysql

Posted on 2013-05-20
17
349 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 32

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 

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 41

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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