joining two queries

[ratcateme]

i have been coding in php/mysql for a while but stayed with very basic SELECT INSERT UPDATE DELETE

basically at the moment i want to do this

i have two tables

leaves(ID,
branchID,
type,
typeTableID)
and
types(ID,
table)

each has some more fields but they are dont matter here

leaves.type corresponds to a ID in types

basically i have a ID for leaves and i want to use it to get leaves.branchID, leaves.typeTableID and types.table

i tried

SELECT `leaves.branchID`, `types.table`, `leaves.typeID` FROM `types`, `leaves` WHERE `leaves.ID` = $id AND `leaves.type` = `types.ID`  LIMIT 1

and got

Failed. Error: Unknown column 'leaves.branchID' in 'field list'

is this possible and if so how?

 

Scott.

[Phoenix~Fire]

`leaves.branchID`  needs to be `leaves`.`branchID`

 

 

[fenway]

Get rid of those evil backticks.

[killah]

Get rid of those evil backticks.

 

Is there anything wrong with (` & ´) ?

[corbin]

1.  Unneeded, hence they take up space.

2.  If they are needed, the object should be renamed since it's a reserved word.

3.  They are native only to MySQL's SQL dialect, therefore, they can break compatibility.  (For example, in MSSQL, [] are used.)

[ratcateme]

thanks corbin ill start to go without them

 

ok let me explain a bit further i am trying to remove a row from types.table and leaves at the moment i have it done with 3 queries

the first one to get types.table the second to delete from types.table and the third to delete from leaves

$row = get_row("SELECT leaves.branchID AS branchID, types.table AS table, leaves.typeID AS typeID FROM types, leaves WHERE leaves.ID = $id AND leaves.type = types.ID  LIMIT 1");
query("DELETE FROM {$row['table']} WHERE ID = {$row['typeID']} LIMIT 1");
query("DELETE FROM leaves WHERE ID = $id LIMIT 1");

(get_row() is a function that handles errors and returns mysql_fetch_assoc(). query() handles errors and returns the result object)

is there a way i could join all there queries to make one?

 

Scott.

 

[corbin]

Well, you can use JOIN in DELETE statements, so you could possibly do that.

 

 

Example:

 

DELETE FROM table1 JOIN table2 ON table2.somecol = table1.somecol WHERE table2.someothercol = 'something';

 

 

That would delete only from table1 even though it takes conditions from table2.  It's possible to delete from multiple tables with JOINs, but I don't remember the syntax.

 

 

 

Ahhh....  http://dev.mysql.com/doc/refman/5.0/en/delete.html Look towards the end of the article (not the comments).

 

 

Edit:  Hrmmm, actually, maybe it's not possible to delete from more than 1 table at once.

[ratcateme]

also the one of table names has to come from the first query don't know if that is possible i didn't find any examples when i googled it

 

Scott.