Multiple tables at once

[Cloud9247]

I am trying to make a 'Sort By Series' page for my site, but in order to do that, I have to call from 4 different MySQL tables with one query, at least to make it simple xD Anyways, does anyone know how I could access multiple(4) DB tables with one query?

[akitchin]

simply list as many tables as you need in the FROM segment:

[code]SELECT table1.stuff, table2.something FROM table1, table2, table3, table4[/code]

[komquat]

What do you need from each table?

SELECT table1.info1, table2.info2, table3.info3, table4.info4
FROM table1, table2, table3, table4

This would select different information from all 4 tables

[Cloud9247]

this is my query:
[code]
$sql= "SELECT avatars.id, series, active, wallpapers.id, series, active, pngs.id, series, active, layouts.id, series, active FROM avatars, wallpapers, pngs, layouts WHERE  active='1' ORDER BY `series` ASC";
[/code]

But it doesn't seem to work, what is wrong with it?

[akitchin]

you have to label everything by table.field when you're selecting from multiple tables, otherwise SQL has no idea which table the field you're trying to pull is in.

[Cloud9247]

I'm sorry, I don't understand what you are saying exactly, could you show me in code?

[keeB]

Cloud..

[code=php:0]SELECT * from avatars, wallpapers, pngs, layouts where avatarts.active = 1 ORDER BY series.id ASC[/code]

Yes?

[akitchin]

which table is "active" in?  you need to be very specific with MySQL, otherwise your statement becomes ambiguous and impossible to accurately fetch.

in the interest of properly solving this (and leaving you with useful knowledge), what exactly are you trying to pull and from which tables?

[Cloud9247]

'active' is in ALL of the tables

And, I am trying to pull the series name from each table, but I want to make it where the series name will not repeat. I also want the series' to sort with eachother, so there won't be a boundry between the tables.

EX:

I want it to go like:
Series 1
Series 2
Series 3

NOT:
Series 1
Series 2
Series 1
Series 2
Series 1
Series 2
Series 1
Series 2

-------------
I still don't understand though, could someone please help me with this specific line?:

[code]
$sql= "SELECT avatars.id, series, active, wallpapers.id, series, active, pngs.id, series, active, layouts.id, series, active FROM avatars, wallpapers, pngs, layouts WHERE  active='1' ORDER BY `series` ASC";
[/code]

[akitchin]

the easiest way to do this is to go one table at a time, one query for each table:

[code]SELECT id, series, active FROM avatars WHERE active='1' ORDER BY series ASC[/code]

i don't see any reason for you to try and grab all of the info in one query, as that only complicates everything.  reserve your multiple table queries for queries that actually require the correlation of table values (ie. where one value is dependent upon a value from a different table).

nonetheless, for the record, your problem is that you're not enunciating where each of those fields are to come from.  put properly, your query should be:

[code]$sql= "SELECT avatars.id, avatars.series, avatars.active, wallpapers.id, wallpapers.series, wallpapers.active, pngs.id, pngs.series, pngs.active, layouts.id, layouts.series, layouts.active FROM avatars, wallpapers, pngs, layouts WHERE avatars.active='1' ORDER BY `series` ASC";[/code]

however, this query will not at all do what you want it to do.