Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

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

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
centerforward
Asked:
centerforward
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
Derek JensenCommented:
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
 
PortletPaulCommented:
>>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
 
PortletPaulCommented:
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
 
centerforwardAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now