Help with an SQL Statement

Hello. I was wondering if someone could help me with a SQL statement.
I have two tables that I need to get data from.
Here is some sample data of what the tables look like:
Table A Table B
PBUF0034 BE PBUF0034 GC
PBUF0034 GW PBUF0034 GW
ABBS0003 BE ABBS0003 GC
ABBS0003 GW
I want to be able to run a sql statement that will give me the following results:
PBUF0034 BE
PBUF0034 GW
PBUF0034 GC
ABBS0003 BE
ABBS0003 GW
ABBS0003 GC
I've tried using a statement with a left outer join on the number (PBUF0034) but I didn't get all of the records I wanted.
Could someone please help me with this statement?
Thanks in advance!!

union?

To get those exact results, try:
select field1 , field2 from a
union
select field1 , field2 from b ;
Use "union all" if you want data that's in both tables, such as "PBUF0034 GW" to show up twice.
(You might want to read up on union queries and union-compatibility.)

Thanks for the help. The union keyword did exactly what I wanted it to. Guess I was trying to make it too hard.
In response to reading up on union queries, what would be the best information resource?
Thanks again for your help!

Oops! Just ran into a problem. The union keyword worked, except for when there is a column in one of the tables I want that isn't in the other one.
I tried the union keyword using the following:
select field1,field2,field3 from tableA where field1 = 'XXX'
UNION
select field1,field2 from tableB where field1 = 'XXX';
Got an error message stating number of UNION operands not equal.
Any ideas?

if the column is varchar, then add a dummy column to the other select,
..., to_char(null),...

I tried adding the to_char(null) to my statement
and it gave me an error stating the column null is not in the specified tables???

Then just try NULL witout to_char()

I guess you do not have an Oracle DB.

she doesn't have Oracle

banish her!!!

JS: The union keyword worked, except for when there is a column in one of the tables I want that isn't in the other one.
Such a query is rejected because it's non-union-compatible. Union queries have to be union-compatible.
What union-compatible means is that each item in the select list in one part of the query
(1) must be a field of the same data type (text, numeric, date) as the corresponding item in the other part of the query,
(2) must be a constant of the same data type as the corresponding item in the other part of the query, or
(3) must be a null.
So if there's no appropriate field in the other table, select a null or appropriate constant.
select field1, field2, null ...
select field1, field2, 'nothingUsefulHere'

Thanks again for all the help! I got my statement to run with the union keyword.
Now there's just one more thing that's wrong.
Here's the data
Table A Table B
PBUF0034 BE PBUF0034 GC
PBUF0034 GW PBUF0034 GW
The union keyword gave me everything I wanted, except it has two rows in the result set for
'PBUF0034 GW' since it was found in both of the tables. How can I tell it I want it to return one row with the data from both of the tables?
And by the way, just if anyone's curious,
I'm using DB2. Maybe someday I'll get to use Oracle!

Thanks again!
[ June 09, 2003: Message edited by: Jennifer Sohl ]

Well, in Oracle at least

, by default the union will eliminate the dups unless you say union all.
Maybe the reverse is true for DB2

... try to modify and say union distinct!
Cheers,
Leslie
Please let us know -- Thanks

I tried using UNION DISTINCT and got the same result.
When UNION DISTINCT, it's looking at the entire row to see if it's identical isn't it?
In this case the rows are all different, so maybe that's why it's returning them all.
However, I only want it to look at the first two columns in the row. If those are the same, then don't output another row, just append the information all into one.
Is that even possible??
Thanks again!

it's looking at the entire row to see if it's identical isn't it?
In this case the rows are all different, so maybe that's why it's returning them all.
Yep.
As Leslie pointed out, UNION should remove duplicates. But it uses the fields in the select list to determine what's a duplicate and what's not. So if you're selecting in anything other than the field/fields you've shown us, then you're essentially creating result rows that the UNION will not consider to be duplicates.
For example my original query:
select field1 , field2 from a
union
select field1 , field2 from b ;
can return different results from this:
select field1 , field2 , 'from the first half' from a
union
select field1 , field2 , 'from the second half' from b ;
However, I only want it to look at the first two columns in the row. If those are the same, then don't output another row, just append the information all into one.
Before we go down this route, are you really sure you want to do this? The notion of "just append the information all into one" is somewhat at odds with some of the ideas behind relational databases. Things such as duplicate-determination happen at the level of the row as a whole and not on the individual fields you want -- unless you do some ugly tricks.
If you don't care about the additional fields you're selecting (which are causing what you consider to be duplicates) then why are you selecting them in the first place?
I have a couple of ugly solutions in mind. Do you know if DB2 supports something called inline views?

