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?
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';
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
精彩评论