+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: help with a mysql sql statement (/showthread.php?tid=19506)
help with a mysql sql statement - El Forum - 06-09-2009
[eluser]megabyte[/eluser]
Two seperate queries
SELECT ad_listing.* FROM ad_listing, ad_listing_categories WHERE ad_listing.active = 1 AND ad_listing.CityID = 192 AND ad_listing.AdID = ad_listing_categories.AdID
SELECT ad_listing_cities.* FROM ad_listing_cities, ad_listing_categories, ad_listing WHERE ad_listing.active = 1 AND ad_listing_cities.CityID = 192 AND ad_listing_cities.AdID = ad_listing_categories.AdID group by ad_listing_cities.CityID
For the life of me cannot write them as a single statement.
Can someone help?
help with a mysql sql statement - El Forum - 06-09-2009
[eluser]darkhouse[/eluser]
Untested, but what about this:
SELECT l.*, ci.*
FROM ad_listing l
JOIN ad_listing_categories ca ON l.AdID = ca.AdID
JOIN ad_listing_cities ci ON l.AdID = ci.AdID
WHERE l.active = 1 AND ci.CityID = 192
GROUP BY ci.CityID
help with a mysql sql statement - El Forum - 06-10-2009
[eluser]megabyte[/eluser]
thanks for trying, it only returned 1 result though. which would tell me its getting results from ad_listing_cities and not from both.
Basically what i wanna do is get all ad_listing results where cityID in ad_listing = 192 or cityID in ad_lisitng_cities = 192
I'm dealing with an existing db, or i wouldntbe having these issues, lol
help with a mysql sql statement - El Forum - 06-10-2009
[eluser]darkhouse[/eluser]
Ok, how about this:
SELECT l.*, ci.*
FROM ad_listing l
JOIN ad_listing_categories ca ON l.AdID = ca.AdID --do you even need this?
LEFT JOIN ad_listing_cities ci ON l.AdID = ci.AdID
WHERE l.active = 1 AND (ci.CityID = 192 OR l.CityID = 192)
You don't need to group by CityID because there's only 1, so that's why you'll only get 1 result.
help with a mysql sql statement - El Forum - 06-10-2009
[eluser]megabyte[/eluser]
SELECT l.*, ci.*
FROM ad_listing l
JOIN ad_listing_categories ca ON l.AdID = ca.AdID LEFT JOIN ad_listing_cities ci ON l.AdID = ci.AdID
WHERE l.active = 1 AND (ci.CityID = 192 OR l.CityID = 192)
this worked. I removed the group by at the end.
Now i just have to learn from it so i can do it myself next time.
So my question would be why do you use a join vs a left join?
I've searched the web for join tutorials, but none of them ever explain it well enough.
much thanks.
help with a mysql sql statement - El Forum - 06-10-2009
[eluser]darkhouse[/eluser]
No worries. I prefer doing joins that way, it helps me see exactly what's going on.
help with a mysql sql statement - El Forum - 06-10-2009
[eluser]megabyte[/eluser]
Last question!
So the query returns 30 rows
29 have the cityid from one table, 1 has the city id from the other table, so inthe result set there are two columns called cityid.
is there a way inside the query to combine the columns into 1?
That way their is only 1 cityid, and every row has a value.
help with a mysql sql statement - El Forum - 06-10-2009
[eluser]darkhouse[/eluser]
There is a way, but I can't remember what the command is. Actually someone on here had that issue, and I believe the answer is there too.
help with a mysql sql statement - El Forum - 06-10-2009
[eluser]megabyte[/eluser]
I figured it out. i just did a select as. you can also use an if statment in certain cases.
My last question for you, resources to learn about joins? got any good ones?
help with a mysql sql statement - El Forum - 06-10-2009
[eluser]TheFuzzy0ne[/eluser]
I loved this tutorial - http://www.keithjbrown.co.uk/vworks/mysql/