LEFT JOIN problem

[sniperscope]

Hi

I have join problem again.  :'(

 

Lets say i have a TABLE_A and TABLE_B

I want to get all data from TABLE_A but sort by TABLE_B

I have and sql query but it returns all data from TABLE_A but repeat it number of TABLE_B

I know there is a simple but i cannot see it (i didn't sleep more than 50 hours maybe because of that)

My query is :

SELECT * FROM shop s LEFT JOIN search_up ss ON s.id = ss.shop_id WHERE s.shop_region = 'kansai' AND s.publication = 'dsp' AND (s.shop_area = 'osaka') ORDER BY ss.add_date DESC LIMIT 0,5 

 

Thanks for any help.

[trq]

Don't use SELECT *, be specific about that data that you want.

 

[sniperscope]

Dear thorpe

I used SELECT * because i need all fields (There is about 34 fields in TABLE_A but there is only 2 fields in TABLE_B shop_id and add_date)

[trq]

Then I don't understand the problem.

[sniperscope]

I want to get all data from table_a but sort it by add_date field in table_b

[trq]

So what is the issue?

[sniperscope]

the issue is

query grab data from TABLE_A but repeat same data number of TABLE_B

 

For example:

I have 5 records in TABLE_B and i want to get phpfreaks.com, amazon.com, google.com, yahoo.com data from TABLE_A

This query brings me phpfreaks.com, amazon.com, google.com, yahoo.com 5 times

 

All i want is get data from TABLE_A and find out which data has The Latest entry.

[awjudd]

SELECT * FROM shop s
LEFT JOIN ( SELECT ss.shop_id, MAX(add_date) AS add_date FROM search_up ss GROUP BY shop_id ) ss ON s.id = ss.shop_id
WHERE s.shop_region = 'kansai' AND s.publication = 'dsp' AND (s.shop_area = 'osaka')
ORDER BY ss.add_date DESC LIMIT 0,5

 

Please Note: You should never use SELECT * even if there are a large number of fields.

 

~awjudd

[sniperscope]

SELECT * FROM shop s
LEFT JOIN ( SELECT ss.shop_id, MAX(add_date) AS add_date FROM search_up ss GROUP BY shop_id ) ss ON s.id = ss.shop_id
WHERE s.shop_region = 'kansai' AND s.publication = 'dsp' AND (s.shop_area = 'osaka')
ORDER BY ss.add_date DESC LIMIT 0,5

 

Please Note: You should never use SELECT * even if there are a large number of fields.

 

~awjudd

 

Marvelous.

It is working now. Thank you so much for help.

 

Have a great day

[sniperscope]

SELECT * FROM shop s
LEFT JOIN ( SELECT ss.shop_id, MAX(add_date) AS add_date FROM search_up ss GROUP BY shop_id ) ss ON s.id = ss.shop_id
WHERE s.shop_region = 'kansai' AND s.publication = 'dsp' AND (s.shop_area = 'osaka')
ORDER BY ss.add_date DESC LIMIT 0,5

 

Please Note: You should never use SELECT * even if there are a large number of fields.

 

~awjudd

 

Dear awjudd

Thanks for your code. It was worked.

But, i would like to ask that what if i want to sort it by two columns.

 

I changed your query as below but it fails.

Here is what i changed.

 

PS: I took your advice and used column name in query

 

"SELECT 
			s.id, 
			s.copy, 
			s.name, 
			s.type, 
			s.address_short, 
			s.access, 
			s.salary, 
			s.qualification, 
			s.staff_only, 
			s.opstions, 
			s.picture, 
			s.main_txt, , 
			ss.add_time, 
			ss.add_date 
		FROM shops s 
		LEFT JOIN ( SELECT ss.shop_id, MAX(add_time), AS add_time MAX(add_date) AS add_date FROM search_up ss GROUP BY shop_id ) ss 
		ON s.id = ss.shop_id
		WHERE s.shop_region = 'kansai' AND s.publication = 'dsp' AND (s.shop_area = 'osaka')"

[trq]

ORDER BY takes more than a single field.

[sniperscope]

Dear Thorpe

After editing my own post and while waiting reply.

I tried to solve by myself. i solved but i was unable to edit/delete my own post.

So, namely problem solved.