February 6, 2009 at 6:02 am
Hello ppl,
I have a kinda tricky issue here.
I have one table with master data, and a table with detail data.
Some of the records in the master data table have multiple corresponding records in the detail table.
I'm trying to write a query, so that I can retrieve records that match a certain criteria in multiple records of the detail table ... hmmm ... I'm no native speaker, so this is a bit complicated.
ok, example:
use test
-- TABLES
CREATE TABLE tblKandidaten(
ID_Kandidat int NOT NULL,
Nachname nvarchar(50) NOT NULL,
Vorname nvarchar(50) NOT NULL);
CREATE TABLE tblK_Berufsziele(
ID_Berufsziel int IDENTITY(1,1) NOT NULL,
ID_Kandidat int NOT NULL,
Berufskuerzel nvarchar(10) NOT NULL);
GO
-- DATA
INSERT INTO tblKandidaten
SELECT 1, 'Mustermann', 'Max'
UNION ALL
SELECT 2, 'Doe', 'John'
INSERT INTO tblK_Berufsziele
SELECT1,'CIMP'
UNION ALL
SELECT2,'IPROD'
UNION ALL
SELECT1,'INS'
GO
-- SELECT
SELECT
K.ID_Kandidat, Nachname, Vorname, Berufskuerzel
FROM
tblKandidaten K LEFT OUTER JOIN tblK_Berufsziele B
ON K.ID_Kandidat = B.ID_Kandidat
WHERE Berufskuerzel = 'CIMP' AND Berufskuerzel = 'INS'
-- This of course doesn't work ...
-- DROP
DROP TABLE tblKandidaten
DROP TABLE tblK_Berufsziele
GO
As you can see, the WHERE clause does not work, I know why it doesn't work, but I don't know how to get it to work, without joining to tblK_Berufskuerzel multiple times.
I don't want to join tblK_Berufskuerzel multiple times cuz a record in tblKandidaten could theoretically have unlimited correspondants in tblK_Berufskuerzel (just a hand full in reality, but anyways).
Thank you!
February 6, 2009 at 6:37 am
How about using an OR statement between the two criteria?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2009 at 6:44 am
I need to retrieve the guys that have both ('CIMP' and 'INS' in my example).
February 6, 2009 at 6:48 am
Oh, you want to verify that they have BOTH values, but if they have one or the other, it's no good to you?
OK then, how about joining to the table twice, and I think you can use an INNER JOIN on both. Then check for the value on the first table AND the second value on the second table. You'll only return rows that have both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2009 at 6:55 am
Michael Niemand (2/6/2009)
WHERE Berufskuerzel = 'CIMP' AND Berufskuerzel = 'INS'
-- This of course doesn't work ...
This is asked for records where Berufskuerzel is equal to both 'CIMP' and 'INS' which is impossible and therefore always false.
I think what you want is
WHERE Berufskuerzel = 'CIMP' OR Berufskuerzel = 'INS'
This gives
ID_Kandidat Nachname Vorname Berufskuerzel
----------- -------------------- -------------------- -------------
1 Mustermann Max CIMP
1 Mustermann Max INS
(2 row(s) affected)
Is this what you wanted?
Derek
February 6, 2009 at 6:57 am
Thank you very much, but as I wrote in my initial post, I already discarded this option, because the count of the detail is not limited to 2. In most cases there won't be more than 2, but there is the opportunity that some guys have 3 or more.
So I would have to join the same table as much as I search "Berufskuerzel". The point is: how much "Berufskuerzel" I search for is not determined until the user in the frontend builds a search.
February 6, 2009 at 6:59 am
I just read the posts which appeared while I was writing mine
What I think you need is
SELECT
K.ID_Kandidat, Nachname, Vorname
FROM
tblKandidaten K LEFT OUTER JOIN tblK_Berufsziele B
ON K.ID_Kandidat = B.ID_Kandidat
WHERE Berufskuerzel = 'CIMP' OR Berufskuerzel = 'INS'
GROUP BY
K.ID_Kandidat, Nachname, Vorname
HAVING count(*)=2
The result is
ID_Kandidat Nachname Vorname
----------- -------------------- --------------------
1 Mustermann Max
(1 row(s) affected)
Derek
February 6, 2009 at 7:00 am
If you need to retrieve those tblKandidaten records that have an associated tblK_Berufsziele record with Berufskuerzel = 'CIMP' AND another tblK_Berufsziele record with Berufskuerzel = 'INS', then the following should do the job. I've used DISTINCT inside the COUNT aggregate function just in case it is possible to have duplicate tuples of the ID_Kandidat and Berufskuerzel fields in the tblK_Berufsziele table. You don't need the DISTINCT if this is not possible.
SELECT K.ID_Kandidat, K.Nachname, K.Vorname, B.Berufskuerzel
FROM tblKandidaten K
INNER JOIN tblK_Berufsziele B ON (K.ID_Kandidat = B.ID_Kandidat)
INNER JOIN (
SELECT K1.ID_Kandidat
FROM tblKandidaten K1
INNER JOIN tblK_Berufsziele B1 ON (K1.ID_Kandidat = B1.ID_Kandidat)
WHERE B1.Berufskuerzel IN ('CIMP', 'INS')
GROUP BY K1.ID_Kandidat
HAVING (COUNT(DISTINCT B1.Berufskuerzel) = 2)
) X ON (K.ID_Kandidat = X.ID_Kandidat)
WHERE (B.Berufskuerzel IN ('CIMP', 'INS'))
The query can be easily amended to filter on different lists of Berufskuerzel codes.
SELECT K.ID_Kandidat, K.Nachname, K.Vorname, B.Berufskuerzel
FROM tblKandidaten K
INNER JOIN tblK_Berufsziele B ON (K.ID_Kandidat = B.ID_Kandidat)
INNER JOIN (
SELECT K1.ID_Kandidat
FROM tblKandidaten K1
INNER JOIN tblK_Berufsziele B1 ON (K1.ID_Kandidat = B1.ID_Kandidat)
WHERE B1.Berufskuerzel IN ('CIMP', 'INS', 'IPROD')
GROUP BY K1.ID_Kandidat
HAVING (COUNT(DISTINCT B1.Berufskuerzel) = 3)
) X ON (K.ID_Kandidat = X.ID_Kandidat)
WHERE (B.Berufskuerzel IN ('CIMP', 'INS', 'IPROD'))
February 6, 2009 at 7:04 am
Michael Niemand (2/6/2009)
Thank you very much, but as I wrote in my initial post, I already discarded this option, because the count of the detail is not limited to 2. In most cases there won't be more than 2, but there is the opportunity that some guys have 3 or more.So I would have to join the same table as much as I search "Berufskuerzel". The point is: how much "Berufskuerzel" I search for is not determined until the user in the frontend builds a search.
I think my solution will work here but, obviously you need to replace the '2' by however many "Berufkuerzel" are actually needed.
Derek
February 6, 2009 at 7:20 am
andrewd.smith (2/6/2009)
SELECT K.ID_Kandidat, K.Nachname, K.Vorname, B.BerufskuerzelFROM tblKandidaten K
INNER JOIN tblK_Berufsziele B ON (K.ID_Kandidat = B.ID_Kandidat)
INNER JOIN (
SELECT K1.ID_Kandidat
FROM tblKandidaten K1
INNER JOIN tblK_Berufsziele B1 ON (K1.ID_Kandidat = B1.ID_Kandidat)
WHERE B1.Berufskuerzel IN ('CIMP', 'INS')
GROUP BY K1.ID_Kandidat
HAVING (COUNT(DISTINCT B1.Berufskuerzel) = 2)
) X ON (K.ID_Kandidat = X.ID_Kandidat)
WHERE (B.Berufskuerzel IN ('CIMP', 'INS'))
The outer WHERE clause here is only needed if you want to restrict the display to the 'Berufskuerzel' which are selected. I'd expect you'd either want ALL of them displayed (WHERE not required) or NONE of them (i.e. only the name of the candidate needed) in which case you only need the inner select as in my solution.
Note that "WHERE x IN (a,b,...)" and "WHERE x=a OR x=b ..." are equivalent. If you look at the execution plan for the above, it shows the Table Scan Predicate for tblK_Berufsziele using 'OR' clauses even though 'IN' is used.
Derek
February 6, 2009 at 7:25 am
Yep, my solution is essentially the same as Derek Dongray's, except that I use a derived table. Derek Dongray's solution is simpler and may be all you require, but mine does allow more control over the resultset that is returned. The only significant difference is the use of the DISTINCT keyword in the COUNT aggregate, which may or may not be necessary.
February 6, 2009 at 8:10 am
Thank you all very much. I really appreciate your help!
nonetheless I made a whole different approach, which may be not as high-performance as your solutions, but it saves work in changing the frontend. The query is generated automatically, so it would be rather difficult to fit a complicated statement in there. Since the "Berufskuezel" change very rarely, I decided to generate a pivoted view over all the Berufskuerzel (45) which has all the Berufskuerzel as columns:
SELECT
ID_Kandidat, ***, CIMP, CSTR, ECOM, ENG, FAC, FAM, FCF, FCON, FCR,
FER, FFO, FMA, FPC, FPE, FRE, FRM, FSF, FST, FTR, FVC, HUO, IBDS, IGM,
IHR, ILOG, IPROD, IPROJ, IRD, IRE, ISM, IT, JOU, JURA, KOM, MAN, MED,
MIL, NN, PR, PRAK, [R & D], TAX, VER, XXX
FROM
(SELECT ID_Kandidat, Berufskuerzel
FROM tblK_Berufsziele) KB PIVOT (COUNT(Berufskuerzel) FOR Berufskuerzel IN
(***, CIMP, CSTR, ECOM, ENG, FAC, FAM, FCF, FCON, FCR, FER,
FFO, FMA, FPC, FPE, FRE, FRM, FSF, FST, FTR, FVC, HUO, IBDS,
IGM, IHR, ILOG, IPROD, IPROJ, IRD, IRE, ISM, IT, JOU, JURA, KOM, MAN, MED, MIL,
NN, PR, PRAK, [R & D], TAX, VER, XXX))
AS qryPvtK_Berufsziele
edit: ok, now it works as expected ...
funny: The forum-Software censors our code for assurance
February 9, 2009 at 4:25 am
I don't know what your front-end is written in, but I wouldn't call either andrew's or my solution a complicated query.
' VBScript (or Visual Basic)
SQL = "SELECT K.ID_Kandidat, Nachname, Vorname " & _
"FROM tblKandidaten K LEFT OUTER JOIN tblK_Berufsziele B " & _
"ON K.ID_Kandidat = B.ID_Kandidat WHERE 0=1"
FOR i = 1 to NBerufskuerzel
SQL = SQL & " OR Berufskuerzel = '" & select(i) & "'"
NEXT
SQL = SQL & " GROUP BY K.ID_Kandidat, Nachname, Vorname " & _
" HAVING count(*)=" & cstr(NBerufskuerzel)
This can't be much more complex than what you are building.
Of course, it depends what you need to do to populate select(i).
P.S. Of course, purists would say you shouldn't build SELECT statements but should be calling a procedure... 🙂
Derek
February 9, 2009 at 5:04 am
Well, you would of course be right if I would put a static SQL-string into my code. The point is, that I want our useres to flexibly build up a search. So all search criteria (tablename, fieldname, datatype, possible comparison operators, display name etc) are residing into a table, the user clicks together his search which generates a XML file, from which I create a WHERE-criteria. If the string differs from [tablename].[fieldname] [operator] [value] I would have to consider a lot of exceptions, which would make this now clearly arranged code rather complicated.
I don't know, this may not be what is called "best practice", but its convenient for the users, easy to add search criterias and the code is small and easy to understand.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply