select statement from multiple tables

[littledragon]

Hi guys, have been googling this for an hour now, not getting anywhere, maybe am getting tired...

 

I have three tables (files, media and content), mostly different except all have the columns id, par, basename, & path

 

I need a single statement where I can select id, basename, path FROM  all three tables  WHERE par = 'a_value',  ORDER BY basename

 

??? any ideas?  _Much_ appreciated!

 

ld

[DyslexicDog]

Read about JOIN.

 

google "mysql join" or "mysql left join"

[otuatail]

SELECT files.id, files.basename, files.path,  media.id, media.basename, media.path, content.id,

content.basename, content.path

FROM files INNER JOIN media  ON files.par =  media.par

INNER JOIN content  ON WHERE media.par =  content.par

WHERE content.par = a_value',  ORDER BY basename

[otuatail]

SORRY

 

SELECT files.id, files.basename, files.path,  media.id, media.basename, media.path, content.id,

content.basename, content.path

FROM files INNER JOIN media  ON files.par =  media.par

INNER JOIN content  ON  media.par =  content.par

WHERE content.par = a_value',  ORDER BY basename

[littledragon]

Gnaaagh! That looked so good...

 

error: Column 'basename' in order clause is ambiguous

 

This is my main problem... I need them to be ordered as per 1 column

 

I read about join, lots of examples that don't apply.  still reading though

[littledragon]

oops

[BlueSkyIS]

you'll also need to add a single-quote before a_value and remove the comma before ORDER BY

[littledragon]

Yeah, thanks I did that already

[littledragon]

Hmmm... and if I remove the ORDER BY clause I get NULL (all tables have par = a_value at least once)

[Pickle]

yeah you need to specify which table in the order clause as well like ORDER BY files.basename or which ever table you want it to be ordered from

[littledragon]

Yeah, but I want it to be ordered by basename across all tables, so some kind of join is needed at basename and then order by it - I just can't find how to do this

 

In any case, if I remove the order clause in the above example I get a NULL result, even though a simple select on any one of the tables yields results for par = a_value

 

Let me know if my problem is still ambiguous

[Pickle]

is it imperative that you have one single statement? can you not do this in nested statements? you would have more control over what its going on. i know it would be better coding practice to write a join however if youre not getting out of it what you need then maybe you should consider a different way of doing it.

[littledragon]

I agree, overheads are not really an issue on this site, but it seems silly to go pulling apart and reassembling the arrays to get them all organised by the one field (cos if I do three seperate queries then I get three *seperate* nicely organised arrays rather than one) when I know mysql is capable of doing it for me

 

And also, I'd like to know how to do it

[Pickle]

ok thats a fair enough point and a good enough reason to persist with one statement.

 

is it not possible to say ORDER BY files.basename, media.basename, content.basename so that it would first order by the files.basename and then within that order by media.basename etc etc.

 

just a thought

[littledragon]

Sorry to do this but amn't getting much further with rtfm...

 

*bump*