MySql select records

louie

I need to select all records from TBL1 but only if TBL2.FIELD='yes'

These two tables are not and can not be related for use of JOIN, etc.

Any ideas? Banging my head for the last few hours using the IF() statement, etc...

Liam Byrne

You'll need to be clearer on what you are trying to do.

If the 2 tables are "not related", then what bearing does the "yes" have on the query ?

louie

The first table contains a lot of records while the second table (users TBL) has a field that controls the view of the records.
if allowed = "yes" then records will be displayed otherwise contact the Administrator for permission.

I can easily check the page permission before making the second call to the DB, but I though it will be better if I can get all that in just one call...

Liam Byrne

You could do a straightforward select from both, returning the results, but then have the PHP code only display the results if the field is yes.

SELECT * from TBL1, FIELD from TBL2 

if ($results["FIELD"]=="yes") {

}

Not sure if it's "better", though, since the YES/NO field is "disposable" almost immediately.

louie

Not really.

I'll work out something tomorrow. Now I am just enjoying my Southern Comfort drink...
Thanks for your help.

fcrossen

SELECT * from TBL1, FIELD from TBL2

This will not work for you. You will get a cross join where every row from TBL1 is joined with every row from TBL2. You'll get (no. rows in TBL1) * (no. rows in TBL2) results.

Try

SELECT * from TBL1, FIELD from TBL2 GROUP BY TBL1.<primary_key>

You'll only get unique rows from TBL1 that way.

jmcc

Possibly a case for a stored procedure (if using MySQL)? Alternatively a conditional UNION which could be messier than the simple two query solution?

Regards...jmcc

Dr.Silly

select * from Table_1,Table_2
where Table_2.testcol = 'Yes'

jmcc

louie wrote: »

The first table contains a lot of records while the second table (users TBL) has a field that controls the view of the records.
if allowed = "yes" then records will be displayed otherwise contact the Administrator for permission.

I can easily check the page permission before making the second call to the DB, but I though it will be better if I can get all that in just one call...

Checking the permissions in the permissions table sounds like a very low cost query and is probably the best way to do it as only the properly authorised users will then make it through to the more expensive query on the larger table. This will result in a lot of fast low cost queries but fewer expensive queries.

Regards...jmcc

castie

Declare permissions NVARCHAR(10)

SELECT @permissions = Field FROM TBL2 WHERE userid = @currentuserid

IF @permissions = 'yes'
BEGIN

SELECT * FROM TBL1

END



That should do it above I think.
Made the assumption that your looking up the permissions based on a @currentuserid which is passed in.

In your application if the returned record count is 0 then you can produce a message saying contact the administrator.