SQL query help

  • 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!!!!!!!!

  • 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 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!!!!!!!!

    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/

  • 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

  • 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

  • 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

  • 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/

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Cadavre thank you soo much for your help will get back to you if i need any more help

    god bless

  • 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 !!!

  • 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 😉


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • hi cadavre its ok iv managed to sort it out. thank u very much for your help.

    much abliged

    mamzy

  • 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