3 Table join help 2

[gaperry]

I have three tables that I need to join. There are several fields in each table that are the same and there is only one common field between all three.

I was using the common field for each table to make the join but the problem I am having is that when I add the third table, I get rows of data in triplicate. (but not for ALL records) The third table has only one field of data that I need. I have tried various kinds of joins but cannot seem to come up with the right combination.

Should I be using DISTINCT here ? Or perhaps a sub SELECT statement within the main select statement ?

Any quidance here is appreciated.

Thanks

 

[jhall156]

Please provide the query. JHall

 

[bperry]

Hi there,
You query probably looks something like this, does it?

Select col1, col2, col3
from TableA a
INNER JOIN TableB b
ON a.ComCol = b.ComCol
INNER JOIN TableC c
ON a.ComCol = c.ComCol

(Perhaps you would like to post your own query so we can see it?)
--------------------
It sounds like when you add the third table, you start getting more rows in the result because the third table has n+ rows that match that common col.

In this situation, you are probably going to be using a Group By predicate to summarize the results into one row. (Sometimes it can also be done with a DISTINCT or subquery...it all depends).

A key question to be considered: since there are apparently multiple matching rows in that third table, WHICH value of the 'field of value' in that third table would you want to see in the summaarized row? for instance, the MAX value of that field, say?

If you post your query here, (plus also any additional comments that you can think of), probably someone will be able to make a helpful suggestion.
bp

 

[gaperry]

Thanks for the quick response. I have toned down the select statement so you would not have to much data to look at. These table and field names will change so please ignore the "ugly". The 3rd table I spoke of is named "tbl_ship_pmu_stg" and the field I need is SHPRFG

All three tables share the common field named CHONO.

You are probably right about having multiple rows of data show up when I added the third table because many of the fields and data are the same. I really just need the field from the third table named SHPRFG. I did try both the DISTINCT and GROUP BY with different results for each but that seemed to have taken away the dups. There will be some cases in this join where I will probably have duplicate entries which is what makes this hard for me to determine proper technique. I was thinking that the subquery or nested query might be the answer here ?

Anyway, here is the select statement. Thanks for the help. I am out the door to go buy a good T-SQL book for future reference.


SELECT tbl_bookings_pmu_open_det.CDPN, tbl_bookings_pmu_open_ord_hdr.CHONO, tbl_bookings_pmu_open_det.CDUPRI, tbl_SHIP_PMU_STG.SHPRFG
FROM tbl_SHIP_PMU_STG INNER JOIN tbl_bookings_pmu_open_ord_hdr ON tbl_SHIP_PMU_STG.CHONO = tbl_bookings_pmu_open_ord_hdr.CHONO INNER JOIN
tbl_bookings_pmu_open_det ON tbl_bookings_pmu_open_ord_hdr.CHONO = tbl_bookings_pmu_open_det.CHONO

 

[jhall156]

Try joining your tables like this pseudocode

FROM Header INNER JOIN Detail ON Header = Detail INNER JOIN Other ON Header = Other
JHall

 

[jhall156]

I'm a little confused by the complaint of duplicate rows. Although I would join the tables differently it looks like you should get duplicate header and _stg values but only insofar as the multiple CHONO records in the detail table.

So my question is, is there another field in the detail table that when coupled with the CHONO makes it unique such as linenumber? If so, does the tbl_SHIP_PMU_STG table also contain this field?

If so then your pseudo FROM clause should look something like this

FROM header INNER JOIN detail ON header.chono = detail.chono
INNER JOIN other ON detail.chono + detail.otherfield = other.chono + other.otherfield


the otherfield may need a conversion if it and the CHONO are not the same datatype.

JHall

 

[bperry]

gaperry:
My response here is a little long, so I've broken it up into sections.

Here is your original query re-coded a bit just to make it easier for us to read/debug:
------------------------------------------
SELECT
d.CDPN,
h.CHONO,
d.CDUPRI,
s.SHPRFG
FROM tbl_SHIP_PMU_STG s
INNER JOIN tbl_bookings_pmu_open_ord_hdr h
ON s.CHONO = h.CHONO
INNER JOIN tbl_bookings_pmu_open_det d
ON h.CHONO = d.CHONO
------------------------------------------
By the way, most people would probably have put the tables in a different order. Usually doesn't mater with INNER JOINS, but it just sort of makes more sense if you linking "from the header to the detail". Maybe like this:

SELECT
d.CDPN,
h.CHONO,
d.CDUPRI,
s.SHPRFG
FROM tbl_bookings_pmu_open_ord_hdr h
INNER JOIN tbl_bookings_pmu_open_det d
ON h.CHONO = d.CHONO
INNER JOIN tbl_SHIP_PMU_STG s
ON s.CHONO = h.CHONO
--------------------------------------
Here's a version that you might want to try.....As JHall said, we are a little confused and having a little trouble figuring out what it is you want, so it's hard to tell if this is on the right track or not.

SELECT
d.CDPN,
h.CHONO,
d.CDUPRI,
s.SHPRFG
FROM tbl_bookings_pmu_open_ord_hdr h
INNER JOIN tbl_bookings_pmu_open_det d
ON h.CHONO = d.CHONO
INNER JOIN tbl_SHIP_PMU_STG s
ON s.CHONO = h.CHONO
GROUP BY h.CHONO, d.CDPN, d.CDUPRI,

You can try this, but I dunno if it will give you what you are looking for.
--------------------------------------------
This process will be *much* easier if you would like to post:

(1) a bit of sample data of what the tables look like (relevant columns only!), and
(2) an example of the kind of output you want


Sorry for such a long post. It would have been better if I could have helped you, but maybe if you provide that information we will then be on the right road.

bp

 

[gaperry]

I would like to just say thanks to both of you. I took your advice and narrowed tables down a bit(less is more) and was able to get the results I was looking for.

Thanks again.