Page 1 of 1

Need help with a mysql query, selecting multiple tables.

Posted: Thu Dec 31, 2009 9:28 am
by granite
Hello there, dudes.

I have a problem, to which I have no solution.
I have four different tables and need to retrive data from all of them at once.

To the tables:
Announcements, from which I need all the data;
Categories, from which I need to grab the announcement's category title;
Subcategories, from which I need to grab the announcement's subcategory title;
Regions, from which I need the announcement's region name;

Obviously, the Announcement table has "id_category","id_subcategory" and "id_region" fields.
So, I guess the query'd be something like:

Code: Select all

SELECT Announcements.*,Categories.title,Subcategories.title,Regions.name 
FROM Announcements,Categories,Subcategories,Regions 
WHERE Announcements.state='something' AND Categories.id=Announcements.id_category AND Subcategories.id=Announcements.id_subcategory AND Regions.id=Announcements.id_region
The problem is here, in the where clause. I don't know how to do it right or even if it'll be possible to do this.

What do you guys think? Is it possible? Can anybody enlighten me?

Thanks in advance.

Re: Need help with a mysql query, selecting multiple tables.

Posted: Thu Dec 31, 2009 10:05 am
by AbraCadaver
Your query does look odd but may work (try it). Obviously untested, but something like this may work for what you want and may be easier to read/understand:

Code: Select all

SELECT Announcements.*, Categories.title, Subcategories.title, Regions.name
FROM Announcements
LEFT JOIN Categories ON Categories.id = Announcements.id_category
LEFT JOIN Subcategories ON Subcategories.id = Announcements.id_subcategory
LEFT JOIN Regions ON Regions.id = Announcements.id_region
WHERE Announcements.state = 'something'

Re: Need help with a mysql query, selecting multiple tables.

Posted: Fri Jan 01, 2010 4:04 pm
by granite
Oh, thanks AbraCadaver!
Actually mine works. I hadn't tested it. :oops: :banghead:
However, I'm using yours, just cause it looks better. :)

Re: Need help with a mysql query, selecting multiple tables.

Posted: Fri Jan 01, 2010 4:25 pm
by granite
Oh... Now I'm with a problem in another query...
What I want now is to get the categories' title and all the subcategories related to them in just one query.
In order to get the subcategories, I could access the database for each category returned in a first query, but that would take a lot of resources, so...

here's is what I'm was trying (tested the query this time and it didn't work, returning the error "#1241 - Operand should contain 1 column(s)"):

Code: Select all

SELECT Categories.title, (
select id, title
from Subcategories
where Subcategories.id_category = Categories.id
) AS subcategory
FROM Categories
WHERE Categories.status = 'a'
Any help would be much appreciated. Thanks.

Re: Need help with a mysql query, selecting multiple tables.

Posted: Fri Jan 01, 2010 4:59 pm
by Eran
You can't select multiple columns to alias to one column (in your case id, title => subcategories). It's not clear what you were trying to achieve, but probably joining the tables is what you're after.
Perhaps something like:
[sql]SELECT Categories.title, Subcategories.id,Subcategories.title FROM CategoriesINNER JOIN Subcategories ON Subcategories.id_category = Categories.idWHERE Categories.STATUS = 'a'[/sql]

Re: Need help with a mysql query, selecting multiple tables.

Posted: Fri Jan 01, 2010 6:24 pm
by granite
Well, thanks for the answer, but that won't work in this case.
As I said, I want to get the categories' titles and all the subcategories related to each of these categories.

For example, I have the categories "animals" and "cars".
"animals" has, let's say, three subcategories:
"dogs", "cats", "fishes";
while "cars" has two:
"bmw", "ford".

Now with the query, I want to retrive "animals" and "cars", as well as their subcategories, but these must be within the categories' results, something that I can access in php with:
$result['title'], $result['id'] for the category's data, and $result['subcategories']['id'], $result['subcategories']['title'] for the category's subcategories' data. Do you think it is possible?

Re: Need help with a mysql query, selecting multiple tables.

Posted: Fri Jan 01, 2010 6:35 pm
by Eran
A MySQL data can only be returned in tabular format (ie, rows and cells). You can't have more dimensions than that. The query I suggested will return all the information you need - you just have to iterate through it with PHP and arrange in the manner you suggested. I just noticed you might need to perform a slight change to the query I suggested, since you can't have the same column appearing twice -
[sql]Subcategories.title AS subcat_title[/sql]
Or an alias of your choosing.

Re: Need help with a mysql query, selecting multiple tables.

Posted: Wed Jan 06, 2010 2:24 pm
by granite
Thank you very much, dude.
I found a way to do what I wanted with php. ;)