Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-11-04
5
Medium Priority
?
379 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 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

916 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