SQL Query not working perfectly

debugcn 投稿 Dev

ANJYR

I have three tables Chain, Hotel and Shell. Hotel table link with Chain table and shell table link with both of tables.

Table Structure :---

Chain //Level 2

ChainID    |    ChainName

Hotel //Level 3

HotelID    |    HotelName    |    ChainID

Shell

ShellID    |    ShellName    |    Chain_Hotel_ID    |    Level

In Shell Table, Chain_Hotel_ID column hold (Chain and Hotel) table ChainID and HotelID and Level Column Hold Int Value 2 and 3. Chain consider as 2 and Hotel 3

From these table i want

ShellID    |    ShellName    |    ChainName

I write a query for that but it not working as I want

select 
   _gs.ShellID, _gs.ShellName, _gc.ChainName 
from 
   Shell _gs, Chain _gc
where 
   _gc.ChainID in (select _lh.ChainID 
                   from Shell _ls,Hotel _lh 
                   where _lh.HotelID = _ls.Chain_Hotel_ID and _ls.Level = 3) 
   and _gs.Level = 3

Sample Table Data

Chain

ChainID    |    ChainName
   1       |    Sarovar
   2       |    Taj Group
   3       |    Oberoi

Hotel

HotelID    |    HotelName    |    ChainID
   1       |   RadhaRegent   |      1
   2       |   SarovarPortic |      1
   3       |   LaxmiVilas    |      1
   4       |   Taj Mumbai    |      2
   5       |   Taj Delhi     |      2
   6       |   Oberoi Delhi  |      3

Shell

ShellID    |    ShellName    |    Chain_Hotel_ID    |    Level
   1       |   Shell1        |        1             |      2
   2       |   Shell2        |        1             |      2
   3       |   Shell3        |        2             |      3
   4       |   Shell4        |        3             |      2
   5       |   Shell5        |        2             |      3
   6       |   Shell6        |        3             |      3
   7       |   Shell7        |        4             |      3

Output

ShellID    |    ShellName    |    ChainName    
   3       |   Shell3        |      Sarovar         
   5       |   Shell5        |      Sarovar         
   6       |   Shell6        |      Sarovar         
   7       |   Shell7        |      Taj Group   
   3       |   Shell3        |      Sarovar         
   5       |   Shell5        |      Sarovar         
   6       |   Shell6        |      Sarovar         
   7       |   Shell7        |      Taj Group

I got repeated data

Dudi Konfino

Is this what you need?

IF LEVEL=3
BEGIN
SELECT DISTINCT ShellID,  ShellName,  ChainName
FROM HOTEL JOIN SHELL
ON HOTEL.CHAINID=SHELL.CHAIN_HOTEL_ID
JOIN 
CHAIN ON CHAIN.CHAINID=HOTEL.CHAINID
END

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]