Distinct/Inner join query

LZ1
LZ1 used Ask the Experts™
on
Hey Experts!!

I have a query that populates a drop down, but I need only unique values.  The link is at the bottom of the post.  

I need to only show unique makes, but I don't know how to do the join.  I've done joins before, but never with MySQL/PHP.  

I'm hoping it's just something I'm missing.

http://aimsmanufacturing.com/dbaxle/start.php
SELECT * 
FROM `lzakoor`.`makes`, `lzakoor`.`models`
INNER JOIN (SELECT DISTINCT makes.make_id 
FROM `lzakoor`.`makes`) as makes
WHERE to_year >=1971
AND from_year <=1971;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi LZ,

There are several variants of the query that will work.  Which one works best is dependent on the data.


Good Luck,
Kent

--
--  Select distinct
--

SELECT distinct a.*, b.*
FROM tableA a
INNER JOIN tableB b
  ON a.key = b.key
WHERE to_year >=1971
AND from_year <=1971;

--
--  Select distinct limiting join size
--

SELECT distinct a.*, b.*
FROM tableA a
INNER JOIN
(
  SELECT * 
  FROM tableB
  WHERE to_year >=1971
    AND from_year <=1971
) b
  ON a.key = b.key;

Open in new window

Most Valuable Expert 2015

Commented:
(no points ...)

>> SELECT *

If you only want distinct "makes" you'll almost certainly need to get rid of the SELECT * since DISTINCT returns unique combinations of _all_ columns in the select list.
LZ1
Top Expert 2011

Author

Commented:
Still kind of confused on how/where to put the tables in the query.  
I want distinct make_id's.  I can match on the outside right?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Most Valuable Expert 2015

Commented:
Yes. But it's not clear which columns belong to which table. Can you give us the relevant column names in each table?

TABLE: makes  COLUMNS:  Make_id, ... ?
TABLE: modelsCOLUMNS:  Make_id, to_year, from_year, .. ?
LZ1
Top Expert 2011

Author

Commented:

Oops, sorry about that.  

Makes Table:
make_id
make
 
Models table:
Model_id
Make_id
From_year
To_year
Model_name
 
The make_id should only display unique values from the makes table.  I hope that explains it
 
Most Valuable Expert 2015
Commented:
Either one of the previous queries or something like

SELECT ma.make_id
FROM    makes ma
WHERE  ma.make_id IN (
         SELECT mo.make_id
         FROM    models mo
         WHERE mo.make_id = ma.make_id
         AND      mo.to_year >=1971
         AND      mo.from_year <=1971
)
Most Valuable Expert 2015

Commented:
... or a 4th option. That's assuming you only want the distinct make_id's. If you need other values as well, try one of the JOINs suggested by @Kdo. Just remove the SELECT *

SELECT ma.make_id
FROM    makes ma
WHERE  EXISTS (
         SELECT mo.make_id
         FROM    models mo
         WHERE mo.make_id = ma.make_id
         AND      mo.to_year >=1971
         AND      mo.from_year <=1971
)
LZ1
Top Expert 2011

Author

Commented:
What are the ma and mo representing in the query?  
LZ1
Top Expert 2011

Author

Commented:
So far so good!  This works.  But I also need the makes.make column which is associated with the makes.make_id.

SELECT ma.make_id
FROM    makes ma
WHERE  EXISTS (
         SELECT mo.make_id
         FROM    models mo
         WHERE mo.make_id = ma.make_id
         AND      mo.to_year >=1971
         AND      mo.from_year <=1971
)

Open in new window

Most Valuable Expert 2011
Top Expert 2016
Commented:
Consider using GROUP BY, as well.
Most Valuable Expert 2015

Commented:
>> What are the ma and mo representing in the query?

They're table aliases.  "ma" is an alias for "makes" and "mo" for "models".  Instead of having to type out the full table name in front of every column, you can use the aliases instead.  But either way is fine.  It's mostly personal preference.

>> But I also need the makes.make column which is associated with the makes.make_id

Just add it to the SELECT list

SELECT ma.make_id, ma.make_id
FROM    makes ma
....etc....


Most Valuable Expert 2015

Commented:
Correction:  That should be "make" instead of "make_id"

SELECT ma.make_id, ma.make
FROM    makes ma
....etc....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial