Select constant/null values in PS Query 1

[marc79]

Hi,
I'm not very familiar with PS Query yet. I was wondering if it's possible to select constants/null values from a table? The reason i'm asking is because my query needs to look something like this:

SELECT A.COL1, A.COL2, A.COL3, A.COL4
FROM TABLE A, TABLE B
WHERE...
UNION
SELECT '','','',C.COL4
FROM TABLE C
WHERE...

I was able to join the two select statements with a UNION, but it seems like in the 2nd Select, i'm limited to only fields from Table C. How can i select constants/null values?
Any suggestions are appreciated. Thanks.

 

[Rofeu]

Hi,

with a UNION you basically stick the results of 2 SEPERATE queries into one recordset. The 2 select statement don't communicate with eachother, sort to speak.

To get only NULL values, you can use the operator IS NULL in your WHERE clause.

From your post, I don't think that you're looking for a UNION though. I think you problem is with JOINING the tables.

Please post back with what exactly you're trying to get.

Cheers,

Roel

 

[marc79]

Thanks for your reply. Actually the reason why i wanted to use the Union is because i need COL4 populated even if the 1st Select doesn't return any records. It's not very pretty I know but that's the best i could think of since i needed a quick fix.

 

[notadba]

Why not use an outer join rather than a union?

 

[Rofeu]

I might be missing something here, but if you have zero records, where would you place a value for Col4??

Is table C the same as table A?

How do table A, B and C relate to eachother?

Also, you show no fields for table B. Will this not be better put in a subquery?

Is this what you're intending to do:

SELECT A.COL1, A.COL2, A.COL3, A.COL4
FROM TABLE A
WHERE EXISTS (SELECT 'x' FROM TABLE B WHERE A.FIELD = B.FIELD)
UNION
SELECT '','','',C.COL4
FROM TABLE C
WHERE NOT EXISTS (SELECT 'x' FROM TABLE D WHERE C.FIELD = D.FIELD)

Note: TABLE B and TABLE D are the same table!

Cheers,

Roel