• Hi Guest: Welcome to TRIBE, the online home of TRIBE MAGAZINE. If you'd like to post here, or reply to existing posts on TRIBE, you first have to register. Join us!

anyone know anything about SQL

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?
 
Alex D. from TRIBE on Utility Room
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, .
 
J

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

Bass-Invader

TRIBE Member
Originally posted by JayBrain

using a statement like this may help.. not sure what you're looking for though..
[/B]
Click to expand...
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)
 
tribe cannabis accessories silver grinders
J

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:
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.
 
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
 
tribe cannabis accessories silver grinders
J

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
 
J

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.
 
Bass-Invader

Bass-Invader

TRIBE Member
Originally posted by JayBrain
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
Click to expand...

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!
 
tribe cannabis accessories silver grinders
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!
 
J

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
 
tribe cannabis accessories silver grinders
You must log in or register to reply here.
Top