Skip to main content
A
Anonymous
June 29, 2008
Answered

Merge two tables or queries, not identical

  • June 29, 2008
  • 8 replies
  • 1255 views
This is probably the dumbest question I've posted yet, but my mind is numb and I'm not getting anywhere, no matter how much I google terms.

I have three tables in a MS Access DB: Owners, Vehicles, Other. Owners is owner info, Vehicles is car info, Other is misc items info. Vehicles and Other have an OwnerID field that, funny enough, corresponds to the Owners ID field. What I'm trying to do is pull all the Vehicles and all the Other items that belong to a single owner and were added before a certain date. I'm obviously going about it backwards, because after two days of staring at this I now have a syntactically working query that will return 739 records when it should return 6. I think that's a new personal best.

I guess the first question is whether I should run two seperate queries (one for Vehicles, one for Other) and merge them prettily with CF code, or pull everything at once with one big query. Neither idea, so far, has worked. The hoped for outcome is a report in the following format:

Owner Name
Vehicle info here, sorted by date added
Vehicle info here, sorted by date added
Other info here, sorted by date added
Other info here, sorted by date added
-----------------------------------------------------------
4 records

Owner Name
Vehicle info here, sorted by date added
etc etc
------------------------------------------------------------
# records, etc

I wasn't successful looping through seperate queries, so here's the current Select statement.

