Yet another MySQL join question! :P

[eluser]megabyte[/eluser]
I didnt build this database but have to try and deal with its issues,


I need to cobine two tables


SELECT AdID, CityID, OrderSequence FROM ad_listing WHERE CityID = 484 AND Active = 1 GROUP BY ad_listing.AdID ORDER BY OrderSequence


SELECT AdID, CityID, OrderSequence FROM ad_listing_cities WHERE CityID = 484 GROUP BY AdID ORDER BY OrderSequence



And then order by OrderSequence and only show id Active = 1

[eluser]darkhouse[/eluser]
I think what you want is a UNION. Try this:

Code:

SELECT AdID, CityID, OrderSequence FROM ad_listing
UNION
SELECT AdID, CityID, OrderSequence FROM ad_listing_cities
WHERE CityID = 484 AND Active = 1
GROUP BY AdID
ORDER BY OrderSequence

[eluser]megabyte[/eluser]
onlythe ad_listingtable has the Active column. i need to remove it to get it to work.

but then it returns 1191 rows, when i only should get 13 if i run each query seperatley.

[eluser]megabyte[/eluser]
Got it, and your helpis what did it.


SELECT AdID, CityID, OrderSequence FROM ad_listing WHERE CityID = 484 AND Active = 1

UNION
SELECT AdID, CityID, OrderSequence FROM ad_listing_cities
WHERE CityID = 484

GROUP BY AdID
ORDER BY OrderSequence





thanks so much.

[eluser]megabyte[/eluser]
K, i spoke to soon.


What if i want to also get a column called "Company" from the ad_listing table.

I tried and got an error saying:

The used SELECT statements have a different number of columns

[eluser]darkhouse[/eluser]
I'm not sure, but maybe you could do this:

SELECT AdID, CityID, OrderSequence, Company FROM ad_listing WHERE CityID = 484 AND Active = 1

UNION
SELECT AdID, CityID, OrderSequence, NULL as Company FROM ad_listing_cities
WHERE CityID = 484

GROUP BY AdID
ORDER BY OrderSequence