CODEWITHSUNDEEP
phpmysqlsql
Tony
Tony

Reputation: 25

Selecting data that appears on 2 tables in MySQL

I’m trying to figure out how to select data only if it appears in two different tables. Here’s the table structure:

Here is the clientInfo table:

--------------------------------------------------
| Name | Region ID  | other info                 |
--------------------------------------------------
| Bob  |    12      | likes cookies              |
--------------------------------------------------
| Joe  |     20     | scuba diver                |
--------------------------------------------------

Here is the regions table:

----------------------------------------
| Location   | Region ID  | Published  |
----------------------------------------
| California |    12      | 1          |
----------------------------------------
| Oregon     |     4      | 1          |
----------------------------------------
| Washington |     8      | 0          |
----------------------------------------
| Colorado   |    20      | 1          |
----------------------------------------

I want to use a query to make a selection via a pull down menu that selects a state only if it’s published AND a client belongs to one of those states.

Edit: In my rush yesterday, I realized that I didn't make my question fully clear. I wanted to be able to have a pull down menu of the states to be populated as a search parameter. So based on the data above, the web page's pull down menu would populate the pull down menu according to which states actually had clients that belonged to them.

------------------------------     -------------
| Choose State to Search | ▼ |     |  Search   |
------------------------------     -------------
|    California              |
|    Colorado                |
------------------------------

How would I word this mysql query statement?

Upvotes: 0

Views: 36

Answers (4)

Tony
Tony

Reputation: 25

After a bit of researching once you guys let me know of LEFT JOIN and INNER JOIN, this is what ended up working for me 

mysql_query(SELECT DISTINCT regions.location FROM regions INNER JOIN clientInfo ON regions.region_id=clients.region_id)

This ended up displaying the region_id only once (DISTINCT) regardless of how many times it showed up on the clientInfo table.

Thank you everyone for all your help!

Upvotes: 0

Giacomo1968
Giacomo1968

Reputation: 26056

Easy. Use a LEFT JOIN like so:

SELECT Name, Location, Published
FROM clientInfo ci
LEFT JOIN regions reg ON (ci.Region_ID = reg.Region_ID)
;

I am assuming your column titled Region ID is actually Region_ID since I can’t imagine that having an empty space in it.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would use exists:

select r.*
from regions r
where r.published = 1 and
      exists (select 1 from clientinfo ci where r.region_id = ci.region_id);

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

You can use EXISTS for this:

SELECT location
FROM regions r
WHERE published = 1
    AND EXISTS (
        SELECT 1
        FROM clientInfo c
        WHERE r.RegionId = c.RegionId)

You can also achieve the same result with a JOIN or IN, but I prefer the way this reads.

Upvotes: 1

Related Questions