distinct query mysql, no failed rows

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
tyrohansen
Forum Newbie
Posts: 8
Joined: Fri Apr 06, 2007 3:57 am

distinct query mysql, no failed rows

Post by tyrohansen »

hello out there,
I have this big challenge, i'm looking for a way to query the database table while checking for specific condition using the where phrase. the hard part is, the query is base on the != operator. As to be true it shouldn't be equal. HOwever, i also would like the result to be exclusively,extremely unique and true. what do i mean, several rows have same id for example if a column has a name "ninja" many times and the "ninja" id appears on some row and does not meet the condition, then i want all rows with "ninja" excluded from the result.
I have tried using

Code: Select all

select DISTINCT id, name from table WHERE condition1 !=$con1 AND condition2 !=$con2
i'm using php to query the database.

Code: Select all

DISTINCT
stopped the row from being repeated but it produces rows that have ninja false somewhere.
note: if there is another to do this please let me know. all help will be appreciated :cry:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Why is this is Regex?

Moved to Databases.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

You mean you have some repeating data in some columns, not a identical data rows?

If so, look into a GROUP BY query.
tyrohansen
Forum Newbie
Posts: 8
Joined: Fri Apr 06, 2007 3:57 am

the real thing

Post by tyrohansen »

jayshield
GROUP BY xx does not solve it. Let me let you in what the script or program is supposed to solve. I am programing an online reservation system acording to my design it has three table in its database 1 for customer details, 1 for categories of the rooms, 1 for booking. So i'm supposed to query with php to find a free room in other words this room is not booked on the date X. so in the normal way of querying databases since the these rooms appear in multiple booking say rooms several times, result return the rows of rooms with row that don't have X. However this particular room is booked for this day. failed to solved totally.
If you might know of a way better than my 3 table design, please let me know, if more tables might do the trick.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

tyrohansen wrote:I want to find a way to query the table with condition that once the id is found on a row that does not meet the condition all rows with that id are excluded from the results .... and my conditions are based on != operator
So basically you want to find those ids for which all the rows having that particular id satisfy some condition? I mean for id 'samurai' and condition 'has two swords' samurai would get displayed only if every samurai row has two swords, right?


BTW, crossposting is prohibited, don't do that. Your other thread is removed.

[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:3. Do not make multiple, identical posts. This is viewed as spam and will be deleted.
tyrohansen
Forum Newbie
Posts: 8
Joined: Fri Apr 06, 2007 3:57 am

Post by tyrohansen »

weirdan,
thats more likely what i want. when the condition is not true for that id the that id should not be in the result.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

So lets think about samurai and swords. 'Every samurai has two swords' = 'Number of samurai having two swords is equal to number of all samurai':

Code: Select all

select branch
from warriors
group by branch
having sum(num_of_swords=2) = count(*)
Post Reply