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
