SQL query problem (Oracle)

Question

I've got a query which selects either week 1 or week 2 or both if available and then i need to find a way of displaying only the week 1 results but knowing which ones are available for both weeks.

Here's my query which may help explain

select *
from table1 t1
where t1.location = 'VH' 
and (t1.week = '46' or t1.week = '47');

This gives me the following results

week    location
46      VH209A
46      VH209B
46      VH20B
46      VH20C
47      VH209B
47      VH20A
47      VH20C

I only want to show these results

week    location
46      VH209A
46      VH209B
46      VH20B
46      VH20C

But i want to know which records are also available for the next week i.e these ones

week     location
47       VH209B
47       VH20C

So my desired result would be something like

week     location    2weeks
46       VH209A        N
46       VH209B        Y
46       VH20B         N
46       VH20C         Y

Hope this makes sense?

Any ideas how can i get the results desired?

Johan · Accepted Answer · 2011-09-21 09:57:39Z

2

SELECT
  t1.week
  , t1.location
  , CASE WHEN t2.location is not null THEN 'Y'
         ELSE 'N'
    END AS hasmore
FROM table1 t1
LEFT JOIN (SELECT DISTINCT location FROM table1 WHERE week = '47') t2
  ON (t1.location = t2.location)
WHERE t1.location = 'VH' 
  AND t1.week = '46';

edited Sep 21, 2011 at 9:57

answered Sep 21, 2011 at 9:28

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jamie Taylor Over a year ago

Thanks for the answer but i seem to be getting a missing right parenthesis error with the (t2.location is not null) bit

Johan Over a year ago

Put a case when in instead.

Douwe · Accepted Answer · 2011-09-21 09:37:35Z

0

Have you considered of using INNER JOIN e.g. LEFT or RIGHT JOIN ? With this you can access data from other tables which are connected to your table with the same data.

Im don't know anything about your database structure, but I would devide several data into several tables. With this I would run a query with Joins in them.

For example

select * from table1 LEFT JOIN table2.withdatajoinedtotable1 ON table2.week = table1.week WHERE table2.2weeks = 'Y'

If you seperate your data in several tables, you can access them with JOINS.

Hope this helps.

Collectives™ on Stack Overflow

SQL query problem (Oracle)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related