Strange Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Strange Query

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2002-10-21 : 14:38:26

Hello-

I am trying to write a query that has me stumped. I have a table with a field itemID. I then have another table with itemID and neededID.

I need the query to return the neededID of any itemID that are in both tables where neededID does not appear in the first tables itemID field.

Even putting it into words is hard. Let me give an example.

Table1
------
101
102
103

Table2
------
101 102
101 104

I would want the query to return 104 because 101 is in table 1, but 104 isn't. Basically Table2 lists the requirements of table 1. if 101 is in table 1, 104 should be to. So they query returns to me the values that are in violation so i can do error handling.

If anyone can decipher what I just tried to say, can you please help.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-21 : 14:58:54

Nick, does Table2 have 2 columns or is it one column containing multi-values? ...

In either case ... you probably wanted a 'WHERE NOT EXISTS' with a correlated subquery. Somethink like ..


select
    p.ProductName
from
    Northwind.dbo.Products p
where
    not exists (
        select 1
        from
            Northwind.dbo.Orders
        where
            p.ProductId = ProductID)

Jay White
{0}

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-10-21 : 15:02:43

A few background items:
1) Always show the DDL for the tables you discuss. It is the easiest and least ambiguous method.
2) Tables have columns, not fields.

My best guess from your description:

select *
from table2 t2
inner join table1 t1 on t2.itemID = t1.itemID
where not exists (
select 1
from table1
where itemID = t2.neededID )

Jonathan
{0}

Nick
Posting Yak Master

155 Posts

Posted - 2002-10-21 : 15:05:14

Those are two separate columns. My original idea was something like this.

select table2.neededID FROM table1, table2 WHERE table1.itemID IN (select itemID FROM table2) AND table2.neededID NOT IN (select itemID from table1)

I'm not sure if that would work or not though.

quote:

Nick, does Table2 have 2 columns or is it one column containing multi-values? ...

In either case ... you probably wanted a 'WHERE NOT EXISTS' with a correlated subquery. Somethink like ..

select
p.ProductName
from
Northwind.dbo.Products p
where
not exists (
select 1
from
Northwind.dbo.Orders
where
p.ProductId = ProductID)

Jay White
{0}

Nick
Posting Yak Master

155 Posts

Posted - 2002-10-21 : 15:12:13

This is close to working, but if any of the required items are in table1, I don't want them showing up in the query. The user is required to have atleast one of the items, but not nessecarily all of them.

quote:

A few background items:
1) Always show the DDL for the tables you discuss. It is the easiest and least ambiguous method.
2) Tables have columns, not fields.

My best guess from your description:

select *
from table2 t2
inner join table1 t1 on t2.itemID = t1.itemID
where not exists (
select 1
from table1
where itemID = t2.neededID )

Jonathan
{0}

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-10-21 : 15:15:00

You need to explain what "table1" and "table2" are in greater depth - what entities do they represent, what are their attributes (columns), etc. It's unclear from your post what you're after, exactly.

Jonathan
{0}

Edited by - setbasedisthetruepath on 10/21/2002 15:15:35

Nick
Posting Yak Master

155 Posts

Posted - 2002-10-21 : 15:37:36

OK... This should explain it a little better.

The first table is tblShoppingCarts_Items. It has two columns: shoppingCartID and itemID.

The second table is called tblCompanionItems. This table has two columns as well. They are both itemID's. The first is called orderedID the second is called neededID.

The problem boils down to this. If a user puts an item in his/her shopping cart, i need to run a query. If the item they put in the cart is in the tblCompanionItems orderedID column, it will have to check to make sure at least one of the neededID's is also in the shopping cart. If atleast one of them is (not all of them) it will not need to return any items.

I need this functionality because there are certain items that require you order another item to go along with them. For instance if you order a console, you have to order a controller as well, but it could be one of many different style controllers, not just a single type. If no controller is in the shopping cart, I need to handle that. If any of the controllers is in the shopping cart, nothing should be returned by the query, and no error will be generated.

I hope this clears it up a little.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-21 : 16:19:18

SELECT neededID
FROM Table2
WHERE itemID IN (SELECT itemID FROM Table1)
AND neededID NOT IN (SELECT itemID FROM Table1)

Hmm... give or take half a join, isn't that what Setty said?

Edited by - Arnold Fribble on 10/21/2002 16:24:33

Nick
Posting Yak Master

