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.



$q_Result_Number = "SELECT
		, 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

Who is Participating?
Julian HansenConnect With a Mentor Commented:
What if you add MAX(LOGOS.logo_timestamp) to your fields?
ggjonesAuthor Commented:
... thanks for replying Julian

tried :

MAX(vp_logos.logo_timestamp) as logo_name

in the SELECT, but the result is the same.


Ray PaseurConnect With a Mentor Commented:
Try using EXPLAIN SELECT to see what the query is doing.

You may find that you want to adjust the GROUP clause.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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
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:




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.
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?

Ray PaseurCommented:
You should be able to use ORDER BY to let the SQL engine return the last.  How do you define "last?"
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??


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,

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  :-(
ggjonesAuthor Commented:
As always, I appreciate your patience and generosity!


Julian HansenCommented:
No problem - you are welcome and thanks for the points.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.