How to selectively get rows that match criteria

[spowel4]

I have a table with several columns, the two relevant columns for what I need to do are invoiceNumber and stockNumber. The scenario here is that it's a table of invoice information, so each invoice can have and will most likely have multiple rows in the table which represent each line item sold on the invoice.
I need to pick out only the invoices that have a stockNumber that begins with the letters "CC". The "CC" items are an extended warranty that was sold.
So in other words I need to get all the rows for invoices in the table that have a "CC" stockNumber so that I can do some further manipulation of those invoices.
An example of the rows for an invoice that has a "CC" stockNumber in one of the rows might be like this:

    invoiceNumber   invoiceDate customerNumber  stockNumber soldQty soldPrice
    -------------   ----------- --------------  ----------- ------- ---------
    489670          2013-04-11  25116           LR76003-34  1       899.99
    489670          2013-04-11  25116           LR76003-08  1       0.00
    489670          2013-04-11  25116           CCCOMBO     1       99.99

I would need to get all those rows.

An example of the rows for an invoice that does not have a "CC" stockNumber in any of the rows might be like this:

invoiceNumber   invoiceDate customerNumber  stockNumber soldQty soldPrice
-------------   ----------- --------------  ----------- ------- ---------
4934170         2013-04-11  30738           OCT477-8    1       359.99
4934170         2013-04-11  30738           OCT477-6    1       116.99

I would need to completely skip these rows since none of the rows have a "CC" stockNumber.

I hope I'm making sense with this, thanks for any suggestions.

[Reverend Jim]

The usual way is to use wildcards. You didn't say what database you are using so I'll just give you the one for MS SQL.

SELECT * FROM mytable WHERE stockNumber LIKE 'CC%'

This selects all records where stockNumber starts with CC. Other databases may use other characters for wildcards. In MS SQL, "%" matches any string and "_" matches any single character. More information is available here

[spowel4]

Apologies for not specifying the db type, it is MS SQL. Thanks for responding but I might not have explained my need clearly. If a particular invoice happens to have a row whose stockNumber is LIKE 'CC%', I need to get EVERY row that is associated with that invoice, not just the CC row. So in the example I gave above, I would need to get all 3 rows for invoice 489670, not just the CC row. Hope this helps explain it better.

[pritaeas]

Sounds like a sub-select.

SELECT * 
FROM Stock
WHERE invoiceNumber IN (
    SELECT invoiceNumber 
    FROM Stock
    WHERE stockNumber LIKE 'CC%'
)

[IIM]

@pritaeas and @Reverend Jim -You missed last line

An example of the rows for an invoice that does not have a "CC" stockNumber in any of the rows might be like this:

So i think so this must be solution to spowel4 problem.From the problem i think he want all rows except the one having starting with 'CC' in stockNumber

SELECT * FROM Stock WHERE stockNumber NOT LIKE 'CC%'

[pritaeas]

I need to pick out only the invoices that have a stockNumber that begins with the letters "CC".

;) perhaps he needs to make up his mind first.

[spowel4]

pritaeas, your solution of

SELECT * 
FROM Stock
WHERE invoiceNumber IN (
    SELECT invoiceNumber 
    FROM Stock
    WHERE stockNumber LIKE 'CC%'
)

seems to work, thank you.