Check for if else in SQL query

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 891 Views - Last Post: 12 April 2017 - 10:08 PM

#1 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

Check for if else in SQL query

Posted 07 March 2017 - 09:04 PM

Hi,

I have 2 tables:

Quote

Table 1 consist of the columns (user_id and date), below are example:
user_id: A001
date: 2015-01-01

user_id: A001
date: 2016-01-04

user_id: A001
date: 2017-07-01


Quote

Table 2 consist of the columns (user_id, from_date, thru_date, designation), below are example:
user_id: A001
from_date: 2016-01-01
thru_date: 2016-12-31
designation: Senior

user_id: A001
from_date: 2017-01-01
thru_date: null
designation: Manager



May I check how can i write sql query to check if else condition?
Example: if the "date" in table 1 is between the "from_date" and "thru_date" in table 2, display it designation. If the date is not within the range, use the latest designation.

Quote

user_id: A001
date: 2015-01-01
designation: Manager

user_id: A001
date: 2016-01-04
designation: Senior

user_id: A001
date: 2017-07-01
designation: Manager


Is This A Good Question/Topic? 0
  • +

Replies To: Check for if else in SQL query

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,400
  • Joined: 03-December 12

Re: Check for if else in SQL query

Posted 07 March 2017 - 09:08 PM

What have you tried so far?
Was This Post Helpful? 0
  • +
  • -

#3 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

Re: Check for if else in SQL query

Posted 07 March 2017 - 10:46 PM

View Postastonecipher, on 07 March 2017 - 09:08 PM, said:

What have you tried so far?


I have tried the following which each record in table1 will split into 2 rows, as there are 2 records in table2. I have totally no idea how to check the condition.

select t1.user_id, t1.date, t2.designation from table1 t1 
left join table2 t2
   on t1.user_id=t2.user_id


Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4138
  • View blog
  • Posts: 13,074
  • Joined: 08-June 10

Re: Check for if else in SQL query

Posted 08 March 2017 - 01:01 AM

what is the relation between those two tables?
Was This Post Helpful? 0
  • +
  • -

#5 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

Re: Check for if else in SQL query

Posted 20 March 2017 - 01:24 AM

View PostDormilich, on 08 March 2017 - 01:01 AM, said:

what is the relation between those two tables?


Hi,

The 1st table contains all the users' work that they have clock for each date.

The 2nd table contains each user promotion history.
eg. A001 is a senior from 2016-01-01 to 2016-12-31, and from 2017-01-01 is a manager.

Thus, I need to know the date that the user clocked his work is of which rank.

Thanks.
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,400
  • Joined: 03-December 12

Re: Check for if else in SQL query

Posted 20 March 2017 - 05:59 AM

That isn't what he meant. A relationship in a data table is defined by columns that link tables together (relationships). Where does a table 1 column match to a table 2 column?
Was This Post Helpful? 0
  • +
  • -

#7 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

Re: Check for if else in SQL query

Posted 20 March 2017 - 06:00 PM

View Postastonecipher, on 20 March 2017 - 05:59 AM, said:

That isn't what he meant. A relationship in a data table is defined by columns that link tables together (relationships). Where does a table 1 column match to a table 2 column?


Hi,

sorry not sure if my explanation is correct.
Table 1 and Table 2 both have "user_id" which is the employee id (eg. A0001) and is the relationship to link the two tables. Both tables can contains multiple records with the same user_id.

My query is to retrieve the designation (in table 2) of the employee for each record in table 1 (based on the user_id and the date).


Table 1:
user_id
date

Table 2:
user_id
from_date
thru_date
designation
Was This Post Helpful? 0
  • +
  • -

#8 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,400
  • Joined: 03-December 12

Re: Check for if else in SQL query

Posted 20 March 2017 - 06:03 PM

So, you want an inner join to start with and a between type statement
Was This Post Helpful? 0
  • +
  • -

#9 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

Re: Check for if else in SQL query

Posted 20 March 2017 - 08:15 PM

View Postastonecipher, on 20 March 2017 - 06:03 PM, said:

So, you want an inner join to start with and a between type statement


I have no idea how to start with. I tried the following.
select t1.user_id,t1.date from table1 t1, if(t1.date between t2.from_date and t2.thru_date)
left join table2 t2
   on tl.user_id=t2.user_id




Below is the output I need as mentioned in first post with the dataset from table 1 and table 2.
user_id: A001
date: 2015-01-01
designation: Manager

user_id: A001
date: 2016-01-04
designation: Senior

user_id: A001
date: 2017-07-01
designation: Manager
Was This Post Helpful? 0
  • +
  • -

#10 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,400
  • Joined: 03-December 12

Re: Check for if else in SQL query

Posted 21 March 2017 - 03:51 PM

You don't need an if statement, you need it in the where clause.
Was This Post Helpful? 0
  • +
  • -

#11 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

Re: Check for if else in SQL query

Posted 21 March 2017 - 05:43 PM

View Postastonecipher, on 21 March 2017 - 03:51 PM, said:

You don't need an if statement, you need it in the where clause.


Hi,

I amended my code as below, however it does not show all records that I have from table 1.

The latest designation will have "thru_date" column as null. I suppose all the missing records in table 1 is due to null value thus not showing?

Can show me the coding on how to retrieve the record as per below scenario? If the record is not within any promotion date range, use the latest designation.
Table1: I have 3 records with the following date
record1: 2016-03-31
record2: 2017-02-01
record3: 2015-04-01

Table2: There are 2 promotion record
Senior: from_date (2016-01-01), thru_date (2016-12-31)
Manager: from_date (2017-01-01), thru_date (null)

Output:
record1: Senior
record2: Manager
record3: Manager


select t1.user_id,t1.date,t2.designation from table1 t1
left join table2 t2
   on tl.user_id=t2.user_id
where t1.date between t2.from_date and t2.thru_date


Was This Post Helpful? 0
  • +
  • -

#12 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

Re: Check for if else in SQL query

Posted 23 March 2017 - 08:08 PM

Hi,

I have been trying and latest sql query as below can display record 1 & 2 but I still cannot display record 3. Can someone kindly help on the query? Thanks.

Table1: I have 3 records with the following date
record1: 2016-03-31
record2: 2017-02-01
record3: 2015-04-01

Table2: There are 2 promotion record
Senior: from_date (2016-01-01), thru_date (2016-12-31)
Manager: from_date (2017-01-01), thru_date (null)

Output:
record1: Senior
record2: Manager
record3: Manager (dont know how to find)


select t1.user_id,t1.date,t2.designation from table1 t1
left join table2 t2
   on tl.user_id=t2.user_id
where t2.from_date <= tl.date and (t2.thru_date >= tl.date or t2.thru_date is null)


Was This Post Helpful? 0
  • +
  • -

#13 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,400
  • Joined: 03-December 12

Re: Check for if else in SQL query

Posted 23 March 2017 - 09:37 PM

where 
    (t2.from_date <= tl.date 
    and 
    t2.thru_date >= tl.date)
    or t2.thru_date is null


Are you sure that is the logic wanted for the between?
Was This Post Helpful? 0
  • +
  • -

#14 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

Re: Check for if else in SQL query

Posted 12 April 2017 - 05:45 PM

Hi,

Can anyone kindly help on my query? Trying to figure out a solution but to no available.
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13566
  • View blog
  • Posts: 54,122
  • Joined: 12-June 08

Re: Check for if else in SQL query

Posted 12 April 2017 - 07:05 PM

What have you tried, attempted, or considered in the last three weeks?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2