SELECT owners.ownerid, owners.fname, owners.lname, owners.bname,
vehicles.id as vid, vehicles.ownerid as vownerid,
vehicles.year as vyear, vehicles.mfr as vmfr, vehicles.model as vmodel,
vehicles.price as vprice, vehicles.date_add as vdate_add,
other.id as iid, other.ownerid as iownerid,
other.year as iyear, other.mfr as imfr,
other.model as imodel, other.item_name as iname,
other.price as iprice, other.date_add as idate_add
FROM owners, vehicles, other
WHERE (vehicles.ownerID = owners.ownerID
AND vehicles.date_add < #createODBCDate(variables.begin_date)#)
OR (other.ownerID = owners.ownerID
AND other.date_add < #createODBCDate(variables.begin_date)#)
ORDER BY vehicles.ownerID, vehicles.Date_add, other.ownerID, other.date_add

Anybody got a clue where I should restart? I know this shouldn't be this difficult.
Thanks.

This topic has been closed for replies.
Correct answer paross1
Taking another look at the solution posted by draves, try removing other from the FROM in the first query, and vehicles from the FROM in the second, since nothing is selected from, or related to, these tables in their respective queries. (I think that it was probably just an oversight)

select x.* from (
SELECT '1' as skey,'VEHICLE' as sname,
owners.ownerid, owners.fname, owners.lname, owners.bname,
vehicles.id, vehicles.ownerid, vehicles.year, vehicles.mfr, vehicles.model,
'' as item_name,vehicles.price, vehicles.date_add
FROM owners, vehicles
WHERE vehicles.ownerID = owners.ownerID
AND vehicles.date_add < #createODBCDate(variables.begin_date)#
union all
SELECT '2' as skey,'OTHER' as sname,
owners.ownerid, owners.fname, owners.lname, owners.bname,
other.id, other.ownerid, other.year, other.mfr , other.model ,
other.item_name,other.price, other.date_add
FROM owners, other
WHERE other.ownerID = owners.ownerID
AND other.date_add < #createODBCDate(variables.begin_date)#
) x
ORDER BY lname,fname,ownerID,skey,Date_add

Phil

8 replies

A
Anonymous
July 2, 2008
I mostly understand it. I do understand the change that made it work, and that's sort of an "Ooooohhhhh, of course" kind of thing. (not that I ever would've caught it before, but I guess I could now.) Again, thanks all.
P
paross1
Participating Frequently
July 3, 2008
You had what was considered a Cartesian join (cross-join). This happens "When you perform a multiple-table query that does not explicitly state a join condition among the tables...... A Cartesian product consists of every possible combination of rows from the tables. This result is usually large and unwieldy." Your two queries had an "extra" unecessary table included in the FROM, which caused this condition.

Nasty Join Operations

Creating a Cartesian Product

Phil
A
Anonymous
July 2, 2008
*ding ding ding* Holy Cow! We have a winner! Yay! Thank you all so much. There's no points distribution or anything to worry about when I mark the answer is there? (I was going to tag paross1's last entry, if that's ok with draves)
Oh, you don't know how much I appreciate the help! Thanks all!
P
paross1
Participating Frequently
July 2, 2008
No points, but draves should get any "credit" since I only pointed out one oversight.

The most important thing here (other than having it now working for you) is that you understand what was done and why.


Phil
A
Anonymous
July 2, 2008
Your right. That was a oversight.
A
Anonymous
July 2, 2008
Tell you what, since I can't seem to get around this "Cartesian join" I've created (after latest help I can now pull 2380 records. it just keeps getting better!), how about somebody explain how I would print out the results if I do two seperate queries (one for vehicles for the owner, one for other items) and then join them in the output. I know how to do the Group = "ownerid" for a single query like getVehicles, but how would I shuffle in the getOther query? I couldn't figure out how to say While OwnerID = "x", Print Out Matching Other Records.

And thanks for all the help so far. Sorry I'm being so difficult.
P
paross1Correct answer
Participating Frequently
July 2, 2008
Taking another look at the solution posted by draves, try removing other from the FROM in the first query, and vehicles from the FROM in the second, since nothing is selected from, or related to, these tables in their respective queries. (I think that it was probably just an oversight)

select x.* from (
SELECT '1' as skey,'VEHICLE' as sname,
owners.ownerid, owners.fname, owners.lname, owners.bname,
vehicles.id, vehicles.ownerid, vehicles.year, vehicles.mfr, vehicles.model,
'' as item_name,vehicles.price, vehicles.date_add
FROM owners, vehicles
WHERE vehicles.ownerID = owners.ownerID
AND vehicles.date_add < #createODBCDate(variables.begin_date)#
union all
SELECT '2' as skey,'OTHER' as sname,
owners.ownerid, owners.fname, owners.lname, owners.bname,
other.id, other.ownerid, other.year, other.mfr , other.model ,
other.item_name,other.price, other.date_add
FROM owners, other
WHERE other.ownerID = owners.ownerID
AND other.date_add < #createODBCDate(variables.begin_date)#
) x
ORDER BY lname,fname,ownerID,skey,Date_add

Phil
A
Anonymous
July 2, 2008
Well, at least I'm in good company. As written, the Union Select pulled 913 records. Are we talented, or what? Personally, I thought it had great potential. Any ideas what we missed? The records all seem to be listed by a multiple of 7. Sometimes 77 times, others 14, others only 7 times. But they are in nice, neat ID order. And the Parts are mixed with the Vehicles like I wanted, grouped by Owner.

Usually when I get too many records like this, it's because I've got the WHERE x = y clause backwards (y = x), but that is not the case this time. It still pulls 913 records, either way. Why?
P
paross1
Participating Frequently
July 2, 2008
Looks like a possible Cartesian join..... you are joining owners, vehicles, and other, but only "relating" vehicles to owners in the first query, and other to owners in the 2nd (union) query. This will cause you to multiply the number of rows returned by the number of rows in the unrelated table. You need to relate other to either owners or vehicles in the first query, and vehicle to either owners or other in the second.

FROM owners, vehicles, other
WHERE vehicles.ownerID = owners.ownerID

--and--

FROM owners, vehicles, other
WHERE other.ownerID = owners.ownerID

Phil
A
Anonymous
June 30, 2008
Please explain the lines
'1' as skey,'VEHICLE' as sname,
'2' as skey,'OTHER' as sname,

What in particular do I do with those?
A
Anonymous
June 30, 2008
Most of the versions of SQL I have seen only require the number of columns to be the same and the data types to be compatible for a Union to work. The output would then use the names from the first query. In a recent change to AS/400 SQL I have seen it actually require the names to be the same with a Union if the column was refered to externally in the order by or if the queries in a Union were part of a sub query. If you keep the column names and types consistant it should work. If you want to use a null instead of the empty string some SQLs require you to cast the null to the same type column.

So you can know the source of the output and can sort on it the way you want I put in the skey and sname. The order by is referring to skey positionally as the first column. If the SQL you use does not like the position number then try using the skey by the name though only a few SQLs I have seen act that way. If neither work then the whole query might be selected from as below:

select x.* from (
SELECT '1' as skey,'VEHICLE' as sname,
owners.ownerid, owners.fname, owners.lname, owners.bname,
vehicles.id, vehicles.ownerid, vehicles.year, vehicles.mfr, vehicles.model,
'' as item_name,vehicles.price, vehicles.date_add
FROM owners, vehicles, other
WHERE vehicles.ownerID = owners.ownerID
AND vehicles.date_add < #createODBCDate(variables.begin_date)#
union all
SELECT '2' as skey,'OTHER' as sname,
owners.ownerid, owners.fname, owners.lname, owners.bname,
other.id, other.ownerid, other.year, other.mfr , other.model ,
other.item_name,other.price, other.date_add
FROM owners, vehicles, other
WHERE other.ownerID = owners.ownerID
AND other.date_add < #createODBCDate(variables.begin_date)#
) x
ORDER BY lname,fname,ownerID,skey,Date_add
A
Anonymous
June 30, 2008
Well, for one, since I created the two tables Vehicles and Other, I know how very different the two are and it hasn't dawned on me yet I'm pulling almost exactly the same fields from both. Doh! Thank you for pointing that out.
For two, didn't know I was allowed ANY differences in the column names. I can do the "" blank thing for the vehicles item_name? Cool. Let me get some sleep and I will investigate your answer. Thank you so much.
A
Anonymous
June 30, 2008
Not sure why you would have a problem looping over a query of owners and issuing separate queries to vehicles and other. Your joins are causing a cartesian product. Why can't you force the results of the queries to have the same column name and data type so you can use a UNION ALL? The table structure difference seen from what you supplied is item_name is not in vehicle.

SELECT '1' as skey,'VEHICLE' as sname,
owners.ownerid, owners.fname, owners.lname, owners.bname,
vehicles.id, vehicles.ownerid, vehicles.year, vehicles.mfr, vehicles.model,
'' as item_name,vehicles.price, vehicles.date_add
FROM owners, vehicles, other
WHERE vehicles.ownerID = owners.ownerID
AND vehicles.date_add < #createODBCDate(variables.begin_date)#
union all
SELECT '2' as skey,'OTHER' as sname,
owners.ownerid, owners.fname, owners.lname, owners.bname,
other.id, other.ownerid, other.year, other.mfr , other.model ,
other.item_name,other.price, other.date_add
FROM owners, vehicles, other
WHERE other.ownerID = owners.ownerID
AND other.date_add < #createODBCDate(variables.begin_date)#
ORDER BY lname,fname,owners.ownerID,1,Date_add
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices