join tables and include blank data/rows not in either table

[deso]

Hi all,

I am trying to join two tables from a groundwater database. Table 1 has bore ID and geological feature data with corresponding depth. Table 2 has bore ID and sedimentary data with corresponding depth. For each geological feature there are multiple sedimentary data with corresponding depth. i.e each geological feature contains a range of sediment types. There are top and bottom data for depth for both geological and sediment tables. The problem I have is that when I join these two tables, the query populates what should be blank cells with a value, and the table isn't arranged in order. I need the order of the data to be bore ID, and then by depth. But the depth order needs to be a combination of the two different depth values in each table. There should be rows with blanks, as not every field is occurs in each table. So to try and explain this see the simplified tables below. There's three tables (sorry) but its so much easier to demonstrate it this way, without waffling on with words. Maybe this will require multiple steps. Filters don't work because the join populates depth fields to rows where there should be a blank, and when it does the depths are totally wrong, so when the filter is applied its wrong as well. I've got thousands of bores to sort, so any help would be greatly appreciated.

Table 1. example

BORE ID	material	Top depth	bottom depth	Geo
1	steel	0	1	Sandstone
1	open steel	1	2	Shale
2	plastic	0	3	Basalt
2	open plastic	3	7	Granite

Table 2. example

Bore ID	Top	Bottom	Sediment
1	0	0.5	soil
1	0.5	0.8	sand
1	0.8	0.9	sandy clay
1	0.9	1.2	gravel
2	0	0.2	soil
2	0.2	0.5	sandy clay
2	0.5	1.7	clay
2	1.7	5	rock
2	5	6.8	loose rock

I need the result to look like this table below. The "-" are where I want a blank.

Bore ID	Material	sed Top	sed Bottom	Sed	geo top	geo bottom	Geo
1	-	0	0.5	soil	-	-	-
1	-	0.5	0.8	sand	-	-	-
1	steel	-	-	-	0	1	Sandstone
1	-	0.9	1.2	gravel	-	-	-
1	open steel	-	-	-	1	2	Shale
2	-	0	0.2	soil	-	-	-
2	-	0.2	0.5	sand clay	-	-	-
2	-	0.5	1.7	clay	-	-	-
2	-	1.7	5	rock	-	-	-
2	plastic	-	-	-	0	3	Basalt
2	-	5	6.8	loose rock	-	-	-
2	plastic open	-	-	-	3	7	Granite

[deso]

I ended up solving this. If anyone is interested in how I did it here's the answer in a nutshell.

I used a select left join to select everything I wanted from Table A with the added "on TableA.BOREID=TableB.BOREID". Then I did another select left join but in reverse for TableB again with "on TableA.BOREID=TableB.BOREID". in between both select left join SQL statements I used a UNION statement, which joins everything. Because both tables have a different number of columns, I had to insert some dummie columns in both tables so everything matched up.

A basic structure for the final SQL should be something like:

SELECT TABLEA.BOREID, MATERIAL, TOP DEPTH, BOTTOM DEPTH, DUMMIEA1, GEO FROM TABLEA LEFT JOIN ON TABLEA.BOREID=TABLEB.BOREID
UNION
SELECT TABLEB.BOREID, DUMMIEB1, TOP, BOTTOM, SEDIMENT, DUMMIEB2 FROM TABLEB LEFT JOIN ON TABLEA.BOREID=TABLEB.BORID.

You need to add the dummie columns because the union query tries to match up the columns in each table, based on the order of the SQL SELECT statement. So DUMMIEA1 is blank in table A, and it ends up containing SEDIMENT values in the query result, as both DUMMIEA1 and SEDIMENT are 5th in the order of the SQL statement. There's no values in the DUMMIEA1 column, so the result of the query means only the SEDIMENT values end up in the column. I left the top and bottom data to merge, which works out fine. But, like DUMMIEA1, I used a DUMMIEB1 and DUMMIE B2 in TableB to ensure the MATERIAL values and GEO values from tableA occur in the own columns in the query result also.

hope this helps someone, cause being a novice it drove me bonkers

[ssanfu]

This is as close as I could get it. I don't see a way have a sort closer to the way you need. There is not another common field (like BoreID) to do a second level sort. Maybe a SQL guru will know a method.
BTW, the query is a union query.

[deso]

thanks ssanfu. That's great. I ended up merging the top and bottom data into two columns in the UNION query and then applied a filter to sort by numerical order for each BORE.ID. seems to have worked well, except a few cells won't sort. Other than that, I'm happy with the outcome. Thanks a lot for spending the time to help me solve the problem.

[ssanfu]

Wonderful...