INTERSECT 2 tables in MySQL

Question

Hi,

I need to intersect two tables based on a column,in both tables.

Here's my code snippet :

SELECT b.VisitID,  b.CarrierName, b.PhoneNum, b.PatientName, b.SubscriberID, b.SubscriberName, 
        b.ChartNum, b.DoB, b.SubscriberEmp, b.ServiceDate, b.ProviderName, b.CPTCode, b.AgingDate, 
        b.BalanceAmt, f.FollowUpNote, f.InternalStatusCode FROM billing b JOIN followup f 
        USING (VisitID) WHERE b.VisitID = f.VisitID

In the 'followup' table I've 281 rows AND 'billing' table contains 2098 rows. When I execute this query I'm getting 481 rows.

Did anyone faces this sort of problem? Could you help me to INTERSECT these tables?

Thanx in advance..

Answer 1

I think you like to do a left join here (not an inner join as in your example):

SELECT b.VisitID,  b.CarrierName, b.PhoneNum, b.PatientName,
  b.SubscriberID, b.SubscriberName, b.ChartNum, b.DoB,
  b.SubscriberEmp, b.ServiceDate, b.ProviderName, b.CPTCode,
  b.AgingDate, b.BalanceAmt,
  f.FollowUpNote, f.InternalStatusCode
FROM billing b
LEFT JOIN followup f ON b.VisitID = f.VisitID

This will also return rows from the 'billing' table that don't have corresponding fields in the 'followup' table.

Answer 2

It seems to me that you'd be very likely to have multiple follow ups. Thus the 481 records from the notes table is likely accurate.

Perhaps add an

ORDER BY b.SubscriberID

to JochenJung's answer above and accept that you have the right number of rows. alternately a

GROUP BY b.SubscriberID

Would give you one row per customer