Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Question Unanswered: Noob question about FKs

    Hi all!

    I'm having some trouble with a MySQL query. I have two tables: the A table containing clients' information and the B table containing information about cities. A client may have been linked to a city in the B table through a foreign key linking the B row's index to a field in A; however, some clients don't have a linked city. My problem is that I want to retrieve the list of all the clients and, in the cases where they are linked to a city, I want the city name too. The problem is that, through the following query, clients who aren't linked to a city doesn't get retrieved:

    SELECT cli.name, cit.nam
    FROM clients cli, cities cit
    WHERE cli.city = cit.id;

    To see an example, let's say I have 3 clients and 2 cities:


    CLIENTS
    -------------------
    NAME | CITY
    -------------------
    James - 1
    John - NULL
    Joseph - 2
    -------------------

    CITIES
    ---------------------
    ID | NAME
    ---------------------
    1 - San Francisco
    2 - Los Angeles
    ---------------------

    When I run the previous query, I just get 'James' and 'Joseph', but I want to see also John. I've tried adding an 'OR cli.city IS NULL' in the WHERE clause, but then 'John' appears twice (once per city)...

    Does anybody knows how can this be solved?

    Thanks in advance for your time!

  2. #2
    Join Date
    Feb 2013
    Posts
    2
    Solved with a LEFT JOIN! If anyone encounters the same problem, it can be solved with the following query:

    SELECT cli.name, cit.name
    FROM clients cli
    LEFT JOIN cities cit
    ON cit.id = cli.city;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •