SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: help to correct syntax
-
Dec 28, 2008, 11:06 #1
- Join Date
- Sep 2008
- Location
- Dubai
- Posts
- 971
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
help to correct syntax
Code MySQL:"SELECT a.* , b.thumb_id , c.amount , ( SELECT COUNT(*) FROM listing_bid WHERE listing_id = a.id ) AS c_rows FROM listing a LEFT OUTER JOIN listing_photo b ON b.listing_id = a.id AND b.key = 1 LEFT OUTER JOIN listing_bid c ON c.listing_id = a.id ORDER BY c.id DESC LIMIT 1 WHERE a.activation = 1 ";
I want to get the highest amount so I put the ORDER BY clause in, its error.
So I need help to put the ODER clause correctly in the query.
-
Dec 28, 2008, 16:14 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
write this down somewhere --
SELECT
FROM
[ WHERE ]
[ GROUP BY ]
[ HAVING ]
[ ORDER BY ]
-
Dec 28, 2008, 21:41 #3
- Join Date
- Sep 2008
- Location
- Dubai
- Posts
- 971
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
In the query you have built before. I want one more thing.
Get the highest amount (only one) in table listing_bid, the highest amount will correspond with the the highest listing_bid.id
The possible WHERE clause is WHERE listing_bid.id = a.id
This is what I come up with, but its not working. Please help me fix it!
Code MySQL:"SELECT a.* , b.thumb_id , c1.amount AS amount1 , ( SELECT COUNT(*) FROM listing_bid WHERE listing_id = a.id ) AS c_rows , ( SELECT amount FROM listing_bid WHERE listing_id = a.id ORDER BY id DESC LIMIT 1 ) AS h_amount FROM listing a LEFT OUTER JOIN listing_photo b ON b.listing_id = a.id AND b.key = 1 LEFT OUTER JOIN listing_bid c1 ON c1.listing_id = a.id WHERE a.activation = 1"
-
Dec 28, 2008, 21:49 #4
- Join Date
- Sep 2008
- Location
- Dubai
- Posts
- 971
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So weird! its working now.
But can you tell me if I am doing it absolutely coreectly
Bookmarks