thanks a lot for your posting, [cut] Posted by Fred Mitchell on December 11 2004 2:47pm [Delete] [Edit] Let's say you are doing a LEFT JOIN with a table that shares a column name in common with another table, and that you are selecting for instances where the join is missing, that is IS NULL. Normally, the common column name is "wiped out" by the null record, but here is a workaround for it: You simply alias that common column name in the select. For instance, CREATE TABLE t1 (INT id NOT NULL, ....); CREATE TABLE t2 (INT id NOT NULL, ....); ... SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL; would result in the column 'id' being null on each selected row. Instead, you can do: SELECT *, t1.id AS id FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL; And now the 'id' column will be preserved since the alias is evaluated *after* the LEFT JOIN. [/cut] pay attention to: -> *, t1.id <- cheers, cajbecu Jay Blanchard wrote: > [snip] > $sql = "SELECT * FROM `table1` LEFT JOIN `table2` USING `id` WHERE ... > GROUP BY `table1`.`id`"; > > mysql_query($sql); > > the problem is, that, when in table2 is not matching data using that id, > i lose that id from output array. but i don`t want to.. is there any > posibility to keep that id? > [/snip] > > More of a mysql question but do a left outer join with no group by > statement. > > SELECT table2.* > FROM table1 LEFT OUTER JOIN table2 > ON(table1.id = table2.id) > WHERE table1.id IS NULL > > > Returns all of table2 id's where there is no id in table1 > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
