October 12, 2005 at 9:16 am
I have a simple union I'm trying to do. I want the results to return only 1 row. col_a & col_b exist in both tables. However, col_c exists only in TABLE_A. If I perform the UNION w/ col_a & col_b I get 1 row. However, when I add col_c (see below) I get 2 rows. What am I doing wrong?
SELECT TABLE_A.col_a, TABLE_A.col_b, TABLE_A.col_c
FROM TABLE_A
UNION
SELECT TABLE_B.col_a, TABLE_B.col_b, '' col_c
FROM TABLE_B
October 12, 2005 at 9:21 am
Because the values of col_c in table a is different than your derived value of '' in col_c in table_b?
your using the union statement which removes duplicates from your 2 queries. and the records are unique because of the difference in col_c. Please post your sample data so we can better see what your issue is.
Why would you want to union values from two differnt tables that have a different # of columns and expect to only get one row?
create table #A (col_A int, col_B int, Col_C varchar(10))
create table #B (col_A int, col_B int)
Insert into #a values (1, 2, 'A')
Insert into #b values (1, 2)
select col_a, col_b, col_C
from #a
union
select col_a, col_b, '' as col_C
from #b
col_a,col_b,col_C
1,2,
1,2,A
October 12, 2005 at 9:31 am
col_c does not exist in TABLE_B - I'm just using it as a placeholder for the UNION. What can I do to get 1 row?
October 12, 2005 at 9:39 am
The first UNION that you did filtered out the second row because it was identical to the first. Your second UNION has rows which are not identical - so they are both output.
If you are receiving this as the output:
1,2,
1,2,A
which row should not be output? First thing we need is a rule about which rows get excluded from the output..
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 12, 2005 at 9:46 am
Since I am using the 'computed' columns as just placeholders to make the UNION work, I always want to use the values from the 'REAL' columns. So I woulkd want 1,2,A
October 12, 2005 at 9:51 am
OK - but there must be some circumstances under which you want the rows from TABLE_b, otherwise why bother with the UNION in the first place?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 12, 2005 at 9:58 am
TABLE_B has a column (order_no) which is not contained on TABLE_A. Or, maybe it's better for me to...
SELECT TABLE_A.col_a, TABLE_A.col_b, TABLE_A.col_c,
col_d = (SELECT col_d FROM _B ...)
FROM TABLE_A
October 12, 2005 at 10:08 am
I think this is maybe what you want:
SELECT TABLE_A.col_a, TABLE_A.col_b, TABLE_A.col_c, TABLE_B.col_d
FROM TABLE_A Left join TABLE_B on TABLE_A.key = TABLE_B.key
* Noel
October 12, 2005 at 10:09 am
Yes, this doesn't really sound to me like a UNION query after all.
Can you provide some sample data - just the relevant fields - and show also your desired output? Just two or three rows should be enough to clear this up.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 12, 2005 at 10:20 am
Hope this helps - thank you...
BL_TRUCK_BOL_DEST_COMM_ORDER specifies how much of a commodity has been order by a specific order. BL_TRUCK_BOL_DEST_COMM specifies the total amount of product the destination plans to receive. There could be multiple orders (BL_TRUCK_BOL_DEST_COMM_ORDER) to one BL_TRUCK_BOL_DEST_COMM.
SELECT BL_TRUCK_BOL_DEST_COMM.dest_bol_no,
BL_TRUCK_BOL_DEST_COMM.rev_no,
BL_TRUCK_BOL_DEST_COMM.comm_code,
BL_TRUCK_BOL_DEST_COMM.quantity,
BL_TRUCK_BOL_DEST_COMM.bol_no,
BL_TRUCK_BOL_DEST_COMM.trailer_id,
BL_TRUCK_BOL_DEST_COMM.pup_trailer_id,
'' order_no
FROM BL_TRUCK_BOL_DEST_COMM
WHERE BL_TRUCK_BOL_DEST_COMM.dest_bol_no = '00000001'
AND BL_TRUCK_BOL_DEST_COMM.rev_no = 0
AND BL_TRUCK_BOL_DEST_COMM.comm_code = '32410'
UNION
SELECT BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no,
BL_TRUCK_BOL_DEST_COMM_ORDER.rev_no,
BL_TRUCK_BOL_DEST_COMM_ORDER.comm_code,
BL_TRUCK_BOL_DEST_COMM_ORDER.quantity,
BL_TRUCK_BOL_DEST_COMM_ORDER.bol_no,
'' trailer_id,
'' pup_trailer_id,
BL_TRUCK_BOL_DEST_COMM_ORDER.order_no
FROM BL_TRUCK_BOL_DEST_COMM_ORDER
WHERE BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no = '00000001'
AND BL_TRUCK_BOL_DEST_COMM_ORDER.rev_no = 0
AND BL_TRUCK_BOL_DEST_COMM_ORDER.comm_code = '32410'
dest_bol_no...rev_no...comm_code...quantity...bol_no......trailer_id...pup_trailer_id...order_no 00000001......0...........32410..........1000.00....000515AC....................................00000001
00000001......0...........32410..........1000.00....000515AC...22..........NULL..........................
October 12, 2005 at 10:33 am
OK - getting there. I think you want something like this:
SELECT t1.dest_bol_no,
t1.rev_no,
t1.comm_code,
t1.quantity,
t1.bol_no,
t1.trailer_id,
t1.pup_trailer_id, t2.order_no
FROM BL_TRUCK_BOL_DEST_COMM t1 join BL_TRUCK_BOL_DEST_COMM_ORDER t2 on t1.dest_bol_no = t2.dest_bol_no and
t1.rev_no = t2.rev_no and t1.comm_code = t2.comm_code
WHERE t1.dest_bol_no = '00000001'
AND t1.rev_no = 0
AND t1.comm_code = '32410'
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply