Re: creating view with user variable

On 1/23/2017 12:50 PM, David wrote:
> Hi guys,
>
> Can anyone tell me if its possible to create a view with a where clause linking to a variable which is passed by the user.
>
> Something like:
> CREATE VIEW test As
> SELECT * FROM userAccount
> LEFT JOIN userDetails On userAccount.ID = userDetails.UID
> WHERE userAccount.ID = {variable}
>
>
> Then in the user accesible pages, it would be called by
>
> SELECT * FROM test WHERE (but here is where I get stuck)
>
> I have found a few articles knocking around which suggest creating a function and then calling the function by the user instead of calling the view , but all examples I tried this always through up errors.
>
> Would be most grateful if someone could point me in the right direction.
>
> Dave.
>

Dave,

You would put the WHERE clause in your SELECT statement, not in the CREATE VIEW, i.e.

CREATE VIEW test As
  SELECT * FROM userAccount
  LEFT JOIN userDetails On userAccount.ID = userDetails.UID;

SELECT * FROM test WHERE ID = {variable}

However, generally it's better to specify the individual columns instead of *, and is required if you have duplicate column IDs.

But I'm not sure what you're trying to accomplish here. You're JOINING to userDetails, but not selecting any columns from it. Which brings up the question - what are you REALLY trying to do?

-- 
==================
[Quoted] [Quoted] Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================