I searched around for inline views on the web. Found a lot of stuff for Oracle, but didn't find anything that indicated to me that DB2 supports them.
Exactly how ugly of ideas are we talking?

Can you show an example of the rows you want to collapse and the query that produced them? How many additional fields are we talking about? It'll be a little easier I think if we move to specifics. If it's data you can't post, make up something sufficiently representative. Make sure it's clear what stuff is coming from what table.

Here's the query:
SELECT pcsspe,pcsmfg,pcsvnd,pcsunt,pcsnpr,pcsnfc, pcsyld,pcsnfr,pcsnot,pcsocd,pcscmm,pcscdd,pcscyy, pcslt,pcshpl,pcsdmm,pcsddd,pcsdyy,pcsfip,pcscpr, pcscfr,pcscot,pcsccd,pcsnmm,pcsndd,pcsnyy,pcscip, pcsrmm,pcsrdd,pcsryy,'',0,0,'','',0 from
qs36f/pcspeci where pcsspe = 'ABBS0003'
UNION
SELECT inispe,inimfg,0,'',0,0,
0,0,0,'',0,0,0,
0,'',0,0,0,'',0,
0,0,'',0,0,0,'',
0,0,0,inistk,inidus,inidst,iniicl,iniimt,iniqre from qs36f/inspeci where inispe = 'ABBS0003'
The two rows I want to combine:
(I didn't post the whole row, too long)
ABBS0003 GW PIECES,0,0, 0,0,-, 0
ABBS0003 GW - ,Y,3,31,M,L,150
The first row is from the first select statement (QS36F.PCSPECI),the second row is from the second select statement. (QS36F.INSPECI).
Is that what you needed?
Hopefully I didn't confuse you too much.
Let me know if you need anything else.
Thanks again for your help!

I'm going to push back again (and not just because I'm currently stumped by this).

What exactly is it you're trying to do? What question is it you're trying to ask about this data?
When you talk of combining these two rows, which values from the extra fields are you expecting to be returned? Are you trying to create some relationship in the data that's not really there?
You seem to be saying give me all the spe/mfg combinations (whether they be pcsspe or inispe, pcsmfg or inimfg) that exist on either of these two tables. Oh, and also by the way, give me all these other fields from those tables. But I'm only interested in the values of these other fields if it just so happens that these fields are populated only on one of the two tables I'm looking at. (That's how I'm interpreting your interest in combining what you're seeing as duplicates.)
I guess I just don't understand what question has the answer you're trying to write a query to come up with.

I think the WHERE clauses in both of your SELECT statements have a common denominator. That means, there is a common field to both the tables.
If you have successfully represented the environment, you can use a JOIN instead of UNION.

select A.column1, A.column2, B.column3, B.column4 from TableA A, TableB B where A.commonA = B.commonB and A.commonA = "xxxxxxxx";

Where commonA in TableA and commonB in TableB represent the same entity and is our commonField.
[ June 09, 2003: Message edited by: Murali Nanchala ]

Michael,
What you said is exactly what I want to do. I guess I'm just trying to get everything lumped together to make it easier when it comes time to code the program using this data. The only other way I could see getting this data I wanted was running two separate stored procedures to get the information from one file, then the other. I thought if I only tapped the DB once, it would be much more efficient?
Is this a bad thing, what I'm trying to do?
If so, what are the drawbacks?
Thanks!

Generally speaking, you always want to limit the number of trips to the database. The trip is usually over a network which is costly.

But I still don't understand what you're trying to do.
How about I show through an example what I think you're trying to do and hopefully show you why I don't really think it's a valid query.
Let's say you have two tables that store (US) address information for people (identified by id numbers). One table stores the current address. The other address stores a previous address. (NOTE: by no means am I suggesting that this is an appropriate way to store addresses. I'm just trying to come up with tables vaguely similar to yours.)
Here's some data:

