Mysql case left jooin

[Krik]

I need my select query to look at one of the values in my table and based on the value left join another table.

I have been looking in the MySQL Manual for more information and based on that I believe that the "case" function is what I should use but I am not getting it to work. And really their manual is very difficult to navigate if so if they have a more relevant section I have not been able to find it.

$custom_qry = "
	SELECT * 
	FROM `Custom_Pages`
		CASE `Custom_Pages`.`City_State` 
		WHEN 'city' 
		THEN LEFT JOIN Cities ON Cities.record_number = Custom_Pages.Location
		WHEN 'state' 
		THEN LEFT JOIN States ON States.record_number = Custom_Pages.Location
		END
	";

So what am I missing?

[mpb001]

I am not convinced MySql will let you do that like that... A join is fundementally a where clause and you are using techniques that are part of the column select.

You could try something like

 SELECT  *
    FROM `Custom_Pages`
        CASE `Custom_Pages`.`City_State` 
		LEFT JOIN Cities ON (  City_State = 'city'  AND Cities.record_number = Custom_Pages.Location ) 
        LEFT JOIN States ON (  City_State = 'state' AND States.record_number = Custom_Pages.Location )

I don't do much MySQL any more but it is valid SQL. I remember this working in MySQL in versions above 4 ( including 4.1 ). Earlier versions would just ignore it. But that will give you at least 1 extra column

Though you are probably better off doing a union, the columns will have to match( or their aliases ) in both statements so you are better of not doing SELECT . Actually you are probably better of never doing a SELECT except when wrapping a UNION.

 SELECT  *
    FROM `Custom_Pages`
	JOIN Cities ON (  City_State = 'city'  AND Cities.record_number = Custom_Pages.Location ) 
  UNION
  SELECT  *
    FROM `Custom_Pages`
    JOIN States ON (  City_State = 'state' AND States.record_number = Custom_Pages.Location )

If you want to order it you have to wrap it.

SELECT * FROM
(
 SELECT  *
    FROM `Custom_Pages`
	JOIN Cities ON (  City_State = 'city'  AND Cities.record_number = Custom_Pages.Location ) 
  UNION
  SELECT  *
    FROM `Custom_Pages`
    JOIN States ON (  City_State = 'state' AND States.record_number = Custom_Pages.Location )
) AS RESULTS /* You need an alias as you are making a table set ) */
ORDER BY City_State

I can't test it but that is the gist.

[Krik]

A union is nice idea except the States table and the Cities table are very different in the what is stored in each column.

I guess I could make is so both have the same number of columns and then create an addition table for each that has the remaining particular data for each. And then maybe left join that data later in the query.

For that to work I think I need to figure out how that "AS" works. Anybody know good source of documentation, the MySQL site is not going to cut it.

[Krik]

Ok W3schools.com has bit of a SQL manual but between it the MySQL manual and some past projects and redoing the database table I think I have it.

Here the finished query

	SELECT * FROM
	(
		SELECT  Cities.record_number AS Ctrn, Cities.City_Name AS Loc_Name, Custom_Pages.* 
		FROM `Custom_Pages`
		JOIN Cities ON ( City_State = 'city' AND Cities.record_number = Custom_Pages.Location )
	UNION
		SELECT States.record_number, States.State_Name, Custom_Pages.*
		FROM `Custom_Pages`
		JOIN States ON ( City_State = 'state' AND States.record_number = Custom_Pages.Location )
	) AS CP
	LEFT JOIN Cities_Data ON ( City_State = 'city' AND CP.Ctrn = Cities_Data.record_number )
	LEFT JOIN States_Data ON ( City_State = 'state' AND CP.Ctrn = States_Data.record_number )
	LEFT JOIN States ON States.record_number = Cities_Data.State
	LEFT JOIN Titles ON Titles.record_number = CP.Title
	LEFT JOIN Descriptions ON Descriptions.record_number = CP.Description
	LEFT JOIN Custom_Info ON Custom_Info.record_number = CP.Custom_Info

May not be the best way to do it but it does work. Suggestions welcome