Filtering?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Filtering?

Post by xterra »

Hello,
Was wondering if you all wouldn't mind taking a look at something. This is my table:


[FK] [FK2]
1 1
2 1
3 1
1 2
2 2
3 2


Wellllllllll I would like to be able to select all where Fk2= 1 AND 2, but then i will have duplicates from the FK column. Can I make the results look like this?

1 1
2 1
3 1


I'm lost:(
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Maybe try looking up the UNIQUE keyword (assuming you are using MySQL).
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

superdezign wrote:Maybe try looking up the UNIQUE keyword (assuming you are using MySQL).

I took your advice and found the DISTINCT function. But, how can I run a query ON a query? So I select * from FK, and THEN run the distinct on the results???
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

SELECT DISTINCT..
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

Jcart wrote:

Code: Select all

SELECT DISTINCT..
Right but how can I do that on a result from another query?

Filtering twice, I guess.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You're not running two queries. You are selecting DISTINCT, which means that the query will only return one value then move on.

PS | Moved to databases.
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

Everah wrote:You're not running two queries. You are selecting DISTINCT, which means that the query will only return one value then move on.

PS | Moved to databases.
But when I put it into one query I am getting empty results.

select DISTINCT * from `applications` where `FK2` ='1' AND `FK2` = '2'



It *should* return at first this:

1
2
3
1
2
3

But then distinct should make it

1
2
3
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Distinct will act on a field name.

Code: Select all

SELECT DISTINCT(`FK1`) FROM `applications` WHERE `FK2` <= 2;
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

Everah wrote:Distinct will act on a field name.

Code: Select all

SELECT DISTINCT(`FK1`) FROM `applications` WHERE `FK2` <= 2;
I apologize, please bare with me I appreciate your help.

That would be fine but the last condition may be different. Is there a way I can change it where instead of WHERE `Fk2`<=2, could I make it something like:

WHERE `FK2` = '1' AND '2' AND '3' ?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

.. WHERE `FK2` IN (1,2,3)
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

Jcart wrote:

Code: Select all

.. WHERE `FK2` IN (1,2,3)
Wow. It works.

Thank you so much.
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

And thank you as well Everah
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You're welcome.
Post Reply