MySQL variables

Question

I'm currently in the process of learning MySQL, and I've come to learning variables, however I'm having an issue accessing whats saved in my variable; I'm getting my variable contains with the following command:

SELECT 
    @foods := `idFood`, `price`
from
    `foods`;

This seems to be working okay, and this is resulting a table, now I'm wanting to sort my table animals by the following command:

Select 
    *
From
    `Animals`
order by `Animals`.`Food consumption` * @foods.price
where @foods.idFood = `Animals`.`FoodType`

The Animals.FoodType is holding the id of the food, that the animal is consuming.

When running this, I'm getting an error in the last part, where I'm using the variable, I've been playing around with it for quite a while without any luck.

if you're getting an error, posting the error-message would be nice ;) — oezi
– oezi, Commented Aug 24, 2011 at 10:26

Devart · Accepted Answer · 2011-08-24 10:37:57Z

1

You should use one query with JOIN clause. Try this query -

SELECT a.* FROM animals a
LEFT JOIN foods f
  ON a.FoodType = f.idFood
ORDER BY a.`Food consumption` * f.price;

About the variables - variables in MySQL are scalar values, you cannot set an array into variable.

answered Aug 24, 2011 at 10:37

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Skeen Over a year ago

How does this function exactly, it selects an animal 'a', and a food 'f' and simply compare them?

Devart Over a year ago

The JOIN clause is used to extract data from two related tables. In your case tables are related by food id. To view full result set use 'SELECT * FROM ...' instead of 'SELECT a.* FROM...'.

Skeen Over a year ago

Yea okay, I think I get it now, thanks, this actually just changed to way I work with SQL

ypercubeᵀᴹ Over a year ago

@Skeen: I think you should try improving your SQL skills, avoiding variables until you hit problems that (MySQL) can't deal easily or efficiently without them.

Collectives™ on Stack Overflow

MySQL variables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related