Clever Geek Handbook

SELECT by reference with number

I have a relationship between two tables, authors and styles. Each author is associated with a style, with a special case where the author has no style (IS NULL).

There is no problem setting the reference to NULL, but there is a problem with the query to select authors and styles.

For example, the request:

SELECT "authors"."id", "authors"."name", "styles"."name", "authors"."comments" FROM 
"authors" , "styles" WHERE "authors"."style" = "styles"."id"

just ignores authors with NULL style (as expected).

I need to make a selection that also displays NULL-styled authors, such as a left join (I can't use a LEFT JOIN for some reason).

Is there a solution that doesn't include explicit joins?

0


source to share


4 answers


The most obvious solution is the LEFT OUTER JOIN.

See: http://www.postgresql.org/docs/8.1/static/tutorial-join.html



If you don't want to use explicit joins you should use UNION

SELECT "authors"."id", "authors"."name", "styles"."name", "authors"."comments" FROM 
"authors" , "styles" WHERE "authors"."style" = "styles"."id"
UNION
SELECT "authors"."id", "authors"."name", "", "authors"."comments" FROM 
"authors" WHERE "authors"."style" IS NULL
+4


source


I think that if you cannot use LEFT JOIN then you should use UNION.

Check out the link from Coding Horror, it's pretty interesting. Visual explanation of SQL connections



+1


source 


SELECT "authors"."id", "authors"."name", "styles"."name", "authors"."comments" FROM    "authors" , "styles" WHERE "authors"."style" = "styles"."id" OR "authors"."style" = null

Have you tried this?

0


source


In my opinion, you just need to expand your query to include NULL:

SELECT "authors"."id", "authors"."name", "styles"."name", "authors"."comments" 
FROM "authors" , "styles" 
WHERE "authors"."style" = "styles"."id" OR "authors"."style" IS NULL
0


source






More articles:


All Articles