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

Is it possible I'm missing something set up correctly with the database?

Here is what I have:

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">)

Kronin, I'm not getting the right result in mySQL, in the query area so the CF result wouldn't be any different.

bag_orders.order_id,
materials_a.material_label as traitA,
materials_b.material_label as traitB

from bag_orders
join materials as materials_a on (bag_orders.bag_trait_a = materials_a.material_id)
join materials as materials_b on (bag_orders.bag_trait_b = materials_b.material_id)


If I use this code above it only returns the third order_id item. If I add this statement:
WHERE bag_orders.order_party IN(2) // this works fine as well because there is only one result.
WHERE bag_orders.order_party IN(1) // this is empty, no results.

Still inside mySQL query browser if I take the full amount of code, from the SS and I run the query I get the same results. However removing the b traits from the query it returns 2 items and if I remove a and b traits it returns all 3 items. This means that giving the alias to the materials isn't working for a result set of more than 1. Make sense?

By doing everything in mySQL query browser I can eliminate any other code problems. It's just not working correctly there.

Well, running that function with an order_party of 1 will return a query with 2 rows. In order to determine why your client isn't displaying those rows, we'll have to see the code in your client where you're calling this function.

If you call this function with a test ColdFusion page, passing it a order_party of 1, and cfdump the resulting query return value, I'd bet it comes back just fine (2 rows, one for each order_id). If not, if you've changed the query from what's in the screenshot, post that.

When I ran this code on my existing database it didn't return all three order id's from the database, just the third? Why?

quote:

---

Originally posted by: C-Rock
Kronin, Your post two up with the select statement changed doesn't work either, it still only brings back trait_a and does it twice, for a and b. I actually tried it when it didn't work before my last post. Then tried it again and it still doesn't work either.

---

Let me break it down for you:
<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>

These lines:
materials_a.material_label AS bag_trait_a,
materials_b.material_label AS bag_trait_b
select the label from the materials table that corresponds to these join statements (note the use of "materials_a" and "materials_b" instead of "materials". These are aliases):
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

Essentially, what this does is join your query to 2 copies of the Materials table. One of those copies, we're calling "materials_a" and joining on bag_trait_a, the other one we're calling "materials_b" and joining on bag_trait_b. Nowhere in your query should you be referencing "materials" directly, you reference using the alias names.

Post the exact query you're using now, along with what output you're getting and the output you're expecting to get. Double-check and make sure that the values stored in trait_a and trait_b in your orders table aren't identical. It sounds like, with what you say about the mapping table and it being confusing and how you have traits from a to g, that this query you're posting isn't exactly like what you're running that isn't working. It makes it very difficult to help you. Please post exactly what you're running, and I'll fix it for you.

Regarding the mapping table, you can do it how you want. Mapping tables are how most developers that know what they're doing will do it. It simplifies things and makes tasks like what you're trying to do here much easier.