Using IF/ELSE in MySQL SELECT

Question

I have a table of members and a table of venues. I currently have this select statement:

SELECT VenueName, VenueID 
FROM Venue 
WHERE active='Y' 
ORDER BY VenueName

The result of this statement is used to populate a drop down list with venue names, which works. I'll call it "ORIGINAL SELECT".

I want to change this so the dropdown list only shows venue names linked to the member:

SELECT Venue.VenueName, Venue.VenueID, members.id, members.venueid 
FROM Venue, members 
WHERE Venue.VenueID = members.venueid 
AND members.id='$userid'

This also works. I'll call it "NEW SELECT".

I have two superadmins whose members.venueid is all rather than a venue id number, so I would like to create a statement that runs the NEW SELECT else if members.id='all' then run ORIGINAL SELECT.

Can anyone point me in the right direction? I've tried various things.

Chris Snow · Accepted Answer · 2012-12-01 22:16:38Z

1

You could try something similar to the following:

SELECT Venue.VenueName, Venue.VenueID
FROM Venue, members 
WHERE 
(
  Venue.VenueID = members.venueid
  AND $userid <> 'all' 
  AND members.id = 'all'
)
OR
(
  Venue.VenueID = members.id
  AND $userid = 'all'
  AND members.id = 'all'
)

edited Dec 1, 2012 at 22:16

answered Dec 1, 2012 at 21:41

Chris Snow

24.9k38 gold badges157 silver badges331 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Benn Moffat Over a year ago

Thanks for the help Chris, although unfortunately I can't get this to work. I am trying a few variations on it to see if I can stumble across the correct one!

Benn Moffat Over a year ago

So far I have this:

SELECT Venue.VenueName, Venue.VenueID FROM Venue, members WHERE (  '$userid' =  'all' AND active =  'Y' ) OR ( Venue.VenueID = members.venueid AND members.id =  '$userid' ) ORDER BY VenueName

This appears to work if the $userid in NOT 'all', if it is 'all' it lists the correct VenueName, but for some reason it lists each one 12 times instead of once!

Chris Snow Over a year ago

I'm glad this pointed you in the right direction. The problem where the userid = 'all' is because of a cartesian join between the Venue and Members table. Using DISTINCT is probably ok unless you have lots of records.

Benn Moffat Over a year ago

thankfully there are only a few venues and members so it doesn't slow things up using Distinct. However, in the interests of furthering my limited knowledge, I'm going to look into cartesian joins, because I've never heard of that or have a clue as to what they are!

Benn Moffat Over a year ago

I see what you meant now, and a LEFT JOIN seems to work in place of DISTINCT. Thanks for all the help

Benn Moffat · Accepted Answer · 2012-12-02 12:53:06Z

1

Thanks @Chris you pointed me in the right direction, below is the final version that I got working:

SELECT DISTINCT Venue.VenueName, Venue.VenueID
FROM Venue, members
WHERE (
'$userid' =  'all'
AND active =  'Y'
)
OR (
Venue.VenueID = members.venueid
AND members.id =  '$userid'
)
ORDER BY VenueName

answered Dec 2, 2012 at 12:53

Benn Moffat

578 bronze badges

Collectives™ on Stack Overflow

Using IF/ELSE in MySQL SELECT

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related