[SOLVED] Query help - populate 1 table from multiple others?

[priory]

Hi,

 

I'm new to php/mysql (two weeks in ). I have access to a bunch of tables that store temp data, I want to be able to make a permanent storage of some of the data based on http posts from a windows app. The tables are structured like:

 

user_lookup[AvatarName, AvatarKey]

basket_lookup[AvatarKey, BasketKey]

item_lookup[itemName, ItemKey]

temp_basket[basketKey, ItemKey]

 

I would like a table that looks like:

 

backup[AvatarName, BasketKey, ItemName]

 

At the moment I have no trouble with simple queries such as deriving the BasketKey for a given AvatarName etc. But I am having trouble trying to express the following as one query:

 

For this AvatarName (stored in $avatarname), find the associated BasketKey and all ItemName's associated with that BasketKey . Then add ALL the fields to table backup. I tried using a join (which I think it the correct method) but it seems to just mess up. This has been hurting my brain for a while now so any hints would be appreciated.

 

[Mchl]

Try:

 

INSERT INTO `backup` (AvatarName,BasketKey,ItemName) VALUES (
  SELECT ul.AvatarName,bl.BasketKey,il.ItemName FROM
  user_lookup AS ul 
  CROSS JOIN
  basket_lookup AS bl USING (AvatarKey)
  CROSS JOIN
  temp_basket USING (BasketKey)
  CROSS JOIN
  item_lookup AS il USING (ItemKey)
  WHERE
  ul.AvatarName = $avatarname
)

[priory]

thanks for your help, I wasn't aware of CROSS JOIN. I tried out your suggestion but for some reason it is throwing up syntax errors. When I use it from a php script mysql_query() I don't get any feedback but trying the SQL directly on the database through phpMyAd throws up syntax errors.

[priory]

modify wouldn't work above for some reason :/ error handling in my php script now returns:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ul.AvatarName, il.ItemName, bl.BasketKey FROM user_lookup AS ul CROSS JOI' at line 1

 

I am on 5.0.67 btw.

[Mchl]

Ok... seems I got it wrong

See: http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

 

 

INSERT INTO `backup` (AvatarName,BasketKey,ItemName) 
  SELECT ul.AvatarName,bl.BasketKey,il.ItemName FROM
  user_lookup AS ul
  CROSS JOIN
  basket_lookup AS bl USING (AvatarKey)
  CROSS JOIN
  temp_basket USING (BasketKey)
  CROSS JOIN
  item_lookup AS il USING (ItemKey)
  WHERE
  ul.AvatarName = $avatarname

[priory]

thank you so much. you've saved me from trawling through the reference manual