155 Posts

Posted - 2002-10-21 : 16:22:43

That query doesn't work if there are 3 items that are associated with one of the items ordered.

quote:

SELECT neededID
FROM Table2
WHERE itemID IN (SELECT itemID FROM Table1)
AND neededID NOT IN (SELECT itemID FROM Table1)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-21 : 16:31:18

I don't understand. Can you give an example of where this doesn't work and what you would want in that case?

Nick
Posting Yak Master

155 Posts

Posted - 2002-10-21 : 16:56:23

Table1
------
item1
item2

Table2
------
item1 item2
item1 item3
item1 item4

Your query will return:

Query
-----
item3
item4

Because atleast one of the required items is in table one your query should return nothing.

quote:

I don't understand. Can you give an example of where this doesn't work and what you would want in that case?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-21 : 18:58:19

Ok, this is a pretty weird one, but I think I've got it. I think some of the real pros could cleanthis up a bit, but here goes.


CREATE TABLE #Table1(ItemID VARCHAR(50))

CREATE TABLE #Table2(ItemID VARCHAR(50), NeededID VARCHAR(50))

INSERT INTO #Table1(ItemID) VALUES('item1')
INSERT INTO #Table1(ItemID) VALUES('item2')

INSERT INTO #Table2(ItemID, NeededID) VALUES('item1', 'item2')
INSERT INTO #Table2(ItemID, NeededID) VALUES('item1', 'item3')
INSERT INTO #Table2(ItemID, NeededID) VALUES('item1', 'item4')

DECLARE @GotOneNeeded INT

SELECT @GotOneNeeded = CASE WHEN (SELECT Count(*) FROM #Table2 WHERE neededID in (SELECT itemID FROM #Table1)) > 0 THEN 1 ELSE 0 END

--SELECT @GotOneNeeded

IF @GotOneNeeded > 0
	BEGIN
		SELECT '' as NeededID
	END
ELSE
	BEGIN
		SELECT neededID 
		FROM #Table2 
		WHERE itemID IN (SELECT itemID FROM #Table1) 
		AND neededID NOT IN (SELECT itemID FROM #Table1)
	END

DROP TABLE #Table2
DROP TABLE #Table1

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Nick
Posting Yak Master

155 Posts

Posted - 2002-10-21 : 20:38:43

That worked perfectly. Thank you so much.

quote:

Ok, this is a pretty weird one, but I think I've got it. I think some of the real pros could cleanthis up a bit, but here goes.


CREATE TABLE #Table1(ItemID VARCHAR(50))

CREATE TABLE #Table2(ItemID VARCHAR(50), NeededID VARCHAR(50))

INSERT INTO #Table1(ItemID) VALUES('item1')
INSERT INTO #Table1(ItemID) VALUES('item2')

INSERT INTO #Table2(ItemID, NeededID) VALUES('item1', 'item2')
INSERT INTO #Table2(ItemID, NeededID) VALUES('item1', 'item3')
INSERT INTO #Table2(ItemID, NeededID) VALUES('item1', 'item4')

DECLARE @GotOneNeeded INT

SELECT @GotOneNeeded = CASE WHEN (SELECT Count(*) FROM #Table2 WHERE neededID in (SELECT itemID FROM #Table1)) > 0 THEN 1 ELSE 0 END

--SELECT @GotOneNeeded

IF @GotOneNeeded > 0
	BEGIN
		SELECT '' as NeededID
	END
ELSE
	BEGIN
		SELECT neededID 
		FROM #Table2 
		WHERE itemID IN (SELECT itemID FROM #Table1) 
		AND neededID NOT IN (SELECT itemID FROM #Table1)
	END

DROP TABLE #Table2
DROP TABLE #Table1

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-21 : 21:45:02

Here's an alternative.

SELECT B.NeededID
FROM
Table1 A
INNER JOIN Table2 B ON A.ItemID = B.ItemID
LEFT JOIN Table1 C On B.NeededID = C.ItemID
WHERE C.ItemID Is Null

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-22 : 02:45:12

quote:

Because atleast one of the required items is in table one your query should return nothing.

So why was the first example supposed to return 104?

quote:

Table1
------
101
102
103

Table2
------
101 102
101 104

I would want the query to return 104 because 101 is in table 1, but 104 isn't.

Edited by - Arnold Fribble on 10/22/2002 03:00:11

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources