Selecting data from multiple tables with complicated WHERE

[jennatar77]

First up, thanks for reading. I am using MySQL5.0. Rather than offering the exact code I'm working on, I'm putting up an example of what I want to achieve. I'm hoping someone will point me in the right direction on what command to use to do this (or if it's impossible...).

 

I want to SELECT all rows from table1, but only if table1.uid appears on table2 (table2.uid) with table2.permit=1 (on the same row).

 

Simplified example tables:

table1

uidnamelocation

1JohnFL

2DaveNV

3KentFL

4KellyCA

 

table2

uidpermit

21

42

31

 

I'm currently sitting at...

"SELECT * FROM table1 WHERE table1.uid=table2.uid && table2.permit='1'"

 

However, that doesn't take into account that permit=1 must be on the same row as the matching uid.

 

Ideal output would be

 

2DaveNV

3KentFL

 

(Peeps who appear in table2 and their entry in table 2 has permit=1...)

 

Is there a magic command to do this? I'm an amateur at MySQL/PHP, so any help would be appreciated.

 

[Alith7]

yay subquery fun!

 

let's see if I can remember how to do this...

 

SELECT * FROM table1 WHERE table1.uid IN

(SELECT table2.uid FROM table2 WHERE table2.permit='1')

 

I think that should work.  give it a try.  my coding skills are so-so.

[jennatar77]

That's exactly what I needed! Thanks!

[Alith7]

great!

I can't fix my problem, but at least I can help someone else!

glad I could help!

[Mchl]

SELECT t1.uid, t1.name, t1.location FROM table1 AS t1 INNER JOIN table2 AS t2 USING(uid) WHERE t2.permit = 1

this one should be faster

[Alith7]

you're right.

my skills aren't the greatest.  thank you for the correction.

[Mchl]

Well, I didn't know that a year ago either.

[jennatar77]

Thanks all, I will try that more efficient query.

 

Another related question; would you also use some sort of a subquery for this kind of issue?

 

table1

uidnameloc1loc2loc3

1JohnFLFLNV

2JasonTXFLCA

3KevinNMCOCA

 

Given a specific uid, an array that returns only the columns (keys) where the value=XX. Single table.

 

For instance,

SELECT (WHERE key='FL') FROM table1 WHERE uid='2'

returns

loc2

FL

 

For uid=1 it would return loc1=>FL, loc2=>FL

 

Obviously incorrect, but that's what I'm going for: just select pertinent columns. The ultimate goal would be to print out just the keys for matching columns (eg 'loc1' if that's the only one ='FL'). Or is this something where you would get the entire array and modify it to contain only columns of interest (via PHP)...?

 

Sorry for asking another question on top of that :-\

[fenway]

Wait... why are storing locN fields in a single table?