SQL Help

[eluser]jwindhorst[/eluser]
Hoping someone out there can help me out with this SQL issue. I have two 3 tables in my db
tree_struct,
tree_nodes,
product_assets

tree_nodes has as asset_id field that refers to the id field of the products assets field. Tree nodes can be either type='folder' or type='file'. When tree_nodes.type='file' then tree_nodes.asset_id will be populated, otherwise it is null.

Now, I want to be able to join my product_assets, but when tree_nodes.asset_id is null, it just skips the row.

Code:

SELECT * FROM
    tree_struct, tree_nodes, product_assets
WHERE
    tree_struct.node_id = tree_nodes.id
AND
    tree_nodes.asset_id = product_assets.id

The last part of the where clause is necessary when there is an asset_id set, but causes the whole row to fail if it is not.

Please help, banging my head, and not to the music!

[eluser]kgill[/eluser]
You're skipping nulls because that's what you're asking the DB to do, only give me a row where this is equal to this, you want to use a left outer join instead, so that you get the rows that are equal or null.

[eluser]jwindhorst[/eluser]
Ok I'm an idiot. Sometimes I think that posting in here is all it takes to appease the Gods of code into showing me the light!

Yes, that is just a left outer join problem that I was attacking like a PHP developer instead of as a DB Admin.

Code:

SELECT
    tree_nodes.asset_id
FROM
    tree_nodes
LEFT OUTER JOIN
     product_assets
ON
     tree_nodes.asset_id=product_assets.id

Just in case someone elses brain is acting retired.

[eluser]jwindhorst[/eluser]
kgill, thanks I was apparently posting my reply at the same time as yours, and drawing the same conclusion. I think I was having trouble switching gears from PHP mode back in SQL mode.