Trouble in querying data

query

I'm having difficulty in retrieving data from tables.
Bellow is the scenario I'm stuck at.

TableA

Id	Name
1	ABC
2	DEF
3	GHI
4	JKL

TableB

Id	TableA_Id	Tag
1	1	X
2	2	Y
3	0	Z

TableC

Id	TableA_Id	Something
1	1	XXX
2	1	XXX
3	1	XXX
4	2	XXX
5	2	XXX
6	3	XXX
7	3	XXX
8	4	XXX
9	4	XXX

TableA joins With TableB by TableA_Id column, the only difference is that where there is no reference of TableA id then we are supposed to return the row with TableA_Id = 0 from TableB.

Required Output

Name	Tag	Something
ABC	X	XXX
ABC	X	XXX
ABC	X	XXX
DEF	Y	XXX
DEF	Y	XXX
GHI	Z	XXX
GHI	Z	XXX
JKL	Z	XXX
JKL	Z	XXX

Best Answer

SELECT t1.name,
       COALESCE(t2.tag, t3.tag) tag,
       t4.something
FROM tableA t1
LEFT JOIN tableB t2 ON t1.id = t2.TableA_Id
JOIN tableB t3 ON t3.TableA_Id = 0
JOIN tableC t4 ON t1.id = t4.TableA_Id

TableB must contain only one row with TableB.TableA_Id = 0. If not then use

SELECT t1.name,
       COALESCE(t2.tag, t3.tag) tag,
       t4.something
FROM tableA t1
LEFT JOIN tableB t2 ON t1.id = t2.TableA_Id
CROSS JOIN ( SELECT *
             FROM tableB 
             WHERE TableA_Id = 0
             -- ORDER BY something, for example, by `Id`
             LIMIT 1 ) t3
JOIN tableC t4 ON t1.id = t4.TableA_Id

fiddle

Related Solutions

Ms-access – Querying special characters

You may need to try something adventurous like coding in the escape sequences.

select * from a where name like '%' & chr$(numberforcharacter) & %'

Sql-server – querying a table of events for reporting

I'd add a PriorShipments field to the fact table. You'll have a lot of rows, but clustering on the ShipDate field should be pretty efficient, and querying would be quite simple.

CREATE TABLE #Shipments
(
ID  INT NOT NULL,
ShipDate    DATE NOT NULL,
PRIMARY KEY (ID, ShipDate),
CustomerID  INT NOT NULL,
PriorShipments  INT NOT NULL DEFAULT 0
)
INSERT INTO #Shipments (ShipDate, ID, CustomerID) VALUES
    ('2005-08-10',   7112,  942), 
    ('2007-07-15',   8798,  160), 
    ('2009-04-03',   8798,  160), 
    ('2009-04-15',   8798,  160), 
    ('2009-04-21',   8798,  160), 
    ('2009-04-21', 145751,  139), 
    ('2009-04-22',   7112,  942), 
    ('2009-04-22',  12121, 1015), 
    ('2009-04-25',   8798,  160), 
    ('2009-05-12',   8798,  160)

UPDATE RS
SET PriorShipments = NumPrev
FROM #Shipments AS RS
    INNER JOIN
        (
        SELECT S1.ID, S1.ShipDate, COUNT(*) AS NumPrev
        FROM #Shipments AS S1
            INNER JOIN #Shipments AS S2 ON S1.ID = S2.ID AND S2.ShipDate < S1.ShipDate
        GROUP BY S1.ID, S1.ShipDate
        ) AS Seq ON RS.ID = Seq.ID AND RS.ShipDate = Seq.ShipDate

-- If Farmer Brown gets a Dept of Agriculture grant to upgrade his database, he could instead use:
UPDATE RS
SET PriorShipments = Seq - 1
FROM #Shipments AS RS
    INNER JOIN
        (
        SELECT ID, ShipDate, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ShipDate) AS Seq
        FROM #Shipments
        ) AS Seq ON RS.ID = Seq.ID AND RS.ShipDate = Seq.ShipDate

-- The good part
SELECT COUNT(*) FROM #Shipments WHERE ShipDate BETWEEN '2009-04-01' AND '2009-04-30'
SELECT COUNT(*) FROM #Shipments WHERE ShipDate BETWEEN '2009-04-01' AND '2009-04-30' AND PriorShipments = 1
SELECT COUNT(*) FROM #Shipments WHERE ShipDate BETWEEN '2009-04-01' AND '2009-04-30' AND PriorShipments >= 2

You can pre-aggregate this a little, as long as Farmer Brown is never going to care about to which farms he has been shipping peaches.

Movin' to the country, gunna eat me a lot of peaches...

Best Answer

Related Solutions

Ms-access – Querying special characters

Sql-server – querying a table of events for reporting

Related Question