Simple SQL code eludes me. Demote two inconsistent tables
I select 1 field from 1 table and store it in temp table.
Sometimes this table ends with 0 lines.
I want to add this field to another table with 20+ fields
Normal union won't work for me due to # field mismatch. The outer habit works for me because there is nothing to compare. NVL does not work on the first temporary table.
Does anyone know how to do this?
UPDATED:
I didn't mention .... When a table that retrieves 1 field finds a match in other cases, this code I'm using now works ...
SELECT DISTINCT reqhead_rec.resp_name<br>
FROM reqhead_rec, biglist<br>
WHERE reqhead_rec.req_no = biglist.req_no
AND reqhead_rec.frm = biglist.req_frm<br>
INTO TEMP grabname with no log;
SELECT biglist.*, grabname.resp_name<br>
FROM biglist, grabname<br>
ORDER BY prnt_item, account_amt<br>
INTO TEMP xxx with no log;
source to share
What field will it match? By the way, here's how to line them up:
SELECT NULL, NULL, NULL, NULL, MySingleField, NULL, NULL, NULL... FROM #temp
UNION ALL
SELECT Col1, Col2, Col3, Col4, Col5, Col6,... FROM OtherTable
UPDATE:
OK, after reading your update ... I don't think you want UNION at all, but rather, and incredibly simple SUBSELECT
SELECT
*,
(SELECT TOP 1 Name FROM Blah WHERE Blah.SomeID = MyTable.SomeID) AS ExtraCol
FROM
MyTable
source to share
It sounds like you want to join, not an alliance.
You don't need to compare anything to make a connection. You end up with a cross product if you don't specify the join conditions:
SELECT t20.*, t1.*
FROM table_with_20_columns AS t20
LEFT OUTER JOIN temp_table_with_1_column AS t1 ON (1=1);
When temp has null rows, it will be declared NULL as a result of the above query.
However, if there are multiple rows in the temp table, you will get a cross product with the first table. I cannot tell from your question what you want.
edit: The join condition, expressed in the clause ON
or USING
, should be optional according to the SQL standard, but at least when I test it in MySQL 5.0, it's a syntax error to omit this article. But you can use ON (1=1)
.
edit: Answering your question in the comment:
SELECT COALESCE(reqhead_rec.resp_name, dflt.resp_name) AS resp_name
FROM (SELECT 'default name' AS resp_name) dflt
LEFT OUTER JOIN reqhead_rec ON (1=1)
WHERE reqhead_rec.req_no = biglist.req_no AND reqhead_rec.frm = biglist.req_frm
INTO TEMP grabname WITH NO LOG;
Actually, you can skip the temp table altogether. Just JOIN your main table before reahead_rec
. Put these conditions in the ON
join offer , not the offer WHERE
. Then use COALESCE()
this query in the select list to specify the default name if it is not found in another table.
SELECT b.*, COALESCE(r.resp_name, 'default name') AS resp_name
FROM biglist AS b
LEFT OUTER JOIN reqhead_rec AS r
ON (b.req_no = r.req_no AND r.frm = b.req_frm)
INTO TEMP xxx WITH NO LOG;
source to share