CODEWITHSUNDEEP
mysqlintersection
Venkat
Venkat

Reputation: 2634

INTERSECT 2 tables in MySQL

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..

Upvotes: 1

Views: 4189

Answers (2)

Alex C
Alex C

Reputation: 17004

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

Upvotes: 1

JochenJung
JochenJung

Reputation: 7213

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.

Upvotes: 2

Related Questions