-2

Good day, On my db, I have two tables(entities), one is named stall and the other is for clients. On the index page, I made a table(function) that would return the rows of the database entity, stall which foreignkey (stallno) values which are not present in the clients table. For further details, the stallno is the primary key of the table stall and a foreign key on clients.

What I want to display is the number of stalls that is still available or yet to be occupied by clients. So I made a query like this:

SELECT DISTINCT a.stallno, a.location, a.condition, b.stallno 
FROM stall a, clients b WHERE NOT (a.stallno<=>b.stallno);

I have a hundred number of stalls saved on the DB table stall. Right now, several numbers are being taken by the clients, these are 12,11,13,14,20,21,28,29 and 30 (thus was being saved at the clients table as the foreign key stallno) So the once that should be displayed are those numbers which are not in this group. Like for example, Stall numbers: 1,2,10,35 etc (those remaining numbers). That's why I've chose to use the not equal functions/symbols to get what I wanted. First query I've used was this:

SELECT a.stallno,a.location,a.condition, b.stallno 
FROM stall a, clients b WHERE a.stallno!=b.stallno

But this only displayed the entire rows including the values of b.stallno and duplicate it aswell. Not only that, it also display 0 numbers. Which is also part of b.stallno. What I want to display is the remaining number which are not connected with b.stallno. So I tried to use the DISTINCT function instead, on the hopes of omitting the 0 numbers. Which kinda worked, but still it duplicates the numbers (12,11,13,14,20,21,28,29 and 30) and the numbers which are yet to be taken (those apart from the given numbers). What I did is to try another query again. This time, the NOT function.

SELECT DISTINCT a.stallno, a.location, a.condition, b.stallno FROM stall a, tenant b WHERE NOT (a.stallno!=b.stallno)

But still, appears to be not working. even the <> giving me the same results. Until I came across with this <=> symbol. Which is said to have worked as a not equal function. I tried it but with a slight difference to the first few statements. This time all the numbers from a.stallno (which is the PK to DB stall) displays all rows from 1-100.

Now, I came with the conclusion that perhaps these symbols for not equals <=>,!=,<> do not seem to work in DREAMWEAVER, or either PHPandMySQL. What d'you guys think? Do you know something that would call the NOT EQUAL function.

Archangel08
  • 91
  • 10
  • 3
    `<=>` is not _not equal_. It's _null-safe equal_. See http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to – Barmar Aug 02 '14 at 07:46
  • You need to understand how JOINs work a bit better. You are requesting a cartesian product of the two tables (all possible combinations) and then filtering out just a part of the resulting records... and wrongly. Go with Lorenz's advice. You need a LEFT JOIN that just puts in the result matching rows or stalls with no clients... and then filter out the former. There are a lot of good tutorials on JOINs, you should have a look at some. – Frazz Aug 02 '14 at 07:48

1 Answers1

0

The not equal sign in mysql is != . The problem is that you search in the wrong direction.

You should use an left join :

SELECT DISTINCT a.stallno, a.location, a.condition
FROM stall a left join clients b ON a.stallno=b.stallno
WHERE b.stallno is null
Lorenz Meyer
  • 19,166
  • 22
  • 75
  • 121