1. Hi Guest: Welcome to TRIBE, Toronto's largest and longest running online community. If you'd like to post here, or reply to existing posts on TRIBE, you first have to register on the forum. You can register with your facebook ID or with an email address. Join us!

anyone know anything about SQL

Discussion in 'TRIBE Main Forum' started by Bass-Invader, Mar 6, 2002.

  1. Bass-Invader

    Bass-Invader TRIBE Member

    i probably shouldnt bother bringing this here..but im registered and i need an answer ...stat..

    i have a table

    userID | item1 | item2 | item3 | item4


    how would i perform a query on it such that all the items are in 1 column(userIDs with multiple items would just appear twice..
    eg:

    steve | gold | silver
    rob | axes | chicken

    would query to

    steve | gold
    steve | silver
    rob | axes
    rob | chicken


    is this even possible?
    or is there a better way to make a table to represent an ID having 1 to many items in a table?
     
  2. pr0nstar

    pr0nstar TRIBE Member

    SELECT cheating FROM Tribe WITHIN *Morals
     
  3. Bass-Invader

    Bass-Invader TRIBE Member

    if cheating = help, then sure.

    this isnt an explicit question from an assignment or something, its something i need to know to further my path to solution of another problem, .
     
  4. JayBrain

    JayBrain TRIBE Member

    Using one to many.. I usually create two tables.. and having the primary key of one of them as a foreign key in the main table.

    Table1
    --------------
    UserID (Primary)
    DescriptionID (Foreign) & (Secondary Key)


    Table2
    -------------
    DescriptionID (Primary)
    Description


    using a statement like this may help.. not sure what you're looking for though..

    select table1.userID, table2.description from table1, table2
    where table1.descriptionID = table2.descriptionID
    Group By table1.userID
     
  5. Bass-Invader

    Bass-Invader TRIBE Member

    okay, sorry, ill try and explain better.

    I am using two tables,

    i have a table Orders( the | is a column spacer)

    userID | item1 | item2 | item3 | item4

    basically, a user can have one, or four items of various types. the item columns store itemIDs, or null.

    the second table has

    itemID | price

    i want to take the entire Orders table, and create a query that will result in:
    userID | totalPriceOwed

    to do this however, i first need to amalgamate the item1, item2, item3, and item4 columns into one column, which i can then just join with the item table and sum for any given userID.

    leaving me with the question
    how do i take all the item columns and throw them into one column(as in the first example)
     
  6. JayBrain

    JayBrain TRIBE Member

    select userID, sum(item1, item2, item3, item4) as " 'totalPriceOwed' " from Table1

    Actually.. you might use " + " instread of ", " not sure.. but the sum command may work


    ... hmm.. give me a minute.. I not right.. let me figure this out
     
    Last edited: Mar 6, 2002
  7. Bass-Invader

    Bass-Invader TRIBE Member

    oh..and if the user has 2 of the same item, then it's itemID is entered into his row twice

    steve | slingshot | slingshot | bariumenema

    which would represent someone named steve, who has an order for 2 slingshots and a barium enema.
     
  8. Bass-Invader

    Bass-Invader TRIBE Member

    well, the thing is, the itemID's are in the first table, not the prices, so i cannot sum them, i just tried

    select
    ADMINISTRATOR.ORDERS.ITEM1 AS item,
    ADMINISTRATOR.ORDERS.ITEM2 AS item,
    ADMINISTRATOR.ORDERS.ITEM3 AS item
    from
    ADMINISTRATOR.ORDERS

    which still yield 3 columns, ignoring the fact that I told db2 to return them all as the item column.

    it gives me a syntax error if i use + instead of ,

    i wish this version of sql allowed for querying queryies ala

    select * from (select userID, item1 as item from orders) AND (select userID, item2 as item from orders)...etc
    actually..that would probably give me the same problem
     
  9. MalGlo

    MalGlo TRIBE Member

    WTF is a barium enema!
     
  10. Bass-Invader

    Bass-Invader TRIBE Member

    thanks for trying btw :)
     
  11. SlipperyPete

    SlipperyPete TRIBE Member

    some kind of anal nitrate I'm sure
     
  12. JayBrain

    JayBrain TRIBE Member

    ok.. heres the issue.. the way you have your DB set up is hard to work with.. the proper way to set it up would be to have multiple tables that are connected though foreign keys..

    At least 3 Tables..

    1 = customer
    - UserID (P)
    -CustomerName
    -CustomerInfo

    2 = ItemData
    -ItemID (P)
    -Description
    -Price
    -QtyOnHand

    3 = Orders
    - OrderNumber (P)
    - UserID (F)
    - ItemID (F)
    - QuantityOrdered

    Maybe 4 if you use an Order Items.. explaining shipping details

    Customers make 1 to many orders. Oders can contain 1 many items. Customers can order 0 to many of the same item. and the items are not limited to 4
     
  13. JayBrain

    JayBrain TRIBE Member

    Actually.. I use sybase sql.. and the user guide that is part of the help section has many helpfull hints.. but Sybase at school right now and I left my SQL book in my locker.
     
  14. Bass-Invader

    Bass-Invader TRIBE Member

    ahhh, fuck, you're right... bad design on my part!!
    I kept shying away from giving items their own table because the items im dealing with are..heh..pizza toppings...
    they seem so trivial i didnt want to give them their own table..

    but you're right.. im going to have to do it that way.

    Thanks alot man, I appreciate it!
     
  15. JayBrain

    JayBrain TRIBE Member

    lol.. I just cought that.. maybe it's some new shot you can get at system
     
  16. Bass-Invader

    Bass-Invader TRIBE Member

    yes!

    it would be more efficient to create the userID itemID quantity table also...since it would reduce the number of queries needed for every transaction.

    nice!
     
  17. JayBrain

    JayBrain TRIBE Member

    cool.. well good luck.. hehe..

    I'm all EsQue-Ellled out!
    I handed in 2 Assignments today in that course.. and damn it stressed me out.. now I have to create a C++ program using dynamic link lists and ferhin fractions and it's looking like a nightmare
     
  18. Bass-Invader

    Bass-Invader TRIBE Member

    linked lists are fun.

    i don't know what a fehrin fraction is though..
     
  19. JayBrain

    JayBrain TRIBE Member

    Neither Do I!!!!!!!

    lol


    It's due Monday
     
  20. Bass-Invader

    Bass-Invader TRIBE Member

    im right there with you in my AI class...
    good luck on that, im off to see coxy

    peas
     

Share This Page