Using this data as an example, the question and query that I believe best corresponds to what you were saying in your very first post on this topic is "Give me a list of all the ids that have a row on either currentAddress or formerAddress. If the id has rows on both tables, only return the id once."
I think we've established that the query that produces these results is
select id from currentAddress
union
select id from formerAddress ;
id
-----
12345

Then you add that you want to include additional fields in the results. So that means your query and results look like this:
select id, address1, city, state, postal from currentAddress
union
select id, address1, city, state, postal from formerAddress ;
id address1 city state postal
----- -------------------- --------------- ----- ---------
12345 123 Main Street Anytown MI 444443333
12345 456 State Street Formerville VT 555557777
But this isn't what you want. You're thinking of these rows as "duplicates" because they have the same id. SQL doesn't see these rows as duplicates because it considers all the fields in the select list and at least one field is different across the rows, so they're not duplicates.
When you say "combine" rows, this is what I envision:
(1)

(2)

Ok, not exactly, but do you get the point? Result sets (1) and (2) are nonsensical. Show me with my data what combining these rows means to you.

OK. I think maybe I can tell you what I'm after using your data.
Say the table currentAddress has all the columns you have listed in it. Now, the formerAddress table doesn't have any of the same columns as currentAddress except for id and address1. We'll say it also has a column for comments.

So if I were to query the tables this is what I would get right now:

I would rather have all of this data for both of the tables on one row.
In our case we have the two tables PCSPECI and INSPECI. The only thing they have in common is the specie number and the location.
The PCSPECI table holds all of the purchasing information about the specie.
The INSPECI table holds all of the inventory information about the specie.
It's kind of screwy becuase we have a location called "GC", but there can only be a "GC" record for purchasing information.
Also, there has to be an inventory record for the locations "BE" and "GW", and we don't allow a purchasing record for "BE", but we do for "GW".
Kinda screwy if you ask me ! :roll:
Do you understand now?
Since this situation seems so complicated, I've been thinking of maybe just returning two sepearate result sets from a stored procedure and looping through them both, adding a location to a HashMap if it's not found, and appending the information if it's already in the HashMap.
What would you suggest?
Also, what is the difference in terms of efficiency returning two result sets from two separate select statements vs. returning one result set from one select statement?
By the way... Thanks so much for all of your time you've put into helping me out. It is GREATLY appreciated!!!
[ June 11, 2003: Message edited by: Jennifer Sohl ]

If you want to combine

into one row you need to use an inline view. the first column you use distinct, all other columns will need either max() or sum() operations performed on them.
really it's a bit of a hack, but should get you want you want 95% of the time.

If you've got a small number of special cases, perhaps you could handle each separately, then UNION them all together. Or if your SQL dialect supports some sort of IF, CASE, or SWITCH, that might be used to split out the special cases.

So if I were to query the tables this is what I would get right now:

Good that we have that straight. That's not at all what I understood from your previous post:

The two rows I want to combine:
(I didn't post the whole row, too long)
ABBS0003 GW PIECES,0,0, 0,0,-, 0
ABBS0003 GW - ,Y,3,31,M,L,150

Anyhow, to get what you want, you can use Simon Lee's approach:

Note several things:
(1) You don't need "distinct" because your select list has grouping functions.
(2) Stuff like this is pure evil. Pure. Evil. Think long and hard whether you want to be doing stuff like this. Think about the how you're stretching the semantics of max() or sum() or min() or whatever other grouping function you're using.
(3) This uses an "inline view," which you said you don't think your product supports. The inline view is the view that the DB creates on the fly from the (select...union...select) in the from clause. You're not selecting from an actual table here, but from a view that's getting created on the fly. If your product doesn't support this, create a real view with the (select...union...select) and build your query off of that.
Further evilness (ok, this really isn't evil, because it doesn't cross rows):
For future reference, here's a way to force in what order you pull from the different tables if the same id exists on both:

Again, if your product doesn't support inline views, you'd need to create a real view and use it twice here.
[ June 12, 2003: Message edited by: Michael Matola ]