Post reply

Need help with a sql query

  • January 17, 2017 at 9:56 pm

    #334285

    How do I write this sql query?

    2) Per the data below, Table A has activities with a unique ID and Table B has list of attachments to each activity, find the list of activities that have attachments with the Title that includes the word "Relayed".

    Any help writing a sql query with the question above and the table data below would be greatly appreciated.

    Table A

    Activity #Activity typeActivity sub typeActivity start dateActivity complete date

    A1234CommunicationSea Hawks1/1/171/3/17

    A5678CommunicationSea Hawks1/2/171/4/17

    A7890CommunicationSea Hawks1/3/171/5/17

    Table B

    Activity #Attachment Title

    A1234Relayed message today - attached

    A1234Message attached.

    A1234Message attached.

    A1234Message attached.

    A1234Message attached.

    A5678Relayed - check!

    A5678Message attached.

    A5678Message attached.

    A5678Message attached.

    A5678I attached the relayed message.

    A7890Message attached.

    A7890Message attached.

    A7890Message attached.

    A7890Message attached.

    A7890Please relay the message.

    A7890Message attached.

  • January 17, 2017 at 11:52 pm

    #1923335

    Nluvwocean - Tuesday, January 17, 2017 9:56 PM

    How do I write this sql query?2) Per the data below, Table A has activities with a unique ID and Table B has list of attachments to each activity, find the list of activities that have attachments with the Title that includes the word "Relayed".Any help writing a sql query with the question above and the table data below would be greatly appreciated.Table AActivity #Activity typeActivity sub typeActivity start dateActivity complete dateA1234CommunicationSea Hawks1/1/171/3/17A5678CommunicationSea Hawks1/2/171/4/17A7890CommunicationSea Hawks1/3/171/5/17Table BActivity #Attachment TitleA1234Relayed message today - attachedA1234Message attached.A1234Message attached.A1234Message attached.A1234Message attached.A5678Relayed - check!A5678Message attached.A5678Message attached.A5678Message attached.A5678I attached the relayed message.A7890Message attached.A7890Message attached.A7890Message attached.A7890Message attached.A7890Please relay the message.A7890Message attached.

    2) ? Looks like homework
    INNER JOIN the two tables. Use col LIKE '%Relayed%'
    Did you create the tables yet? Insert the data?

  • January 18, 2017 at 6:25 am

    #1923404

    I have to do this problem for an interview and I can't figure out how to run the query. What I have built so far doesn't execute the query so i needed help. Truthfully I need a lot of help! Any ideas would help me explain the query process to my interviewer since I am still a novice and can't figure this one out.

    This is what I have so far:

    SELECT Attachmenttitle

    FROM tableb

    INNER JOIN tables

    ON tableb.attachmenttitle=tablea.activity#

    Where attachmenttitle ='%related%'

    This doesn't run! HELP

    What am I doing wrong? How do I write a good sql query for the question per the data below, Table A has activities with a unique ID and Table B has list of attachments to each activity, find the list of activities that have attachments with the Title that includes the word "Relayed".

  • January 18, 2017 at 6:54 am

    #1923407

    Nluvwocean - Wednesday, January 18, 2017 6:25 AM

    I have to do this problem for an interview and I can't figure out how to run the query. What I have built so far doesn't execute the query so i needed help. Truthfully I need a lot of help! Any ideas would help me explain the query process to my interviewer since I am still a novice and can't figure this one out.This is what I have so far:SELECT AttachmenttitleFROM tablebINNER JOIN tablesON tableb.attachmenttitle=tablea.activity#Where attachmenttitle ='%related%'This doesn't run! HELPWhat am I doing wrong? How do I write a good sql query for the question per the data below, Table A has activities with a unique ID and Table B has list of attachments to each activity, find the list of activities that have attachments with the Title that includes the word "Relayed".

    First, the table design is bad to start with, lots I would change, but here's what you need (sort of).

    SELECT b.[Attachment title]
    FROM tableb b
    INNER JOIN tablea a
    ON b.[activity#]=a.[activity#]
    Where b.[attachment title] LIKE'%related%'

    Remember, you can't use the "=" operator with %, you're basically looking for that exact value and not a wildcard string search.
    When there are spaces in you column names, you can't simple concatenate the 2 strings, you'll need to use brackets [part part] not partpart.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • January 18, 2017 at 6:55 am

    #1923409

    Nluvwocean - Wednesday, January 18, 2017 6:25 AM

    I have to do this problem for an interview and I can't figure out how to run the query. What I have built so far doesn't execute the query so i needed help. Truthfully I need a lot of help! Any ideas would help me explain the query process to my interviewer since I am still a novice and can't figure this one out.This is what I have so far:SELECT AttachmenttitleFROM tablebINNER JOIN tablesON tableb.attachmenttitle=tablea.activity#Where attachmenttitle ='%related%'This doesn't run! HELPWhat am I doing wrong? How do I write a good sql query for the question per the data below, Table A has activities with a unique ID and Table B has list of attachments to each activity, find the list of activities that have attachments with the Title that includes the word "Relayed".

    SELECT Attachmenttitle
    FROM tableb
    INNER JOIN tables
    ON tableb.attachmenttitle=tablea.activity#
    Where attachmenttitle ='%related%'

    very close - the INNER join needs to be tablea but also the ON needs to be something that is common for both tables. So does the attachmenttitle in table b = the activity # of table a? Probably not - check the column in table b that would match the column in table a.
    Also the where clause is slightly off - see the response previously has a good clue.

  • January 18, 2017 at 7:04 am

    #1923413

    Just in case anyone needs some data

    use [test]
    go

    create table [TableA]
    ([Activity #] Varchar(5),
    [Activity type] Varchar(15),
    [Activity sub type] Varchar(50),
    [Activity start date] smalldatetime,
    [Activity complete date] smalldatetime)

    Insert TableA Values
    ('A1234', 'Communication', 'Sea Hawks', '1/1/17', '1/3/17'),
    ('A5678', 'Communication', 'Sea Hawks', '1/2/17', '1/4/17'),
    ('A7890', 'Communication', 'Sea Hawks', '1/3/17', '1/5/17')

    select * from TableA

    create table [TableB]
    ([Activity #] Varchar(5),
    [Attachment Title] Varchar(50))

    Insert TableB Values
    ('A1234', 'Relayed message today - attached'),
    ('A1234', 'Message attached.'),
    ('A1234', 'Message attached.'),
    ('A1234', 'Message attached.'),
    ('A1234', 'Message attached.'),
    ('A5678', 'Relayed - check!'),
    ('A5678', 'Message attached.'),
    ('A5678', 'Message attached.'),
    ('A5678', 'Message attached.'),
    ('A5678', 'I attached the relayed message.'),
    ('A7890', 'Message attached.'),
    ('A7890', 'Message attached.'),
    ('A7890', 'Message attached.'),
    ('A7890', 'Message attached.'),
    ('A7890', 'Please relay the message.'),
    ('A7890', 'Message attached.')

    select * from TableB

  • January 18, 2017 at 10:36 am

    #1923483

    2) Per the data below, Table A has activities with a unique ID and Table B has list of attachments to each activity, find the list of activities that have attachments with the Title that includes the word "Relayed".
    Table A
    Activity #Activity typeActivity sub typeActivity start dateActivity complete date
    A1234CommunicationSea Hawks1/1/20171/3/2017
    A5678CommunicationSea Hawks1/2/20171/4/2017
    A7890CommunicationSea Hawks1/3/20171/5/2017
    Table B
    Activity #Attachment Title
    A1234Relayed message today - attached
    A1234Message attached.
    A1234Message attached.
    A1234Message attached.
    A1234Message attached.
    A5678Relayed - check!
    A5678Message attached.
    A5678Message attached.
    A5678Message attached.
    A5678I attached the relayed message.
    A7890Message attached.
    A7890Message attached.
    A7890Message attached.
    A7890Message attached.
    A7890Please relay the message.
    A7890Message attached.

  • January 18, 2017 at 10:39 am

    #1923486

    Thanks! You are the best! I really appreciate this answer. I am a novice so forgot that you cannot use the equal operator with the percentage sign. That helped a lot with the new query suggestion.

    2) Per the data below, Table A has activities with a unique ID and Table B has list of attachments to each activity, find the list of activities that have attachments with the Title that includes the word "Relayed".
    Table A
    Activity #Activity typeActivity sub typeActivity start dateActivity complete date
    A1234CommunicationSea Hawks1/1/20171/3/2017
    A5678CommunicationSea Hawks1/2/20171/4/2017
    A7890CommunicationSea Hawks1/3/20171/5/2017

    Activity #Attachment Title
    A1234Relayed message today - attached
    A1234Message attached.
    A1234Message attached.
    A1234Message attached.
    A1234Message attached.
    A5678Relayed - check!
    A5678Message attached.
    A5678Message attached.
    A5678Message attached.
    A5678I attached the relayed message.
    A7890Message attached.
    A7890Message attached.
    A7890Message attached.
    A7890Message attached.
    A7890Please relay the message.
    A7890Message attached.

  • January 18, 2017 at 10:39 am

    #1923487

    g.dury - Wednesday, January 18, 2017 6:55 AM

    Nluvwocean - Wednesday, January 18, 2017 6:25 AM

    I have to do this problem for an interview and I can't figure out how to run the query. What I have built so far doesn't execute the query so i needed help. Truthfully I need a lot of help! Any ideas would help me explain the query process to my interviewer since I am still a novice and can't figure this one out.This is what I have so far:SELECT AttachmenttitleFROM tablebINNER JOIN tablesON tableb.attachmenttitle=tablea.activity#Where attachmenttitle ='%related%'This doesn't run! HELPWhat am I doing wrong? How do I write a good sql query for the question per the data below, Table A has activities with a unique ID and Table B has list of attachments to each activity, find the list of activities that have attachments with the Title that includes the word "Relayed".

    SELECT Attachmenttitle
    FROM tableb
    INNER JOIN tables
    ON tableb.attachmenttitle=tablea.activity#
    Where attachmenttitle ='%related%'

    very close - the INNER join needs to be tablea but also the ON needs to be something that is common for both tables. So does the attachmenttitle in table b = the activity # of table a? Probably not - check the column in table b that would match the column in table a.
    Also the where clause is slightly off - see the response previously has a good clue.

    Thank you!!!!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply