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

[Guest]

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!

[Dan_Bracuk]

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.

[Dan_Bracuk]

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.

[Dan_Bracuk]

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.

[Guest]

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.

[Kronin555]

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

[Dan_Bracuk]

quote:

---

Originally posted by: C-Rock
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.

---

You only did part of it. You didn't give the two instances of the materials table different alias names. Look at Kronin's code.

[Guest]

Kronin's code shows two different material tables, that's easy. However there's only one material table.

[Dan_Bracuk]

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.

[Guest]

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.

[Kronin555]

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

[Kronin555]

On a sidenote, if you had used a mapping table for these bag traits, you wouldn't have had this problem to begin with.

[Guest]

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?

[Guest]

Doesn't work, same results. Here is my code...

[Kronin555]

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>

[Kronin555]

quote:

---

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?

---

Yes. In this case, you'd have your bag_orders table, your materials table, and a bag_orders_to_materials table.

Remove the trait_a and trait_b from your bag_orders table.

Create your bag_orders_to_materials table as:
bag_orders_to_materials
order_id foreign key references bag_orders(id)
material_id foreign key references materials(id)
trait_type character (here you can store whether it's trait a or trait b, if that information is important. If not, you can remove this column).

[Guest]

I wish I understood that mapping. It doesn't make sense to me. I'm not advanced enough to know about Foreign Keys and the references. I don't see how removing the traits from the order table would allow me to store the selected trait by the user for the ordered bag. Making the trait_a column reference the materials.material_id column would make more sense. So selecting the trait_a value would return anything from the materials table based on the value in the trait_a column.

A user has 7000 different materials they could choose for traits a through g. The bag that was ordered table stores the selected trait material id along with many other things like cost, shipping and bag style that was ordered. I'm trying to just return the infomation so that it can be printed out for the own so they know what was ordered.

[Guest]

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.

[Kronin555]

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.

[Guest]

I have some screen shots. I commented out the other items that don't have any affect on the result focusing on just the traits to make it easier. Also focus on the bag_orders order_id number 3. There's only one bag in this party order. Basically a party number is passed to the coldfusion query. In the example I'm focusing on the party passed is "2". So bag trait_a 5003 and b is 7035.

The output I'm getting when tracing in flash the result query trait_a and trait_b always turn out to bethe label in this case for material_id 5003 which is Black Cherry Heather.



SS1

SS2

Thanks

[Newsgroup_User]

normalize your db as Kronin555 has suggested.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Kronin555]

OK, Carey. I made a simplified version of your table structure and ran a simplified version of the query I posted. It works.

select * from bag_orders;
order_id | bag_trait_a | bag_trait_b
----------+-------------+-------------
1 | 1 | 2
2 | 2 | 3
3 | 1 | 3

select * from materials;
material_id | material_label
-------------+----------------
1 | Leather
2 | Suede
3 | Velour

select bag_orders.order_id, materials_a.material_label as bag_trait_a, materials_b.material_label as bag_trait_b 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);
order_id | bag_trait_a | bag_trait_b
----------+-------------+-------------
1 | Leather | Suede
3 | Leather | Velour
2 | Suede | Velour

Trying to run the query you posted in your screenshot, and my query I posted earlier, throws an error (in a proper database) because you're also doing a "group by bag_orders.order_id". Take that out, and it should work. I don't know why MySQL would actually return something rather than throwing an error...

Sorry I missed the "group by" clause earlier

[Guest]

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?

[Guest]

It's empty, just tried it again. It should bring the two rows back but doesn't. If the party value is 2 I get the one row, which is correct, but when party value is 1 I get no results.

[Newsgroup_User]

MySQL allows "intuitive/intelligent" grouping:
you do not need to specify ALL non-aggregate columns in the GROUP BY
clause, just need one column

i like this MySQL feature very much... but sometimes it throws all your
carefully created table indexes away and runs with a full table scan
instead... so on small tables it is fine, but on large ones it may cause
a serious performance problem...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/