Skip to main content
A
Anonymous
January 29, 2009
Question

If statement in the SELECT???

  • January 29, 2009
  • 5 replies
  • 430 views
I use Coldfusion as my source for remoting with Flash. I have created different databases for different materials. However when an order is placed I get the "id" of the material. I also return the "type" of material. If I would have planned this correctly I would have just put all the materials in one database and returned what I needed just fine. As it turns out I cannot change it.

Below is my code and basically what I'd like to do is say that:

if bag_traits.trait_type = 1
select materials.material_name where bag_orders.bag_trait_a = materials.material_id

So right now it's returning a 6. I need the name of the material from the materials database with an id of 6. I also have a ribbons table where if the trait_type = 2 then I would need the ribbons.ribbon_name where bag_orders.bag_trait_a = ribbons.ribbon_id.

How to I make an if or case statement that will pull the name from the appropriate database depending on the trait_type? Thanks!

    This topic has been closed for replies.

    5 replies

    N
    Newsgroup_User
    Inspiring
    January 29, 2009
    C-Rock wrote:
    > Sorry, removed a ")". works now. But I get the same results if I add the b
    > trait. If I create a new line for INNER JOIN and use materials a second time I
    > get an error. If I use an OR or AND statement I get the same results as before.
    >
    > how can I connect column "trait_a" and "trait_b" to materials.material_id?
    >

    I do not fully understand what you are trying to do here, but you can
    join a table to itself it get more advanced effects.

    You may also want to be using a UNION to combine to relations into one
    larger one.

    SELECT aField, bField
    FROM aTable INNER JOIN bTable ON atable.key1 = btable.key1

    UNION

    SELECT aField, bField
    FROM aTable INNER JOIN bTable ON atable.key2 = btable.key1

    A
    Anonymous
    January 29, 2009
    Sorry, removed a ")". works now. But I get the same results if I add the b trait. If I create a new line for INNER JOIN and use materials a second time I get an error. If I use an OR or AND statement I get the same results as before.

    how can I connect column "trait_a" and "trait_b" to materials.material_id?
    A
    Anonymous
    January 29, 2009
    This code didn't work. I'm getting an error just trying to return one trait.

    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 ON bag_orders.bag_trait_a=materials.material_id
    A
    Anonymous
    January 29, 2009
    I get that but what I'm saying it that I need to join one table/two different columns to two different tables.

    FROM tableA INNER JOIN tableB ON tableA.trait_a = tableB.id INNER JOIN tableA.trait_b=tableB.id

    Make sense? I have a "master table" that has several columns that stores id's from one table
    N
    Newsgroup_User
    Inspiring
    January 29, 2009
    C-Rock wrote:

    First I will reinstate that it sounds like you would have a much more
    maintainable application if you could re-factor the database design.

    But until that happy day, yes you can easily use <cfif...> conditional
    statements to build your SQL output. If you look at it as this, that
    SQL is just another type of output, little different then HTML that is
    delivered to a database instead of a browser. Then it might open a whole
    world to you.
    A
    Anonymous
    January 29, 2009
    I did end up rebuilding my database. So now I have just one materials databaes. Only problem now is that it's not allowing me to join the tables bag_orders.bag_trait_a to materials.material_id and bag_orders.bag_trait_b to materials.material_id?

    Why can I not use the material_id number in more than one column for the bag_orders table?

    My code is below, it's returning an empty set with this code and it works when I remove the joining for the b trait.


    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices