Showing posts with label logic. Show all posts
Showing posts with label logic. Show all posts

Monday, February 20, 2012

Query logic not working...

I have a little system of 3 tables Job, employees and times. This times table has the fields times_id, employee_id and job_id

I'm trying to have a query that pull of employees that don't have a certain job_id yet. I'm going to put this data in a table so the user knows they are available for that job. The code i have isn't working, and i'm not sure why.

SELECT
DISTINCT times.employee_id, employee.employee_name
FROM employee
INNER JOIN times ON employee.employee_id = times.employee_id
WHERE (times.job_id <> @.job_id)

Thanks in advance for any help. I'm sure I missing someting silly, or maybe i need to have a stored procedure involved?... Thanks!

Try a subquery:

SELECT
DISTINCT employee_id, employee_name
FROM employee
WHERE employee_id not in
(SELECT employee_id FROM times
WHERE (job_id= @.job_id) )

|||That worked great, I've totally forgot about sub-queries. Thanks a lot Iori Jay.|||

OR

SELECT
DISTINCT employee_id, employee_name
FROM employee
WHERE not exists (SELECT employee_id FROM times
WHERE (job_id= @.job_id) )

Query Logic - SQL Help

I have some table data and know how I want the results but I'm just having a bit of trouble in constructing the SQL logic to obtain the desired results. There's a site where visitors are able to select from a list of parts, and it will return a set of model/products that they can produce with the selected parts. Here's the data ...

tblModel tblPart
ModelId ModelName PartId PartName
------- -------
1 Alpha 1 CHOO1 Stem
2 Bravo 2 BH034 Rod
3 Bravo Pro 3 HRE Seat

tblModelPart
ModelPartId ModelId PartId
----------
1 1 1
2 2 1
3 2 3
4 3 1
5 3 2
6 3 3

... and here's the logic that I'm trying to implement, assume that the user selects from a form, parts with the PartId 1 and 3 ...

1. Return all models that contain only the parts selected.

ModelId ModelName
-------
2 Bravo

2. Return all models that contain the parts selected, and may contain other parts.

ModelId ModelName
-------
2 Bravo
3 Bravo Pro

... so do you have any idea on how the SQL would look for either of these queries?

Thanks in advance,
GoranWhat have you tried so far?

This sounds a lot like homework to me, and at DBForums we don't do homework outright (although we'll often provide guidance).

For what it is worth, I'd probably read up a bit on the EXISTS clause if I were you.

-PatP|||HOMEWORK!!!

yes, i have a very clear idea what the SQL would like like

you must give it a try yourself, nobody here does homework assignments

however, we will help you with it, once you put in the necessary effort

:)|||rats, sniped again :(|||lmao, never been accused of that before, my programming skills (and age) are much beyond student level but my SQL leaves alot to be desired. The tables have been simplified and context changed to protect the nature of the site ;) so I'm sorry if it appears too simple.

One thing I've tried is querying the table tblModelPart for the selected parts, grouping on ModelId and if the count matches the number of components selected on the client side form then voila! you have a match. It didn't work out so well and didn't get me any further to sussing out the 2nd piece of logic.

I've never really used the EXISTS clause (yes I know, blasphemy!) but will check it out later. Thanks for the pointer ;)|||One thing I've tried is querying the table tblModelPart for the selected parts, grouping on ModelId and if the count matches the number of components selected on the client side form then voila! this is the solution i would have offered, it works ~so~ well

could you show your query please?|||I think I may of had a case of things-always-seem-much-simpler-when-explaining-to-someone-else syndrome :s The query I was using has been left at home so I decided to quickly knock one up as example and it seems to work now.

SELECT ModelId
FROM tblModelPart
WHERE (PartId = 1 OR PartId = 3)
AND (SELECT COUNT(*) FROM tblModelPart AS tblModelPart2 WHERE tblModelPart2.ModelId = tblModelPart.ModelId) = 2
GROUP BY ModelId
HAVING COUNT(ModelId) = 2

Include the line in red for logic #1 and exclude it for #2. Is it really that simple or am I missing something completely obvious? I'd still be interested in seeing if there's a way using EXISTS.|||since we are looking for presence or absence of more than one row, i.e. a group, therefore GROUP BY is necessary
SELECT ModelId
FROM tblModelPart
GROUP
BY ModelId
HAVING ...this is the basic approach, which you anticipated

now for specific situations, simply count what you want

