If Statement in mysql

[deadlyp99]

So, I've never had to use an IF statement in mysql before, but a recent project forced me to learn the JOIN function, which causes me an error trying to pull a few types of information from the same table

 

As far as I know I have the syntax wrong... but I can't figure out what it needs to be, because an IF statement in mysql is just a really foreign thing for me.

SELECT * FROM MapSquares
IF MapType > 0
THEN JOIN Cities ON MapSquares.SquareID = Cities.SquareID
ELSEIF OasisType > 0
THEN JOIN Oasises ON MapSquares.SquareID = Oasises.SquareID
ENDIF
WHERE X = '39' AND Y = '-4'

 

Any help would be appreciated... IF statements in sql are a rather new concept to me, and I'm very unsure of the context it needs to be in.

I'm trying to have the sql join 2 tables based on whether it has a 'MapType' or an 'OasisType'.

The use of 0 basically is going to mean "no it doesn't have this type", and not 1 row has both types, but it will have 1 of the two.

 

Thanks in advance for any advice

[suresh64633]

You can not use IF statement before joins. You can use them while selecting fields from the table or in where clause.

[deadlyp99]

So I have to use nested sql statements in my php to achieve what I am trying to, or is there a different route I can go?

[suresh64633]

You can try this:

SELECT IF (MapType > 0,
	 SelectFields ,
    	IF (OasisType > 0!='',If_True_Select_Fields,If_False_Select_Fields)
    
   )
FROM MapSquares
LEFT JOIN Cities ON MapSquares.SquareID = Cities.SquareID
LEFT JOIN Oasises ON MapSquares.SquareID = Oasises.SquareID

WHERE X = '39' AND Y = '-4'

[deadlyp99]

Is there a way I would be able to have it select fields from other tables?

 

SELECT IF (MapType > 0,
       SelectFields ,
          IF (OasisType > 0!='',Oasises.OasisID,Cities.CityID)
      
      )
FROM MapSquares
WHERE X = '39' AND Y = '-4'

[suresh64633]

yes you can.

Use join in your query. Like

SELECT IF (MapType > 0,
       SelectFields ,
          IF (OasisType > 0!='',If_True_Select_Fields,If_False_Select_Fields)
      
      )
FROM MapSquares
   LEFT JOIN Cities ON MapSquares.SquareID = Cities.SquareID
   LEFT JOIN Oasises ON MapSquares.SquareID = Oasises.SquareID
   
WHERE X = '39' AND Y = '-4'

 

 

[deadlyp99]

Well my question just turned my goal into a monster I wasn't trying to communicate

 

To be more clear:

One or the other join needs to be run per query, based on the MapType and Oasis Type.

Which if you re-look at my code with that in mind:

 

SELECT * FROM MapSquares
IF MapType > 0
   THEN JOIN Cities ON MapSquares.SquareID = Cities.SquareID
ELSEIF OasisType > 0
   THEN JOIN Oasises ON MapSquares.SquareID = Oasises.SquareID
ENDIF
WHERE X = '39' AND Y = '-4'

 

That's my ultimate goal in this. I'm hoping to be able to shorten my code. If it is not possible, then thanks for your time, i'll just use php

[jj20051]

I'd like to use this to shrten my code as well, does anyone know what he is doing wrong, so that I can fix it and copy it?

[kickstart]

Hi

 

Think the solution suresh64633 came up with is pretty elegant and short from an SQL perspective. However if you know in advance which join you want then it would appear best to set up 2 seperate SELECTs and pass the relevant one from php when required.

 

All the best

 

Keith