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_mappi
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.
1)collections(a music collection - id of which would be passed) - id is the relevant column name
2)collection_content_mappi
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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
"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
SELECT COLLECTION_CONTENT_MAPPING
FROM COLLECTION_CONTENT_MAPPING
INNER JOIN CONTENT_ADDITIONALS ON COLLECTION_CONTENT_MAPPING
FROM COLLECTION_CONTENT_MAPPING
INNER JOIN CONTENT_ADDITIONALS ON COLLECTION_CONTENT_MAPPING
ASKER
where to give the WHERE clause ?
As I have given in my question, the id of collections table would be passed to query
As I have given in my question, the id of collections table would be passed to query
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
ASKER
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
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_MAPPING
Select <statement>
FROM <tables>
WHERE <statement>
Hope that makes sense.
example: COLLECTION_CONTENT_MAPPING
Select <statement>
FROM <tables>
WHERE <statement>
Hope that makes sense.
ASKER
In short my query would look like this:
There is a minor problem though:
This part is the problem:
The field "collection_content_mappin
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 = 1There is a minor problem though:
This part is the problem:
collection_content_mappings.tbl_content_common_id = tbl_content_additionals.content_content_codeThe field "collection_content_mappin
where did "tbl" come from? What is the error that you are getting?
The basic syntax for a join is this:
Any additional Tables are just
The basic syntax for a join is this:
<Main_Table_Name> Inner Join <Main_table_Name>.<Column_Name> = <Secondary_Table_Name>.<Column_Name>Any additional Tables are just
Inner Join <Main_table_Name>.<Column_Name> = <Secondary_Table_Name>.<Column_Name>ASKER
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:
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 ?ASKER
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
Can you post some sample data with expected result?
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
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.
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
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.
ASKER
SELECT tbl_content_commons.id
FROM tbl_content_commons
Inner Join collection_content_mapping
INNER JOIN tbl_content_additionals ON tbl_content_additionals.co
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
FROM tbl_content_commons
Inner Join collection_content_mapping
INNER JOIN tbl_content_additionals ON tbl_content_additionals.co
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
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.
ASKER
ok, it was my mistake
Then you list four. Could you provide some relevant sample data for the tables and what your desired output should be?