for #1, use
HAVING SUM(CASE WHEN PartId IN ( 1,3 )
THEN 1 ELSE 0 END) = 2
AND SUM(CASE WHEN NOT PartId IN ( 1,3 )
THEN 1 ELSE 0 END) = 0
for #2, remove the AND condition|||actually, #1 can be simplified (and made more efficient) like this:SELECT ModelId
FROM tblModelPart
WHERE PartId IN ( 1,3 )
GROUP
BY ModelId
HAVING COUNT(*) = 2this means fewer rows go into the grouping process

query logic

Hi there

Could you help me with rewritting below query in ANSI format-- for sql server 2005

SELECT
VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,
Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt
FROM
VSL, Co, VslCo, VslTy
WHERE
VSL.VSLNM = @.VslNm and
Vsl.VslTyCd = VslTy.VslTyCd and
VSL.UVN *= VslCo.UVN and
Co.CoNo =* (Select VslCo1.MainHdgOwrCoNo
from VSL VSL1,VslCo VslCo1, VslTy VslTy1
where VSL1.UVN = VSL.UVN and
Vsl1.VslTyCd = VslTy1.VslTyCd and
VslCo1.UVN = VSL1.UVN)

Regards

Rahul

The general form would be:

SELECT t1.Col1, t2.Col1

FROM Table1 t1

INNER JOIN Table2 t2

ON t1.ID = t2.ID

WHERE t1.Col3 = @.MyParameter

AND t1.Col4 = (SELECT t3.Col1

FROM MyTable3 t3

INNER JOIN MyTable4 t4

ON t3.ID = t4.ID)

See SQL Server 2005 Books Online topic: ‘FROM (Transact-SQL)’ for more details.

|||*= means "LEFT JOIN" and =* means "RIGHT JOIN" (I think)

So what you're after is:

SELECT
VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,
Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt
FROM
VSL
JOIN

VslTy
on Vsl.VslTyCd = VslTy.VslTyCd
RIGHT JOIN

Co

on Co.CoNo = (Select VslCo1.MainHdgOwrCoNo
from
VSL VSL1
JOIN
VslCo VslCo1
on VslCo1.UVN = VSL1.UVN
JOIN VslTy VslTy1
on Vsl1.VslTyCd = VslTy1.VslTyCd
where VSL1.UVN = VSL.UVN
)

LEFT JOIN

VslCo
on VSL.UVN = VslCo.UVN
WHERE
VSL.VSLNM = @.VslNm

But because you have RIGHT JOIN and LEFT JOIN used together, you will want to confirm that it's doing the right thing. Best is to eliminate RIGHT JOINs, in favour of LEFT, because it's so much clearer for all involved.

And it's been so long since I've used =* and *=... I guess I could have them the wrong way around.|||

well *= means left outer join

But above trick didnt work, It is really making me scratching my head.

|||

SELECT

VSL.VSLNM

, VSL.UVN

, VSL.UnqCoNo

, Co.CoRootNm

, Vsl.VslSt

, VslTy.VslTyNm

, VslSt

, Vsl.FlagCd

, Vsl.YrBld

, Vsl.YrBld

, Vsl.Grtge

, Vsl.DdWtTge

, Vsl.FmaDdWt

FROM

VSL

JOIN

VslTy

ON

Vsl.VslTyCd = VslTy.VslTyCd

LEFT JOIN

VslCo

ON

VSL.UVN = VslCo.UVN

RIGHT JOIN

Co

ON

CoNo = (Select

VslCo1.MainHdgOwrCoNo

FROM

VSL VSL1

,VslCo VslCo1

, VslTy VslTy1

WHERE

VSL1.UVN = VSL.UVN

AND

Vsl1.VslTyCd = VslTy1.VslTyCd

AND

VslCo1.UVN = VSL1.UVN

)

WHERE

VSL.VSLNM = @.VslNm

|||Ok, try swapping where I've put 'LEFT' and 'RIGHT' - my definition may be the wrong way around. I guess =* could be LEFT... it's been too long.

Rob|||

Hi there

Many thanx to you all, with ur input I am able to crack it.

Right query is

SELECT

VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,

Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt

FROM

VSL Join VSLTy

on vsl.vsltycd=vslty.vsltycd

left join vslco

on vsl.uvn=vslco.uvn

left join co

on

co.cono=(Select VslCo1.MainHdgOwrCoNo

from VSL VSL1,VslCo VslCo1, VslTy VslTy1

where VSL1.UVN = VSL.UVN and

Vsl1.VslTyCd = VslTy1.VslTyCd and

VslCo1.UVN = VSL1.UVN)

where

VSL.VSLNM = @.VslNm