July 14, 2003 at 5:19 pm
Hello,
I have a hassle with this query.
kind of hard to explain, but in plain English, I want to run a query that will list the names of stores that were involved in the transfer of goods between themselves.
I have a tblJob which can potentially list the id of:
1) a store that the products will be transferred FROM
2) a store that the products will be transferred TO.
The query that I have so far only gives me the NAME of the store that the products will be transferred FROM.I would like to be able to obtain the NAME of the store that the products will be transferred TO
I think I am close to an answer...and to the abyss 🙂
schema:
tblJob
jobId PRIMARY KEY INT NOT NULL,
fromStoreId INT NOT NULL REFERENCES tblStore(storeId),
toStoreId INT REFERENCES tblStore(storeId)
tblStore
storeId PRIMARY KEY INT NOT NULL,
storeName VARCHAR(50)
I have 2 other tables, but I don't think they're part of the problem.
'*********************
tblProduct
productId PRIMARY KEY INT NOT NULL,
productName VARCHAR (50)
tblClient
clientId PRIMARY KEY INT NOT NULL,
clientName VARCHAR (50)
'*********************
The query is as follows:
<code>
SELECT DISTINCT
a.JobId,
b.storeId,
b.storeName,
a.toStoreId,
FROMtblJob a,
tblStore b,
tblClient cl
WHERE
AND cl.clientId = b.clientId
AND a.storeId = b.storeId
AND a.toStoreId <> NULL
AND cl.clientId = 1
<\code>
So, it will return:
jobId, storeId, storeName, toStoreId
1 , 10 ,Aberdeen, 11
2 , 15 ,Dundee, 20
3 , 15 ,Dundee, 23
Therefore, 'storeName' is the name of the store that the goods were transferred FROM.
Is there any way that I can also obtain the 'toStoreName', (ie, the name of the store that the goods will be transferred TO) based on the 'toStoreId' that I can return from tblJob?
Many thanks,
yogiberr.
July 14, 2003 at 5:29 pm
How about something like this (not tested):
SELECT DISTINCT
a.JobId,
a.fromstoreId,
b.storeName,
a.toStoreId,
c.storeName
FROM tblJob a,
tblStore b,
tblstore c,
tblClient cl
WHERE
AND cl.clientId = b.clientId
AND a.fromstoreId = b.storeId
and a.tostoreid = c.storeid
AND a.toStoreId <> NULL
AND cl.clientId = 1
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
July 15, 2003 at 3:56 am
Hi Greg.
That did the trick, thanks.
Went to the the link..I'll be a frequent flyer at that site.
Cheers,
yogi
July 15, 2003 at 3:52 pm
Hi folks,
I'm stuck again, sorry about this.
The query so far will return all the 'toStoreNames' when the 'tblJob.toStoreId' is not NULL.
Unfortunately,
'tblJob.toStoreId' can be NULL. In other words, not all jobs involve transferring products between stores, some jobs take place in a SINGLE store.
So, I would like to be able to return the 'toStoreName' as NULL
when the 'tblJob.toStoreId' is NULL
I have changed the query to:
<code>
SELECT DISTINCT
a.JobId,
a.fromstoreId,
b.storeName,
a.toStoreId,
c.storeName
FROM
tblJob a,
tblStore b,
tblstore c,
tblClient cl
WHERE
cl.clientId = b.clientId
AND a.fromstoreId = b.storeId
AND (a.toStoreid = c.storeId OR a.toStoreId = NULL)
</code>
I added the 'OR a.toStoreId = NULL' clause.
Unfortunately, I get the following results for jobs where the id of 'a.toStoreId' is NULL :
jobId,storeId,storeName,toStoreId,storeName
1 , 10 ,Aberdeen, NULL, Aberdeen
1 , 10 ,Aberdeen, NULL, Belfast
1 , 10 ,Aberdeen, NULL, Birmingham
2 , 15 ,Dundee, NULL, Aberdeen
2 , 15 ,Dundee, NULL, Belfast etc
So, I believe that the 5th column of my query
is simply listing all the store names alphabetically.
I am quite certain that the following section of the query is at fault:
'AND (a.toStoreid = c.storeId OR a.toStoreId = NULL)'
But I've been at it for ages without success.
Can anyone point me in the right direction?
cheers,
yogi
July 16, 2003 at 6:33 am
Try this (untested)
SELECT DISTINCT
a.JobId,
a.fromstoreId,
b.storeName,
a.toStoreId,
c.storeName
FROM tblJob a
INNER JOIN tblClient cl ON cl.clientId = b.clientId
INNER JOIN tblStore b ON b.storeId = a.fromstoreId
LEFT OUTER JOIN tblstore c ON c.storeId = a.toStoreid
Far away is close at hand in the images of elsewhere.
Anon.
July 18, 2003 at 7:13 am
Hi David,
Nice one 🙂
Thanks,
yogiberr
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply