ok. It looks like you have 4 tables here with nothing to join them together. Perhaps the easiest solution at this point would be to create a mapping table called something like CarRental which would have the columns Carpoolid, cilentsid, dateinid, and dteoutid.
I think the way I would go would be to drop the datein and dateout table and put them on a table called car rental.
CREATE TABLE [dbo].[CarRental](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CarpoolID] [int] NOT NULL,
[ClientID] [int] NOT NULL,
[DateIN] [datetime] NULL,
[DateOut] [datetime] NOT NULL,
CONSTRAINT [PK_CarRental] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CarRental] WITH CHECK ADD CONSTRAINT [FK_CarRental_CarPool] FOREIGN KEY([CarpoolID])
REFERENCES [dbo].[CarPool] ([Id])
GO
ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_CarPool]
GO
ALTER TABLE [dbo].[CarRental] WITH CHECK ADD CONSTRAINT [FK_CarRental_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([Id])
GO
ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_Clients]
GO
Car rental now stores the relationship between the client and the car that they rented and for each record stores the datein and dateout.
If you dont want to script all of this out you can use the table designer to create the table by right clicking on tables and then choosing new table. You can use the database diagram to drag and drop columns in order to create your foreign key relationships by right clicking database diagrams and chosing New Database diagram.
If you use this structure then You would leave the DateIn field as NULL untill the car was returned. Your query to find out which clients and cars have not been returned would be
SELECT carpool.brand, clients.name, clients.lastname
from carrental
join carpool on carpool.id = carrental.carpoolid
join clients on clients.id = carretnal.cientid
where carretnal.datein IS NULL
In this case you dont need to update the records for people who havent returned a car. You only update the record to contain the date when they have returned the car.
UPDATE CarRental
SET Datein = GETDATE()
WHERE Clientid = @CLientid
AND Carpoolid = @Carpoolid
CASE
is an expression that returns a single value. You are attempting to use it as control of flow logic to optionally include a filter, and it doesn't work that way. Maybe something like this would work:
AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
WHEN 0 THEN 25000 ELSE 2000000000 END
-- guessing that 2 billion is enough to always be greater
-- than the highest valid contribution
Expanding to your three conditions, you need to use multiple expressions because you can't use CASE
, again, to change >=
to <
. So something like:
AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
WHEN 0 THEN 25000 ELSE 2000000000 END
AND COALESCE(Project.Contribution, 0) < CASE
@BudgetFilter WHEN 1 THEN 25000 WHEN 2 THEN 0 ELSE -2000000000 END)
Or you can use the OR conditionals as mentioned in the comments:
AND ((@BudgetFilter = 0 AND Project.Contribution >= 25000)
OR (@BudgetFilter IN (1,2) AND Project.Contribution < CASE
@BudgetFilter WHEN 1 THEN 25000 ELSE 0 END))
Or you can just use dynamic SQL, which may be useful if you have a lot of other criteria and/or you are having difficulty getting consistent behavior from the plans for the above variations:
DECLARE @sql NVARCHAR(MAX) = N'SELECT ...
WHERE ...
AND COALESCE(Project.Contribution, 0) '
+ CASE @BudgetFilter
WHEN 0 THEN ' >= 25000'
WHEN 1 THEN ' < 25000'
WHEN 2 THEN ' < 0' END + ';';
EXEC sp_executesql @sql;
(And in case you're wondering why I use COALESCE
instead of ISNULL
. That said, the COALESCE
/ISNULL
is really meaningless here, because the COALESCE
d value (0
) will only potentially meet the < 25000
criteria - and I'm not sure that's what you intended at all.)
Best Answer
Some points
As @swasheck mentioned, you can't have a condition like
WHERE a = b = c
in SQL, it's not valid (unlike other languages). You need to make itWHERE a = b AND b = c
Using implict joins with
WHERE
is not good practise any more, 25 years since SQL-92 standards adopted theJOIN
syntax (a JOIN b ON <condition>
), which has several advantages and should be preferred. One reason is that there are several types of joins available (and all queries except those that use onlyINNER
joins are hard to write using theWHERE
syntax):INNER JOIN
or justJOIN
this is the most common type of join, combines rows from the two joined tables when they match the
ON
condition.LEFT OUTER JOIN
or justLEFT JOIN
very common, too: gets all combinations of
INNER JOIN
plus all unmatched rows of the left table.RIGHT OUTER JOIN
or justRIGHT JOIN
(not so common) the reverse of
LEFT
join: gets all combinations ofINNER JOIN
plus all unmatched rows of the right table.FULL OUTER JOIN
or justFULL JOIN
this is
LEFT
andRIGHT
join, combined.CROSS JOIN
NATURAL JOIN
and variations (not supported by SQL-Server)There are many references and tutorials on the Web about Joins. You can start with MSDN online documentation.
You also need to study how
COUNT()
works:COUNT(*)
counts the number of rows (of a group).COUNT(column/expression)
counts the number of rows (of a group) where thecolumn
orexpression
is not null. If thecolumn
cannot beNULL
, this is the same asCOUNT(*)
COUNT(DISTINCT column/expression)
counts the number of distinct values of thecolumn
orexpression
(within a group).And here are a few ways to write your query:
Option 1 - inline subqueries:
Option 2 - two
LEFT
Joins, thenGROUP BY
and use ofCOUNT(DISTINCT)
:This is very similar to your approach, but has the implicit joins with
WHERE
turned into explicit joins.The
GROUP BY p.ID, p.Name
was added, too, so the query can group rows per Person.We have to use the
COUNT(DISTINCT)
in this version because the two joins may produce multiple rows per Person. (If a person has 2 Cars and 500 books, 1000 rows will be produced and then collapsed into 1 with the grouping. You can try withCOUNT(*)
there to see what (erroneous) results are produced.)Option 3 (my preference) - two
LEFT
Joins to (derived)GROUP BY
subqueries:Option 4 - In SQL Server, there is also the option of using
OUTER APPLY
to (derived)GROUP BY
subqueries. This is similar toLEFT
joins but has even more flexibility, which can be very useful in more complex cases. (in other DBMS like PostgreSQL and DB2, the same functionality exists as well, withLATERAL
joins).Notice how the
ON
conditions from option 3 have been moved toWHERE
, inside the outer apply subqueries:All 4 queries will give same results - all Persons and the Count of their Books and Cars, even if they have no book or no car. If you want to show only Person that have at least one Book or at least one Car (or both), options 2, 3 and 4 can be easily modified: just change the respective
LEFT OUTER JOIN
(or both of them) toINNER JOIN
- and theOUTER APPLY
toCROSS APPLY
.If the problem involves calculating other aggregates, like
MAX()
,SUM()
, etc., then the options are essentially the same - but note that option 2 can be used withMIN
andMAX
but not withSUM
orAVG
. Option 1 can be used with any aggregate function but needs a separate subquery for each aggregate, so if one needs many aggregates from the same table, options 3 and 4 are preferable.