Solved

Need a 3 (multi) table join sql query with a limit 1 on one table (mysql)

Posted on 2013-11-04
5
363 Views
Last Modified: 2013-11-04
Thanks in advance - I don't know enough SQL to write this.  Using MySQL:

I have a table of organizations, and organizations can have more than one address, so I keep the addresses in a separate table and there's a join table between the two.  I want to sort my list of organizations by the CITY  NAME stored in the ADDRESSES table (instead of sorting alpha by the organization name).  My query works but I get duplicate org names in my result list when there's more than one address for that org in the join table - I just want to grab the city name of the first address for each org (limit 1) from whichever address it finds first -- that's good enough.  I just don't know how to add "limit 1" to my query with proper syntax...:

Three tables:
(A)
ORGANIZATIONS
org_id
org_name

(B)
ADDRESSES
addr_id
addr_city (ie city name for that address)

(C)
ORGANIZATIONS_ADDRESSES
org_id
addr_id

My query that's close:

select a.org_id, a.org_name,
b.addr_city, b.addr_id,
c.addr_id
from organizations a
inner join organizations_addresses c
 on a.org_id = c.org_id
inner join addresses b
 on b.addr_id = c.addr_id
where a.client_id = 2
order by b.addr_city

But I'll get results like
ACME ORG
BEDFORD ORG
FIRST BANK ORG
FIRST BANK ORG
FIRST BANK ORG
GREENHILL ORG

because first bank had 3 addresses... just want it listed once.
Just need a "limit 1" when it runs the join part on organizations_addresses

I've googled and googled for examples and tried everything and nothing works, I only have an amateur/self-taught knowledge of mysql...

I know there's a lots of ways to write queries and I may need to post more detail, but I hope that's enough info?

Thanks so much !!!
0
Comment
Question by:centerforward
5 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Try add a DISTINCT first - looks like there are duplicate rows - like:

select      DISTINCTROW
      a.org_id,
      a.org_name,
      b.addr_city,
      b.addr_id
from organizations a
      inner join organizations_addresses c on a.org_id = c.org_id
      inner join addresses b on b.addr_id = c.addr_id
where a.client_id = 2
order by b.addr_city
0
 
LVL 9

Accepted Solution

by:
Derek Jensen earned 500 total points
Comment Utility
SELECT a.org_id, a.org_name, b.addr_city, b.addr_id, c.addr_id
FROM organizations a, addresses b, organizations_addresses c
WHERE a.org_id = c.org_id
AND b.addr_id = c.addr_id
AND a.client_id = 2
GROUP BY a.org_name
ORDER BY b.addr_city

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>My query works but I get duplicate org names in my result list when there's more than one address for that org in the join table
This is standard behaviour for joined results. I prefer to call this repetition

>>I want to sort my list of organizations by the CITY  NAME
then if you are sorting purely by city name you NEED repetition. e.g.
CITY       ORGANIZATION
London     Acme Corp
Melbourne  Zebra Limited
Milan      Acme Corp
New York   Charlie Delta
Paris      Acme Corp

Open in new window

I suspect you are sorting by organization and city, but expecting the repetition to disappear - it won't.

SQL isn't a report writer
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Here's your query run against some assumed data (but I missed client_id), it is sorted by City and I would (again) suggest you actually need repetition of the organization when sorted this way.
    CREATE TABLE ORGANIZATIONS
    	(`org_id` int, `org_name` varchar(13))
    ;
    	
    INSERT INTO ORGANIZATIONS
    	(`org_id`, `org_name`)
    VALUES
    	(1, 'Zebra Limited'),
    	(2, 'Charlie Delta'),
    	(3, 'Acme Corp')
    ;
    
    CREATE TABLE ORGANIZATIONS_ADDRESSES
    	(`org_id` int, `addr_id` int)
    ;
    	
    INSERT INTO ORGANIZATIONS_ADDRESSES
    	(`org_id`, `addr_id`)
    VALUES
    	(3, 1),
    	(3, 3),
    	(3, 5),
    	(1, 2),
    	(2, 4)
    ;
    
    CREATE TABLE ADDRESSES
    	(`addr_id` int, `addr_city` varchar(9))
    ;
    	
    INSERT INTO ADDRESSES
    	(`addr_id`, `addr_city`)
    VALUES
    	(1, 'London'),
    	(2, 'Melbourne'),
    	(3, 'Milan'),
    	(4, 'New York'),
    	(5, 'Paris')
    ;

**Query 1**:

    SELECT
            a.org_id
          , a.org_name
          , b.addr_city
          , b.addr_id
          , c.addr_id
    FROM organizations a
            INNER JOIN organizations_addresses c
                    ON a.org_id = c.org_id
            INNER JOIN addresses b
                    ON b.addr_id = c.addr_id
    /*WHERE a.client_id = 2*/
    ORDER BY
            b.addr_city

**[Results][2]**:
    
    | ORG_ID |      ORG_NAME | ADDR_CITY | ADDR_ID |
    |--------|---------------|-----------|---------|
    |      3 |     Acme Corp |    London |       1 |
    |      1 | Zebra Limited | Melbourne |       2 |
    |      3 |     Acme Corp |     Milan |       3 |
    |      2 | Charlie Delta |  New York |       4 |
    |      3 |     Acme Corp |     Paris |       5 |



  [1]: http://sqlfiddle.com/#!9/6f5e1/2 

Open in new window

0
 

Author Closing Comment

by:centerforward
Comment Utility
WOW 'bigdogdman' perfect fix right off the bat, THANK YOU.  I swear I tried everything but I didn't really know what I was doing.  So the "GROUP BY" basically means limit to 1 of each org_name "group type"?  ... Anyway it works perfect, thank you !!

---

Icohan and PortletPaul thanks so much for your time as well, I hadn't scrolled enough I didn't realize others had tried to help - I think "distinct" could get me somewhere yes I just don't fully understand the syntax well enough to use it properly so needed someone just to spell it out.  PP - gotcha on the "repetition" -- it's true, it's giving me dups because there are more than one address, I was asking though how to just scrap the dups and limit to 1.  From google searching and reading all these people trying to help each other, I think the problem is usually just originally communicating enough about the tables that are being started from to actually fully help, I tried to hit the sweet spot on that but maybe didn't provide enough context, anyway, bigdogman just got what I meant on the first pass.  I appreciate everyone's time and help very much
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now