seo Question posted in
Some basic information to get you started with this can be found here.

Is there a way to retrieve data from an sql table depending on the value of a column that can be equal to multiple values? – SEO

I have an SQL table that contains some info about projects and roughly looks like the table below. In this table a project may have multiple instances because it has multiple tasks. Each task will have a status and every status has an id that correlates to it. How do I write a query that will get all instances of project 1 where the status is equal to open, closed, billed or cancelled? even if there is no instance of project one with a status of cancelled?

PROJECT NAME|PROJECT ID|TASK|STATUS|status_code    
project_1        1       seo  open   5
project_1        1       blog closed 0 
project_1        1       data billed 2 
project_2        2       seo  open   5
project_3        3       seo  open   5
project_4        4       seo  open   5
project_5        5       seo  open   5

Note: each status correlates to a status id

0 = closed 
1 = cancelled 
2 = billed 
3 = paid 
4 = on hold 
5 = open 
6 = pending

As of right now my query looks like

"SELECT * FROM Table WHERE project_id = 1 AND Status_Code = (0 OR 1 OR 2 OR 3 OR 4 OR 5 OR 6)"

This returns nothing despite multiple instances of status_code = 2 0 & 5 existing in the table

if I change my query to

SELECT * FROM table WHERE project_id = 1 AND status_code = 2

I do get all instances of status code 2 but I need to be able to check for all of these instances with the possibility of one not existing for the project being queried.

Tags:

2

Answers


  1. 0

    Try this

    SELECT * FROM Table 
WHERE project_id = 1 AND Status_Code IN( 1, 2, 3, 4, 5, 6)

    Also, you might want to filter out duplicates, so maybe

    SELECT DISTINCT [PROJECT NAME], [PROJECT ID]
FROM Table 
WHERE project_id = 1 AND Status_Code IN( 1, 2, 3, 4, 5, 6)
    Login or Signup to reply.
  2. 0

    1st of all, if this is a tasks table, why isn’t there a task id?

    If I understand the quesion right, then GROUP BY or DISTINCT

    SELECT project_id, task, status FROM Table WHERE project_id = 1 AND Status_Code IN (0,1,2,3,4,5,6)
GROUP BY
project_id, task, status

    which will return each distinct value of all 3 columns
    uainv distinct without group by, would return the same

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search