June 26, 2013 at 9:04 am
Hey guys i need to create a query were i can find out the number of learners there are with a model of 21 and another model eg
learner model
mamzy 20
mamzy 21
mamzy 30
jack 21
jack 99
greg 99
greg 30
harper 20
RESULT
LEARNER model
mamzy 20
mamzy 21
mamzy 30
jack 21
jack 99
please help
ive tried
select learner, model
from table name
where model = 21 and model = 20 or model = 30 or model = 99
group by learner, model
but i get a long list as the first execution please help!!!!!!!!
June 26, 2013 at 9:09 am
mamzy10 (6/26/2013)
Hey guys i need to create a query were i can find out the number of learners there are with a model of 21 and another model eglearner model
mamzy 20
mamzy 21
mamzy 30
jack 21
jack 99
greg 99
greg 30
harper 20
RESULT
LEARNER model
mamzy 20
mamzy 21
mamzy 30
jack 21
jack 99
please help
ive tried
select learner, model
from table name
where model = 21 and model = 20 or model = 30 or model = 99
group by learner, model
but i get a long list as the first execution please help!!!!!!!!
This looks a lot like homework. Look at the query you tried. It will never return any rows because there are no values that are 21 AND are also either 20, 30, 99. 🙂
There are a number of ways to accomplish this type of thing. EXISTS is probably the way I would do this. Want to give that a shot?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 9:10 am
Hi
SELECT
learner
, model
FROM
tablename
WHERE
learner in (SELECT Learner FROM tablename WHERE MODEL IN (21,20,30,99))
Edit: oops didn't twig this may be homework!
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
June 26, 2013 at 9:15 am
will give it ago now n its for work actually but i have very limited knowledge using sql but there are no sql dudes in to this work im an accountant
June 26, 2013 at 9:23 am
Hey andy thats not worked thats just spit out all the data from the first table i need to know where they definitly have a model of 21 with another model could be a combination of anything but they must have atlest one model at 21
June 26, 2013 at 9:24 am
mamzy10 (6/26/2013)
will give it ago now n its for work actually but i have very limited knowledge using sql but there are no sql dudes in to this work im an accountant
Reason I said something about homework is because there are lots of people who come in here looking for us to do their homework. The work is simple but it takes away the learning opportunity for them.
The first thing I did was put together some ddl and sample data so we all have something to work with.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
learner varchar(10),
model int
)
insert #Something
select 'mamzy', 20 union all
select 'mamzy', 21 union all
select 'mamzy', 30 union all
select 'jack', 21 union all
select 'jack', 99 union all
select 'greg', 99 union all
select 'greg', 30 union all
select 'harper', 20
Now we start to work on the actual query. I came up with a couple ways to do this off the top of my head.
--#1
select s.learner, s.model
from #Something s
where model = 21
and exists (select * from #Something s2 where s2.learner = s.learner and s2.model <> 21);
--#2
select distinct s.learner, s.model
from #Something s
join #Something s2 on s2.learner = s.learner and s2.model <> 21
where s.model = 21;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 9:27 am
Firstly, whenever you come on a technical forum you should always try to provide people with DDL and readily consumable sample data. This makes people far more willing to answer your question. In your case, the following is enough: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT IDENTITY(INT,1,1) AS id, learner, model
INTO #testEnvironment
FROM (VALUES('mamzy', 20),('mamzy', 21),('mamzy', 30),
('jack', 21),('jack', 99),('greg', 99),
('greg', 30),('harper', 20)
)a(learner, model);
Now everyone that wants a copy of your table to test solutions with, has one. This means that when you are provided with an answer, it is far more likely to be a tested and working solution.
Now, we'll get on to your actual question. You want to find all of the "learner" records that have a model of 21. So, the first part of any query we execute needs to find that out: -
SELECT *
FROM #testEnvironment alias_21
WHERE alias_21.model = 21;
That produces: -
id learner model
----------- ------- -----------
2 mamzy 21
4 jack 21
Not quite what we're after, but it is close. We need to grab all of the models that mamzy and jack have. So we could use the query we've just written to filter the table. Let's try an EXISTS: -
SELECT *
FROM #testEnvironment alias_allRecords
WHERE EXISTS (SELECT 1
FROM #testEnvironment alias_21
WHERE alias_21.model = 21 AND alias_allRecords.learner = alias_21.learner);
That produces: -
id learner model
----------- ------- -----------
1 mamzy 20
2 mamzy 21
3 mamzy 30
4 jack 21
5 jack 99
Looks good, right?
Well, I'd say that there may be a bug. You said that you want to find all of the records that have a 21 model and another model. What if a learner only has a 21 model, but no other model? Let's change the sample data: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT IDENTITY(INT,1,1) AS id, learner, model
INTO #testEnvironment
FROM (VALUES('mamzy', 20),('mamzy', 21),('mamzy', 30),
('jack', 21),('jack', 99),('greg', 99),
('greg', 30),('harper', 20),('cadavre', 21)
)a(learner, model);
Now run our query: -
SELECT *
FROM #testEnvironment alias_allRecords
WHERE EXISTS (SELECT 1
FROM #testEnvironment alias_21
WHERE alias_21.model = 21 AND alias_allRecords.learner = alias_21.learner);
So, from your description, we are expecting the same results as before. The new learner "cadavre" has a model 21, but that is his only model so he shouldn't be returned in our resultset. Guess what our query produces: -
id learner model
----------- ------- -----------
1 mamzy 20
2 mamzy 21
3 mamzy 30
4 jack 21
5 jack 99
9 cadavre 21
Whoops. So, how are you going to filter out the bad row? I'll leave you with that, if you struggle then come back and show what you've tried.
June 26, 2013 at 9:35 am
Cadavre thank you soo much for your help will get back to you if i need any more help
god bless
June 27, 2013 at 2:09 am
Hi cadavre still having prolems its still spitting out ALL of the data and not just those ones with 21 and something else. im getting learners who dont even have 21 etc HELPPPP !!!
June 27, 2013 at 3:18 am
mamzy10 (6/27/2013)
Hi cadavre still having prolems its still spitting out ALL of the data and not just those ones with 21 and something else. im getting learners who dont even have 21 etc HELPPPP !!!
Please post the code that you're using, otherwise I'm left feeling like a mechanic that is told that a car sounds funny. Pretty much impossible to diagnose 😉
June 27, 2013 at 3:38 am
hi cadavre its ok iv managed to sort it out. thank u very much for your help.
much abliged
mamzy
June 27, 2013 at 7:22 am
mamzy10 (6/27/2013)
Hi cadavre still having prolems its still spitting out ALL of the data and not just those ones with 21 and something else. im getting learners who dont even have 21 etc HELPPPP !!!
Did you look at the examples I posted? They will both do exactly what you are asking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply