0

My understanding of why certain MySQL Joins do what they do is a bit scatterbrained.

I have multiple tables, ALM_RECORDS which has ID, and ALM_PHONES, ALM_ADDRESS both which have a column RecordsID that correlates to ALM_RECORDS's ID column.

Basically i need to pull from all 3 tables based on this ID. Below is my query, however it keeps returning duplicated entries threefold.

SELECT a.ID, a.FIRST_NAME, a.LAST_NAME, 
       b.RecordID, b.PHONESID, b.PhoneType, b.NUM,        
       c.RecordID, c.address_block, c.city, c.state, c.post_code, c.country
FROM ALM_RECORDS a
    LEFT JOIN ALM_PHONES b
        ON a.ID = b.RecordID
    LEFT JOIN ALM_ADDRESS c
        ON a.ID = c.RecordID
WHERE a.ID = "123456"

It's result should be:

123456 - John - Smith - Business - 111.222.3333 - address etc..
123456 - John - Smith - Home     - 444.555.6666 - address etc..

but what i get is:

123456 - John - Smith - Business - 111.222.3333 - address etc..
123456 - John - Smith - Business - 111.222.3333 - address etc..
123456 - John - Smith - Business - 111.222.3333 - address etc..
123456 - John - Smith - Home     - 444.555.6666 - address etc..
123456 - John - Smith - Home     - 444.555.6666 - address etc..
123456 - John - Smith - Home     - 444.555.6666 - address etc..

Can someone explain to me what i'm missing here? Should i be using a different type of join?

CrckrJack
  • 93
  • 5
ipixel
  • 519
  • 8
  • 21
  • you're missing a join somewhere. Do a `select *` to find out which table is the culprit and what join you need to make. – Matt Busche Sep 13 '13 at 18:21
  • I think you just need a `GROUP BY b.NUM` to rid yourself of the dupes – DevlshOne Sep 13 '13 at 18:21
  • Using SELECT * Does the same thing, just shows a ton more data. What exactly should i be looking for? – ipixel Sep 13 '13 at 18:22
  • Group By idea works, but is that the "correct" way to solve this? – ipixel Sep 13 '13 at 18:24
  • A `GROUP BY` would probably best be on: `a.ID, b.ID, c.ID`. You'll still have multiple records per person, but no dupes. (`GROUP BY` is critical to more complex queries) –  Sep 13 '13 at 18:25
  • Your query is correct. something is probably wrong with your tables data. can u proviode some sample data? However if you just want no ignore duplicate records you can start your query with "select distinct a.id,....". Maybe some value in the "etc..." is different. If your result rows are REALLY duplicate, then that's what DISTINCT is made for. but they shouldn't be according to your query – Thanos Darkadakis Sep 13 '13 at 18:25
  • You have no choice but to use a `LEFT JOIN` because of the diversity of the records that you're grafting together to get your result.. so, yes, using `GROUP BY` is your only means to limit the results to non-duplicating. – DevlshOne Sep 13 '13 at 18:26
  • Are you sure that on the join condition there are no missing or extra rows? – Mihai Sep 13 '13 at 18:27
  • Great! Thanks for the input. I definitely cannot do Group By on ID's as that will eliminate phone types, i'll either group by phonetype or num. Thanks again! – ipixel Sep 13 '13 at 18:29

1 Answers1

0
SELECT a.ID, a.FIRST_NAME, a.LAST_NAME, 
       b.RecordID, b.PHONESID, b.PhoneType, b.NUM,        
       c.RecordID, c.address_block, c.city, c.state, c.post_code, c.country
FROM ALM_RECORDS a
    LEFT JOIN ALM_PHONES b
        ON a.ID = b.RecordID
    LEFT JOIN ALM_ADDRESS c
        ON a.ID = c.RecordID
WHERE a.ID = "123456" GROUP BY b.NUM
DevlshOne
  • 8,357
  • 1
  • 29
  • 37
  • Just realized i'm only getting 38 results from what should be close to the 100,000 range LOL. Any ideas? – ipixel Sep 13 '13 at 18:44
  • Nevermind, i'm an idiot. GROUP BY b.PhoneType does that. GROUP BY b.NUM returned a hefty result. THANKS! – ipixel Sep 13 '13 at 18:57