Returning Null Value for 0 row results

[pbellem]

Is there a way to write a select that will return a null value for a row that does not exist?

for example:

Table A

productId - type id - name - value

1 - 1 - Title - "Great Song"
1 - 2 - syntax - verb
1 - 3 - country - USA
1- 4 - state - Michigan
2 - 1- Title - "I don't have a country"
2 - 2 - syntax - noun

I want to have a select that returns
Product ID - Title - Syntax - Country - State
1 - Title - "Great Song"
1 - syntax - verb
1 - country - USA
1 - state - Michigan
2 - Title - "I don't have a country"
2 - syntax - noun
2 - syntax - NULL
2 - country - NULL


For product ID 2, the country and state should still exist but be null in the results.

Thanks

[magicwand]

pbellem,

I imply, that the line

2 - syntax - NULL

should read

2-state-NULL

(otherwise I understood something wrong)

This is a perfect example, how a wrong data model leads to trouble.

You ignored the first and simplest rule in database design: NORMALIZE !!
(by the way: second and third are: NORMALIZE!!!)

You will never get scalable and performing applications (on what relational database ever) using such bizarr designs

You need - of course - 2 Tables:

Table A:
prodID,typeID,v alue

and table B:
typeID,name


And suddenly, everything is so easy with an outer join ...

[pbellem]

pbellem,

I imply, that the line

2 - syntax - NULL

should read

2-state-NULL

(otherwise I understood something wrong)

This is a perfect example, how a wrong data model leads to trouble.

You ignored the first and simplest rule in database design: NORMALIZE !!
(by the way: second and third are: NORMALIZE!!!)

You will never get scalable and performing applications (on what relational database ever) using such bizarr designs

You need - of course - 2 Tables:

Table A:
prodID,typeID,v alue

and table B:
typeID,name


And suddenly, everything is so easy with an outer join ...

Yes the the line should state 2 - state - null. My mistake.

And I agree with the design is faulty, but again this is a database that was implemented after many people saying not to do it this way, but they did. I now have the task to write the application to do this, and have done the outer join, just wondering if there was an easier way, as right now I have to join 7 tables since the actual database is much bigger with more fields. The above example is just a simple made up table.

[amitpatel66]

@pbellem,

Can you please post what you are trying and we can have a look?