INNER JOIN returns single record. What to use to return multiple records?

The issue has to do with the LOGOS table that has 3 columns - logo_id, business_location_content_id_fk and logo_timestamp.
There are multiple results when searching on LOGOS.business_location_content_id_fk, each record returning a unique LOGOS.logo_timestamp.
In the query above, I need to return the highest LOGOS.logo_timestamp value (most recent unix timestamp) for the LOGOS.business_location_content_id_fk being searched on.

Alternatively, if the query returned ALL of the LOGOS.logo_timestamp values for the LOGOS.business_location_content_id_fk being searched on, (there will not be many), I could sort for the highest value downstream using PHP... but I would prefer not to :-)

Currently, the above query returns just one record, and it is the lowest LOGOS.logo_timestamp value (which also corresponds with the lowest auto incrementing logo_id PK)

Help with the query would be appreciated.

regards,

GJ



$q_Result_Number = "SELECT
		  search.search_entry
		, business_location.business_location_vpid AS bID
		, business_location.business_location_package_type AS pack_type
		, LOGOS.logo_timestamp as logo_name
		, keyphrases.keyphrase_type AS key_type ".

	"FROM search ".

		"INNER JOIN business_location ON search.business_location_vpid_fk = business_location.business_location_vpid ".

		"INNER JOIN keyphrases ON business_location.business_location_content_id = keyphrases.business_location_content_id_fk AND keyphrases.keyphrase_words IN ('" . $myQuery . "') ".

		"INNER JOIN LOGOS ON business_location.business_location_content_id = LOGOS.business_location_content_id_fk ".

	"WHERE search.search_show = 1 ".

		"AND keyphrases.keyphrase_type IN ('1', '2', '3') ".

		"AND business_location.business_location_package_type BETWEEN 2 AND 5 ".

		"GROUP BY business_location.business_location_content_id ".

		"ORDER BY pack_type DESC";

Open in new window

ggjonesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
What if you add MAX(LOGOS.logo_timestamp) to your fields?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ggjonesAuthor Commented:
... thanks for replying Julian

tried :

MAX(vp_logos.logo_timestamp) as logo_name

in the SELECT, but the result is the same.

regards,

GJ
0
Ray PaseurCommented:
Try using EXPLAIN SELECT to see what the query is doing.
http://dev.mysql.com/doc/refman/5.0/en/explain.html

You may find that you want to adjust the GROUP clause.
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Julian HansenCommented:
Can I clarify something.

When you say one record is returned do you mean in total for the whole query or do you mean one record per business_location.business_location_content_id
0
Dale FyeCommented:
INNER joins will only return those records where there is a match on the join fields.

If you want to include all the values from one table, and the matching values (or NULLs) from a second table, then you would use an Outer Join.

You might want to start by changing:

INNER JOIN

to

LEFT JOIN

for one of your joins and see if that gets you where you want.  However, that might return a lot more records than you want.  You may need a combination of INNER JOINs and LEFT JOINs to achieve the results you are looking for.
0
ggjonesAuthor Commented:
Hi Julian..

re: "When you say one record is returned do you mean in total for the whole query or do you mean one record per business_location.business_location_content_id"

I mean that rather than returning ALL of the LOGOS.logo_timestamp records for LOGOS.business_location_content_id_fk, it returns only the first.

So the objective is to either return just the LAST (preferably), or ALL of them, in which case I can then sort them afterwards.

Does that make sense to you?

GJ
0
Ray PaseurCommented:
You should be able to use ORDER BY to let the SQL engine return the last.  How do you define "last?"
0
ggjonesAuthor Commented:
Thanks for the reply Ray... however cryptic!  :-)

RE:  "You may find that you want to adjust the GROUP clause. "


... are you implying that it's a problem as it exists, or a possible solution to what I am trying to achieve here??

regards,

GJ
0
ggjonesAuthor Commented:
Hi Ray...

RE:  "You should be able to use ORDER BY to let the SQL engine return the last.  How do you define "last?" "

The LOGOS.logo_timestamp column contains ascending unix timestamps. "Last" means the most recent, or largest integer.

many thanks,

GJ
0
ggjonesAuthor Commented:
whoa Julian... I totally messed up.

I had modified the wrong version of the script with your suggestion.

Once I realized that and reloaded, I received the result that I wanted.

I'm really sorry to have misled you  :-(
0
ggjonesAuthor Commented:
As always, I appreciate your patience and generosity!

regards,

GJ
0
Julian HansenCommented:
No problem - you are welcome and thanks for the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.