jennatar77 Posted February 19, 2009 Share Posted February 19, 2009 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. Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/ Share on other sites More sharing options...
Alith7 Posted February 19, 2009 Share Posted February 19, 2009 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. Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/#findComment-766649 Share on other sites More sharing options...
jennatar77 Posted February 20, 2009 Author Share Posted February 20, 2009 That's exactly what I needed! Thanks! Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/#findComment-766682 Share on other sites More sharing options...
Alith7 Posted February 20, 2009 Share Posted February 20, 2009 great! I can't fix my problem, but at least I can help someone else! glad I could help! Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/#findComment-766991 Share on other sites More sharing options...
Mchl Posted February 20, 2009 Share Posted February 20, 2009 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 Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/#findComment-766997 Share on other sites More sharing options...
Alith7 Posted February 20, 2009 Share Posted February 20, 2009 you're right. my skills aren't the greatest. thank you for the correction. Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/#findComment-767025 Share on other sites More sharing options...
Mchl Posted February 20, 2009 Share Posted February 20, 2009 Well, I didn't know that a year ago either. Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/#findComment-767029 Share on other sites More sharing options...
jennatar77 Posted February 20, 2009 Author Share Posted February 20, 2009 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 :-\ Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/#findComment-767169 Share on other sites More sharing options...
fenway Posted February 23, 2009 Share Posted February 23, 2009 Wait... why are storing locN fields in a single table? Link to comment https://forums.phpfreaks.com/topic/146013-selecting-data-from-multiple-tables-with-complicated-where/#findComment-769048 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.