Skip to main content
A
Anonymous
November 28, 2008
Question

SQL Join Help Please

  • November 28, 2008
  • 2 replies
  • 501 views
Hi,

I am joining three tables. ContentTypes, cmsSchemas and Relation.

This join is a many to many relationship. The ID of a content type is stored in a row on the relation table, and it's corresponding cmsSchema ID is stored alongside this. So, the relation table basically contains a long list of relationships between ContentTypes and cmsSchemas.

A content type can have many schemas, and different content types can have the same schema.

What I want to output is the following:

Content type 1
-------------------------
schema 1
schema 2

Content type 2
-------------------------
schema 2

BUT, what I am getting is the following:

Content type 1
-------------------------
schema 1

Content type 1
-------------------------
schema 2

Content type 2
-------------------------
schema 2



So, basically, what I want to do, is output a list of content types with the associated schemas beneath them. But because my relation table contains lots of rows for each association, I'm getting each one.

Any help on modifying my query would be greatly appreciated.

Please note, my term "schema" should not be confused with the database definition of "schema"...just look at it as a name like any other.

Many thanks,
Mikey.
    This topic has been closed for replies.

    2 replies

    A
    Anonymous
    November 28, 2008
    Hi,

    I eventually managed to figure this out inspired by the knowledge of Dan. Thanks!

    Fore those who need this knowledge in future, the key was to use nested CFOUTPUT tags with the GROUP attribute for each column.

    I think one of the main reasons I found this difficult to solve was the lack of resources online - but in turn, this made me more determined, so here is the code below:

    <cfquery name="qryGetContentTypesAndSchemas" datasource="#request.dsn#" username="#request.username#" password="#request.password#">
    SELECT

    contentTypeID,
    contentTypeTitle,
    relationContentTypeID,
    relationSchemaID,
    schemaID,
    schemaTitle

    FROM contentTypes AS ct
    LEFT JOIN relation AS r
    ON ct.contentTypeID = r.relationContentTypeID
    LEFT JOIN cmsSchemas AS s
    ON r.relationSchemaID = s.schemaID
    GROUP BY ct.contentTypeID, s.schemaID
    ORDER BY ct.contentTypeTitle, s.schemaTitle
    ASC
    </cfquery>

    <cfoutput query="qryGetContentTypesAndSchemas" group="contentTypeTitle">
    #qryGetContentTypesAndSchemas.contenttypetitle#<br />
    <cfoutput group="schemaTitle">
    -----> #qryGetContentTypesAndSchemas.schemaTitle#<br />
    </cfoutput>
    </cfoutput>

    Thanks again!!

    Mikey.
    D
    Dan_Bracuk
    Inspiring
    November 28, 2008
    Suggestion 1 - select just the fields you need instead of everything.

    Suggestion 2 - write your joins like this:

    from table1 join table2 on whatever
    join table3 on whatever

    Suggestion 3 - read the cfml on cfoutput and pay particular attention to the group attribute
    A
    Anonymous
    November 28, 2008
    Hi Dan,

    Thanks for the advice. Even joining the way you mentioned still outputs the same as I am already getting.

    I was under the impression that the group attribute works the same as the GROUP BY in SQL. Is this correct? Reading the docs seems to lead me to this as you simple group by a column name.

    Hmm.

    Thanks for the info. If anyone else has more advice please do reply. It's appreciated.

    Many thanks!

    Mikey.
    D
    Dan_Bracuk
    Inspiring
    November 28, 2008
    quote:
    Originally posted by: Kapitaine

    I was under the impression that the group attribute works the same as the GROUP BY in SQL. Is this correct?

    Mikey.

    It is not correct.

    group by in sql is a mandatory clause when you are selecting a combination of fields and aggregates. For example.

    select field1, count(field1)
    from atable

    will crash unless you add
    group by field1.

    if you use it without an aggregate, it becomes another way of doing a select distinct, but with more typing.

    cfoutput group is a Cold Fusion feature. It has nothing to do with sql but it will enable you to achieve the result you stated in the OP.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices