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?

Answer 1

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';

Answer 2

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.

Read more about JOINS on SQL JOINS