Query Help 2

[zinja]

I am trying to pull all the field from the properties table, but no fields from the users table where the currently logged in user has the same company_name as the user that is attached to the property. I have a field called userid in the properties table which relates to the id field in the users table. company_name doesn't exist in the properties table. Here is my current sql:

SELECT * FROM pmr_properties p, pmr_users u WHERE p.sold IS NULL OR p.sold = 0 AND p.active = 1 AND p.userid=u.id AND u.company_name = 'Coldwell Banker Tatman Realtors'

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[feherke]

Hi

I see you composed the query, so what is wrong ?

Just that you want "all the field from the properties table, but no fields from the users table" ?

SELECT [red]p.[/red]* FROM pmr_properties p, pmr_users u WHERE p.sold IS NULL OR p.sold = 0 AND p.active = 1 AND p.userid=u.id AND u.company_name = 'Coldwell Banker Tatman Realtors'

Or have problem dealing with operator precedence ?

SELECT * FROM pmr_properties p, pmr_users u WHERE [red]([/red]p.sold IS NULL OR p.sold = 0[red])[/red] AND p.active = 1 AND p.userid=u.id AND u.company_name = 'Coldwell Banker Tatman Realtors'

In the later case I would prefer using the [tt]coalesce()[/tt] function :

SELECT * FROM pmr_properties p, pmr_users u WHERE [red]coalesce([/red]p.sold[red],0)[/red] = 0 AND p.active = 1 AND p.userid=u.id AND u.company_name = 'Coldwell Banker Tatman Realtors'

Feherke.

http://rootshell.be/~feherke/

 

[r937]

excellent answer, deserves a star

r937.com | rudy.ca

 

[zinja]

I agree, thanks for your great post and explaining what I was doing wrong. Here is another star.


LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[feherke]

Hi

Thank you. Rudy, a star from you worth two for me.

Feherke.

http://rootshell.be/~feherke/