Need help with a mysql query, selecting multiple tables.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
granite
Forum Commoner
Posts: 44
Joined: Mon Feb 09, 2009 10:52 am

Need help with a mysql query, selecting multiple tables.

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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'
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
granite
Forum Commoner
Posts: 44
Joined: Mon Feb 09, 2009 10:52 am

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

Post 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. :)
granite
Forum Commoner
Posts: 44
Joined: Mon Feb 09, 2009 10:52 am

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

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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]
granite
Forum Commoner
Posts: 44
Joined: Mon Feb 09, 2009 10:52 am

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

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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.
granite
Forum Commoner
Posts: 44
Joined: Mon Feb 09, 2009 10:52 am

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

Post by granite »

Thank you very much, dude.
I found a way to do what I wanted with php. ;)
Post Reply