Can these queries be run as one?

[jmcg]

I have been trying to match 2 tables to produce a set of results showing all records from Features and any that match
in StockFeatures and to show the CarID.
StockFeatures will have other CarID and these should not be returned.
View1

SELECT      CarID, Feature
FROM          v100cars.StockFeatures
WHERE      (CarID = 16)

View2

SELECT      Feature
FROM          v100cars.Features
WHERE      (NOT (Feature LIKE N' %'))

Results

SELECT      View1.CarID, View2.Feature
FROM          View1 RIGHT OUTER JOIN
                        View2 ON View1.Feature = View2.Feature

This produces a list of all Feature along with the CarID where they match.

I'm want to know if there is a simple way of doing this in one query as this final query will not work when I use in my coldfusion app (this is not a question about CF, it does not support outer joins in query on query)

 

[ousoonerjoe]

Give this a shot. You may need to adjust the JOIN, but I think this will at least get you on the right path:

SELECT sf.CarID, f.Feature
FROM v100cars.StockFeatures sf
	INNER JOIN v100cars.Features f ON f.Feature = sf.Feature AND (f.Feature NOT LIKE N' %')
WHERE (sf.CarID = 16)

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[bborissov]

Could you please post some example data and what you want as a final result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jmcg]

ousoonerjoe
That seems to just return the rows where they match.


bborissov
The data in Features Table
Feature
ABS
CD Player
Air conditioning
Airbag
Bluetooth
Body Coloured Bumpers
Split Folding Rear Seats
19in Alloys
ABS with EBD

Stock Features Table
CarID Feature
14 Split Folding Rear Seats
16 19in Alloys
16 ABS with EBD

The desird results would be showing all the features and the ID where the match, but not showing the car 14 entry:
CarID Feature
16 19in Alloys
16 ABS with EBD
ABS
CD Player
Air conditioning
Airbag
Bluetooth
Body Coloured Bumpers
Split Folding Rear Seats

 

[bborissov]

And how you decide which car not to show?
In the example you give us bot cars have matched record in Features table, but you want 16 to be shown and 14 not to be.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jmcg]

The first query limits the car to 16 only, the second shows all possible and the 3rd meges them into one recordset.

 

[ousoonerjoe]

OK. If you are wanting a list of all features AND the items for CarID 16, then just UNION the two lists together.

SELECT      CarID, Feature
FROM          v100cars.StockFeatures
WHERE      (CarID = 16)

UNION ALL

SELECT [CarID] = NULL, Feature
FROM          v100cars.Features
WHERE      (NOT (Feature LIKE N' %'))
ORDER BY CarID, Feature

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[bborissov]

SELECT ISNULL(StockFeatures.CarId, 0) AS CarId ,
       Features.Feature
FROM Features
LEFT JOIN StockFeatures ON Features.CarId = StockFeatures.CarId AND
          StockFeatures.CarId = 16

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jmcg]

Thanks again guys, but still not getting the results I was looking for

ousoonerjoe
You query returns all rows from Features and all rows from StockFeatures.
So in my example, instead of 9 rows, I get 11 with the ones with the CarID showing twice. IE:
16 19in Alloys
16 ABS with EBD
19in Alloys
ABS with EBD

bborissov
Your query links Features.CarId to StockFeatures.CarID. There is no CarId field in the features table.

Thanks again for trying, any further ideas are much appreciated.

 

[ousoonerjoe]

I'm sure there's a cleaner way, but put this in a Stored Procedure and call it. Should return what you're needing.

CREATE TABLE TmpTable_DeleteMe(
	CarID	INT,
	Feature	VARCHAR(50)) 
ON [PRIMARY]

INSERT INTO TmpTable_DeleteMe
SELECT CarID, Feature
FROM  v100cars.StockFeatures
WHERE (CarID = 16);

INSERT INTO TmpTable_DeleteMe
SELECT [CarID] = NULL, Feature
FROM v100cars.Features
WHERE (NOT (Feature LIKE N' %'));

DELETE FROM TmpTable_DeleteMe
WHERE (CarID IS NULL AND Feature IN(SELECT Feature FROM TmpTable_DeleteMe WHERE CarID <> NULL));

SELECT * FROM TmpTable_DeleteMe;

DROP TABLE TmpTable_DeleteMe;

When all else fails, get a bigger hammer.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[jmcg]

bborissov
Tweaked you query to this and it seems to do the trick

SELECT ISNULL(StockFeatures.CarID, 0) AS CarId, Features.Feature
	FROM Features LEFT OUTER JOIN StockFeatures ON Features.Feature = StockFeatures.Feature AND StockFeatures.CarID = 16

Apologies if I was sterring you wrong but thanks for the effort to both of you.