$result = mysql_query("select TABLE1.name, TABLE2.show, TABLE2.active, TABLE2.notes from TABLE1, TABLE2 where TABLE1.name = TABLE2.name and (TABLE1.status = 1 or TABLE1.status = 3) order by TABLE1.name", $db);
infolock wrote:micro, that says to do the same thing i posted, except you are ordering by the names found in table 1
No.
1) By bracketing the OR clause, the query will do what he wants. Before, the AND would have taken precidence, and that's not what he wanted.
2) I don't believe you can use "||" to mean OR in WHERE clauses.
3) Not stating TABLE1 in the order by clause may have caused MySQL to spit up an ambiguity error. I have more experience with MS SQL than MySQL and I know that in MS SQL, it would have errored. Not sure about MySQL.
1) By bracketing the OR clause, the query will do what he wants. Before, the AND would have taken precidence, and that's not what he wanted.
2) I don't believe you can use "||" to mean OR in WHERE clauses.
3) Not stating TABLE1 in the order by clause may have caused MySQL to spit up an ambiguity error. I have more experience with MS SQL than MySQL and I know that in MS SQL, it would have errored. Not sure about MySQL
|| is the same as or i tried this out on my own table in mysql, and it worked the same both ways.
you are right by if you select both tables, it will give an ambiguity error if you don't specify the table1.name, but you could remove table1.* and it would work fine with just name
edit ::
MySQL Language Reference
OR
||
Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned.
$result = mysql_query("select TABLE2.* from TABLE2, TABLE1 where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by name", $db) or die(MySQL_Error());
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where TABLE1.name = TABLE2.name and TABLE1.status =
$result = mysql_query("select TABLE1.*, TABLE2.* from TABLE2, TABLE1 where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by TABLE2.name", $db) or die(MySQL_Error());
$result = mysql_query("select TABLE1.name, TABLE2.name, TABLE2.show, TABLE2.active, TABLE2.notes from TABLE2, TABLE1 where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by TABLE2.name", $db) or die(MySQL_Error());
select
TABLE1.name,
TABLE2.name,
TABLE2.show,
TABLE2.active,
TABLE2.notes
from
TABLE1
inner join TABLE2 on TABLE1.name = TABLE2.name
where
TABLE1.status = '1' or TABLE1.status = '3'
order by
TABLE2.name