Solved

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

Posted on 2013-11-04
5
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39622179
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
ID: 39622381
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 49

Expert Comment

by:PortletPaul
ID: 39622768
>>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 49

Expert Comment

by:PortletPaul
ID: 39623044
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
ID: 39623377
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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

622 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