Skip to main content
A
Anonymous
February 16, 2009
Answered

Almost a month can't get query to work...

  • February 16, 2009
  • 18 replies
  • 2967 views
Help, I can't seem to get this query to work like I need it to. I'm not sure what I'm missing but it all seems fine to me.

Here is what my order table looks like (simlified)
--------------------------------------------------------------------------------------------------------------
id | trait_a | trait_b
--------------------------------------------------------------------------------------------------------------
1 5 6
2 2 1


Here is my materials table (simlified)
---------------------------------------------------------------------------------------------------------------
id | label
---------------------------------------------------------------------------------------------------------------
1 Black
2 Cherry
5 Orange
6 Pink

So for order id 1 it has two traits I need to return Orange and Pink. Order 2 has Cherry and Black for trait a and b. Every way I seem to do this I always just get the second item "b"'s label. I can't get it to return each one. Here's my code. Thanks!
    This topic has been closed for replies.
    Correct answer Kronin555
    Are any of those other fields you're joining on null in your bag_orders table? Namely:
    bag_orders.order_party
    bag_orders.order_consultant
    bag_orders.order_hostess
    bag_orders.order_customer
    bag_orders.bag_style
    bag_orders.bag_trait_a
    bag_orders.bag_trait_b
    ?
    If any of those fields you're doing an inner join on are null, or don't have matching records in the tables you're joining to, the query won't return any rows.

    Also, to simplify, it looks like arguments.thisParty is always going to be a single integer. You can remove the IN check and just do an equality check:
    WHERE bag_orders.order_party = <cfqueryparam value="#arguments.thisParty#" cfsqltype="cf_sql_integer">

    18 replies

    Show previous replies
    A
    Anonymous
    February 16, 2009
    Not sure what a mapping table is. :). I'm a Flash guy who is limping along with CF and mySQL. Is that a table that I create somewhere in mySQL that shows relationships?
    A
    Anonymous
    February 16, 2009
    Doesn't work, same results. Here is my code...
    K
    Kronin555
    Participating Frequently
    February 16, 2009
    You're still not using my code. Here, I'll post it again for clarity sake...

    <cfquery name="getAllPartyBagsQuery" datasource="******" username="******" password="******">
    SELECT
    bag_orders.order_id AS order_id,
    parties.party_name AS party_name,
    consultants.consultant_name AS party_consultant,
    hostesses.hostess_name AS party_hostess,
    customers.customer_name AS customer_name,
    bag_styles.style_name AS style_name,
    materials_a.material_label AS bag_trait_a,
    materials_b.material_label AS bag_trait_b
    FROM bag_orders INNER JOIN parties ON bag_orders.order_party=parties.party_id
    INNER JOIN consultants ON bag_orders.order_consultant=consultants.consultant_id
    INNER JOIN hostesses ON bag_orders.order_hostess=hostesses.hostess_id
    INNER JOIN customers ON bag_orders.order_customer=customers.customer_id
    INNER JOIN bag_styles ON bag_orders.bag_style=bag_styles.style_id
    INNER JOIN materials as materials_a ON bag_orders.bag_trait_a=materials_a.material_id
    INNER JOIN materials as materials_b ON bag_orders.bag_trait_b=materials_b.material_id
    WHERE bag_orders.order_party IN(<cfqueryParam value="#arguments.thisParty#" cfsqltype="cf_sql_integer">)
    GROUP BY bag_orders.order_id
    </cfquery>
    K
    Kronin555
    Participating Frequently
    February 16, 2009
    On a sidenote, if you had used a mapping table for these bag traits, you wouldn't have had this problem to begin with.
    K
    Kronin555
    Participating Frequently
    February 16, 2009
    C-rock, look at my code again. I'm joining to the materials table twice, but aliasing it. This allows you to do what you need, which is do 2 joins to the same table using 2 different ids.

    INNER JOIN materials as materials_a ON bag_orders.bag_trait_a=materials_a.material_id
    INNER JOIN materials as materials_b ON bag_orders.bag_trait_b=materials_b.material_id
    A
    Anonymous
    February 16, 2009
    The problem is that the bag_orders table needs to inner join trait_a column and trait_b column with the materials table materials_id column. I believe the problem is that its not allowing me to inner join the same two tables twice.
    A
    Anonymous
    February 16, 2009
    Kronin's code shows two different material tables, that's easy. However there's only one material table.
    D
    Dan_Bracuk
    Inspiring
    February 16, 2009
    quote:
    Originally posted by: C-Rock
    Kronin's code shows two different material tables, that's easy. However there's only one material table.

    Kronin's code shows one material table twice. Look at it again.
    D
    Dan_Bracuk
    Inspiring
    February 16, 2009
    Something like this might be helpful.

    select t1.field1 f1, t2.field1 f2
    from table1 t1 join table1 t2 on t1.id = t2.id
    etc

    Note that t1 and t2 are the same table.
    A
    Anonymous
    February 16, 2009
    I've tried that before and I still get the b trait back for a and b. It's returning trait_a and trait_b as the same lable/name when clearly in the database they have two different material ids.

    K
    Kronin555
    Participating Frequently
    February 16, 2009
    <cfquery name="getAllPartyBagsQuery" datasource="******" username="******" password="******">
    SELECT
    bag_orders.order_id AS order_id,
    parties.party_name AS party_name,
    consultants.consultant_name AS party_consultant,
    hostesses.hostess_name AS party_hostess,
    customers.customer_name AS customer_name,
    bag_styles.style_name AS style_name,
    materials_a.material_label AS bag_trait_a,
    materials_b.material_label AS bag_trait_b

    FROM bag_orders INNER JOIN parties ON bag_orders.order_party=parties.party_id
    INNER JOIN consultants ON bag_orders.order_consultant=consultants.consultant_id
    INNER JOIN hostesses ON bag_orders.order_hostess=hostesses.hostess_id
    INNER JOIN customers ON bag_orders.order_customer=customers.customer_id
    INNER JOIN bag_styles ON bag_orders.bag_style=bag_styles.style_id
    INNER JOIN materials as materials_a ON bag_orders.bag_trait_a=materials_a.material_id
    INNER JOIN materials as materials_b ON bag_orders.bag_trait_b=materials_b.material_id
    WHERE bag_orders.order_party IN(<cfqueryParam value="#arguments.thisParty#" cfsqltype="cf_sql_integer">)
    GROUP BY bag_orders.order_id
    </cfquery>
    D
    Dan_Bracuk
    Inspiring
    February 16, 2009
    Something like this might be helpful.

    select t1.field1 f1, t2.field1 f2
    from table1 t1 join table1 t2 on t1.id = t2.id
    etc

    Note that t1 and t2 are the same table.
    D
    Dan_Bracuk
    Inspiring
    February 16, 2009
    If you are looking to select two traits, you need two traits in your select clause. I don't see that in your query.

    By the way, does that query actually run? You have more items in the top half of your union query than in the bottom.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices