help with a mysql sql statement

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

[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

[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

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

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

[eluser]darkhouse[/eluser]
No worries. I prefer doing joins that way, it helps me see exactly what's going on.

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

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

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

[eluser]TheFuzzy0ne[/eluser]
I loved this tutorial - http://www.keithjbrown.co.uk/vworks/mysql/