August 2, 2012 at 5:49 pm
CREATE TABLE car
(
vinno INT PRIMARY KEY,
model_id INT
)
INSERT INTO car VALUES (12345,25), (12346,25), (12347,26)
CREATE TABLE sales_bill
(
model_id INT NOT null,
billno INT NOT null,
PRIMARY KEY
(
model_id, billno
) )
INSERT INTO sales_bill VALUES (25, 107), (25, 108), (26, 107)
CREATE TABLE record
(
billno INT NOT NULL,
recordno INT NOT NULL,
PRIMARY KEY
( billno, recordno)
)
INSERT INTO record VALUES (107,702),(108,8001),(108,8063),(108,8059),(108,8068)
CREATE TABLE areaofsales
(
zone_id INT NOT NULL,
recordno INT NOT NULL,
PRIMARY KEY
(zone_id, recordno
))
INSERT INTO areaofsales VALUES (1,8063),(2,8063),(1,8059),(2,8001),(1,8068),(1,702)
/*
I need to write query, which display vinno, model_id, zone_id. which I have tried as following.
Requirement is, Each model_id can have multiple vinno, vinno must be unique and in addition, zone_id must be 1 0r 2. If there is
only one model_id then zone_id can be 1 or 2(eg model_id= 26). If there is 2 model_id then both cannot be same 1,1 or 2, 2 it
different zone_id.
Expected out put is
vinno model_id zone_id
12345 25 1
12346 25 2
12347 26 1
or
vinno model_id zone_id
12345 25 2
12346 25 1
12347 26 1
*/
select DISTINCT vinno,B.model_id,D.zone_id FROM car A
JOIN sales_bill B ON A.model_id =B.model_id
JOIN record C ON B.BillNo =C.BillNo
JOIN areaofsales D ON D.recordno =C.recordno
August 3, 2012 at 1:17 am
can you please check car table
vinno 12345,12346 have same model_id 25. based on that one will come output.
pls try below code...
i have changed vinno(12346) realted model_id
CREATE TABLE #car
(
vinno INT PRIMARY KEY,
model_id INT
)
INSERT INTO #car VALUES (12345,25), (12346,21), (12347,26)
--drop table #car
CREATE TABLE #sales_bill
(
model_id INT NOT null,
billno INT NOT null,
PRIMARY KEY
(
model_id, billno
) )
INSERT INTO #sales_bill VALUES (25, 107), (25, 108), (26, 107)
CREATE TABLE #record
(
billno INT NOT NULL,
recordno INT NOT NULL,
PRIMARY KEY
( billno, recordno)
)
INSERT INTO #record VALUES (107,702),(108,8001),(108,8063),(108,8059),(108,8068)
CREATE TABLE #areaofsales
(
zone_id INT NOT NULL,
recordno INT NOT NULL,
PRIMARY KEY
(zone_id, recordno
))
INSERT INTO #areaofsales VALUES (1,8063),(2,8063),(1,8059),(2,8001),(1,8068),(1,702)
select distinct * from (select vinno,B.model_id,D.zone_id FROM #car A
JOIN #sales_bill B ON A.model_id =B.model_id
JOIN #record C ON B.BillNo =C.BillNo
JOIN #areaofsales D ON D.recordno =C.recordno) k
August 3, 2012 at 7:56 am
Thanks for reply, but in car table data is same pattern as I wrote. Yes model_id is repeated.
August 3, 2012 at 8:24 am
I'm not sure what are you trying to do.
Does a model_id can have more than 3 vinno?
Would this help?
SELECT DISTINCT
A.vinno,
A.model_id,
(SELECT TOP 1 D.zone_id
FROM sales_bill b
JOIN record C ON B.BillNo =C.BillNo
JOIN areaofsales D ON D.recordno =C.recordno
WHERE A.model_id =B.model_id) AS zone_id
FROM car A
August 3, 2012 at 8:24 am
Dont you just need to do an order by vinno asc or zone_id desc
edit, didnt test with sample data, not just an order by needed
August 3, 2012 at 8:27 am
Something like this
with cte
as
(
select
car.vinno,
car.model_id,
--sales_bill.billno,
--record.recordno,
max(areaofsales.zone_id) as Zone
from
car
inner join
sales_bill
on
car.model_id = sales_bill.model_id
inner join
record
on
sales_bill.billno = record.billno
inner join
areaofsales
on
record.recordno = areaofsales.recordno
group by
car.vinno,
car.model_id
),
cte2 as
(
SELECT
ROW_NUMBER() OVER(PARTITION BY model_id ORDER BY Vinno) AS RowNum, Vinno, model_id, Zone
FROM
CTE
)
SELECT
Vinno, model_id, CASE WHEN Zone > RowNum THEN Zone - RowNum ELSE Zone End AS Zone
FROM
cte2
Gives this result
Vinno | model_id | Zone
12345 | 25 | 1
12346 | 25 | 2
12347 | 26 | 1
August 3, 2012 at 12:03 pm
Thanks luis, it's almost close what I want
August 3, 2012 at 12:04 pm
Thanks anthony.green, this is perfect. You are awesome.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply