SQL Count (*) doesn't display the correct data

Hello I have got the problem with my query and it display the correct data but not correct amount (count (*)). Somehow it should display the data as 1 for that part but it display 4. I don't know where is the error is.

select Vehical_Type, 
       Vehical_Registration_No, 
       count(*) 
from van, 
     van_booking 
where Vehical_Type = 'Ford - Transit' 
  and Vehical_Registration_No <> fk2_Vehical_Registration_No 
         not in (select fk2_Vehical_Registration_No 
                 from van_booking 
                 where '2010.10.12' between Hire_Start_Date and Hire_End_Date 
                    or '2010.10.11' between Hire_Start开发者_运维问答_Date and Hire_End_Date);

Please can anyone help me?

Note that count (*) will display count including NULLS where as count(fieledName) will display count without NULLS. You should go with appropriate option.

Assuming your DBMS allows the absence of the GROUP BY and the invalid expression with the NOT IN is just a copy & paste error, I'd say your problem is the missing join.

You don't join van to van_booking so the statement generates a cartesian product of those tables. Meaning every row from van is joined to every row from van_booking which is most probably not what you want.

I guess you will want something like:

FROM van
  JOIN van_booking ON van.id = van_booking.van_id

To properly join those two tables.

Where do we start?

select Vehical_Type, Vehical_Registration_No, count(*)
from van, van_booking
where Vehical_Type = 'Ford - Transit'
  and Vehical_Registration_No <> fk2_Vehical_Registration_No
**not in (**
  select fk2_Vehical_Registration_No
  from van_booking
  where '2010.10.12' between Hire_Start_Date and Hire_End_Date
     or '2010.10.11' between Hire_Start_Date and Hire_End_Date);

Since there is no "AND " before the NOT IN, so

1. there is no context as to whether it should be right or wrong
2. the query won't even run

At least try to post a working query since you seem to indicate it works (and returns count=>4).

Secondly, please alias the table names so that you can prefix the columns with the alias names. At present, it is ambiguous as to which columns belong to which tables.

Thirdly, I guess this is MySQL? You should always state which DBMS you are working with, but MySQL is the only one that will let you get away with MIXING aggregate and non-aggregate fields WITHOUT a GROUP BY clause.

Fourthly, please try to learn to use SQL92 ANSI joins instead of the old-style cross-join and filter-in-WHERE. It looks like you have crossed Van and Van_Booking tables without any correlation clause. What are you trying to achieve?

Lastly, and this ties to the last point, it would greatly help to list out some sample data rows and what the result should look like based on that sample data.

I am going to guess that what you are actually after is a list of vehicle registration numbers of the type requested, but at the same time, in the COUNT column, show... the total number of records matching the query? So, something like this?

Type | Registration | Count
Ford - Transit | ABC123 | 4
Ford - Transit | D4 | 4
Ford - Transit | XY13 | 4
Ford - Transit | PQS333 | 4

It's just a guess, but without the count, you can use this

select Vehical_Type, Vehical_Registration_No
from van
where Vehical_Type = 'Ford - Transit'
  and Vehical_Registration_No not in (
  select fk2_Vehical_Registration_No
  from van_booking
  where '2010.10.12' between Hire_Start_Date and Hire_End_Date
     or '2010.10.11' between Hire_Start_Date and Hire_End_Date);

To include the count (which would be the same value in all rows), you can duplicate the query as a subquery to itself

select Vehical_Type, Vehical_Registration_No, C.C
from van
cross join (
    select COUNT(*) C
    from van
    where Vehical_Type = 'Ford - Transit'
      and Vehical_Registration_No not in (
      select fk2_Vehical_Registration_No
      from van_booking
      where '2010.10.12' between Hire_Start_Date and Hire_End_Date
         or '2010.10.11' between Hire_Start_Date and Hire_End_Date)) C
where Vehical_Type = 'Ford - Transit'
  and Vehical_Registration_No not in (
  select fk2_Vehical_Registration_No
  from van_booking
  where '2010.10.12' between Hire_Start_Date and Hire_End_Date
     or '2010.10.11' between Hire_Start_Date and Hire_End_Date);