Showing posts with label pull. Show all posts
Showing posts with label pull. Show all posts

Saturday, February 25, 2012

Query Three Tables - Please Help

Hi

I need to be able pull certain data from our database. I need to find all stockitems (itemid column) that are a T item (binname column) and the memo to be created before the 01/02/2007 (timeanddatecreated column)

To get the data I need - I need to query three tables.

Stockitem - This has the column "itemid"
Stockitemmemo - This has the column "itemid" and "timeanddatecreated"
Binitem - This has the column "itemid" and "binname"

My results must be based on the following criteria.....

All the itemid's have a 'T' in binitem.binname and the memo must have been created before 01/02/2007.

I do have two questions based on the above...

1. Does it make sense what I need?
2. Is it possible

:confused:

Any help would be gratefully received.

Thanks

SimbaOK, help us out.

Read the hint sticky at the top of the forum and post what it asks for|||Possibly a straight Forward


SELECT Stockitem.itemid
,Stockitemmemo.Memo
,Binitem.binname
FROM Stockitem
INNER JOIN Stockitemmemo ON
Stockitem.itemid = Stockitemmemo.itemid
INNER JOIN Binitem ON
Stockitem.itemid = Binitem.itemid
WHERE Binitem.binname LIKE'%T%'
AND Stockitemmemo.timeanddatecreated <'01/02/2007'


Everyones gotta start somewhere :shocked:

Beware If there is NO matching record in either Stockitemmemo or Binitem table then the Stockitem row will NOT be returned - You would need to Consider a LEFT Join to deal with that.

GW

GW|||Your an absolute legend thanks very much! Appreciate your help.

Thanks again.:beer:|||You're Welcome :)|||What's in an absolut legend anyway? Sounds interesting, and my banana Kamikaze's are starting to get me in trouble, so I need a change.|||LOL Tall
What's in an absolut legend anyway? in the UK we call it "Horses for courses"

I'll gladly leave the difficult one's for the u true legends.

Not sure what a banana Kamikaze is but sounds like you need a TallBoy http://www.dbforums.com/showpost.php?p=6287499&postcount=30 - LOL

GW

Monday, February 20, 2012

Query table for cheapest items (but a little more complicated than that)

I'm not sure if this is even possible but can i pull out rows from two tables (that have a one-to-many relationship) but only if they satisfy a few conditions.

tblWine tblSources
ID Name ID WineID Source Price Status
------ -------------
1 Le Dome 1 1 Smith 100.00 IB
2 Teyssier 2 1 Jones 110.00 IB
3 Muscat 3 1 Hill 100.00 DP
4 2 Smith 135.00 DP
5 2 Hill 125.00 DP

I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.

tblWineSources
WineID Name SourceID Source Price Status
-------------------
1 Le Dome 1 Smith 110.00 IB
1 Le Dome 3 Hill 100.00 DP
2 Teyssier 5 Hill 125.00 DPPlease check your Resultsets they do not represent the requirement you stated...
BTW, for your help ...

CREATE TABLE tblWine(
ID VARCHAR(10)
,NAME VARCHAR(20))
Go
CREATE TABLE tblSources(
ID VARCHAR(10)
,WINEID VARCHAR(10)
,SOURCE VARCHAR(20)
,PRICE INT
,STATUS VARCHAR(10))
Go
INSERT INTO tblWine VALUES('1','Le Dome')
INSERT INTO tblWine VALUES('2','Teyssier')
INSERT INTO tblWine VALUES('3','Muscat')

INSERT INTO tblSources VALUES('1','1','Smith',100.00,'IB')
INSERT INTO tblSources VALUES('2','1','Jones',110.00,'IB')
INSERT INTO tblSources VALUES('3','1','Hill',100.00,'DP')
INSERT INTO tblSources VALUES('4','2','Smith',135.00,'DP')
INSERT INTO tblSources VALUES('5','2','Hill',125.00,'DP')
GO
--The required Query Place--
SELECT tblSources.WINEID
,tblWine.Name
,tblSources.ID as SourceID
,tblSources.Source
,tblSources.Status
,tblSources.Price as Maxprice
FROM tblWine
INNER JOIN tblSources
ON tblWine.ID=tblSources.WINEID

GROUP BY tblSources.WINEID
,tblWine.Name
,tblSources.ID
,tblSources.Source
,tblSources.Status
,tblSources.Price
----------

Go
DROP TABLE tblWine
DROP TABLE tblSources|||:) I realise that. That's because I don't want to return all the rows in the join. I only need the rows that have the cheapest price for their status. Since my first post I've managed to solve part of the problem.

SELECT
tblWine.ID AS WineID,
tblWine.Name,
tblSources.ID AS SourceID,
tblSources.Source,
tblSources.Status,
tblSources.Price
FROM
tblSources
LEFT OUTER JOIN tblWine ON tblSources.WineID = tblWine.ID
GROUP BY
tblWine.ID,
tblWine.Name,
tblSources.ID,
tblSources.Source,
tblSources.Status,
tblSources.Price
HAVING
tblSources.ID IN
(
SELECT
TOP 1
subSources.ID
FROM
tblSources subSources
WHERE
subSources.WineID = tblWine.ID
AND subSources.Status = 'IB'
ORDER BY
subSources.Price ASC
)
OR tblSources.ID IN
(
SELECT
TOP 1
subSources.ID
FROM
tblSources subSources
WHERE
subSources.WineID = tblWine.ID
AND subSources.Status = 'DP'
ORDER BY
subSources.Price ASC
)
ORDER BY
tblWine.ID ASC,
tblSources.ID ASC

Thanks for you help though.

The problem now is that I've made these tables simpler for the purpose of this post so not to confuse the issue. There is actually another column in tblSources which I need to check is unique just like status. This additional column can have 1 of 8 different values, and the only way I currently know how to do it is so expand the query above to accommodate 16 subqueries in the HAVING clause, which doesn't seem ideal, or is it?

So ...

tblWine tblSources
ID Name ID WineID Source Price Status Format
------ -------------
1 Le Dome 1 1 Smith 100.00 IB Bottle
2 Teyssier 2 1 Jones 110.00 IB Bottle
3 Muscat 3 1 Hill 100.00 DP Bottle
4 2 Smith 135.00 DP Bottle
5 2 Hill 125.00 DP Magnum

... would return ...

tblWineSources
WineID Name SourceID Source Price Status Format
-------------------
1 Le Dome 1 Smith 110.00 IB Bottle
1 Le Dome 3 Hill 100.00 DP Bottle
2 Teyssier 5 Smith 135.00 DP Bottle
2 Teyssier 5 Hill 125.00 DP Magnum

So because there are two sources for Le Dome / IB / Bottle, only the cheapest record would be shown.|||If no one can help with the above, could someone tell me if the following is even possible?

How do I find the IDs of the cheapest Format/Status pair?

So ...

tblSources
ID Price Format Status
-----------
1540 100.00 Bottles IB
1541 110.00 Bottles DP
1542 105.00 Bottles IB
1543 105.00 Bottles DP
1544 115.00 Magnums IB
1545 110.00 Magnums IB

... would result in ...

Results
ID
--
1540
1543
1545

I could use a query for each combonation but the actual data can contain 1 of 2 different statuses or 1 of 8 different formats.|||try this as the solution to your problem in post #1select W.ID as WineID
, W.Name
, S.ID as SourceID
, S.Source
, S.Price
, S.Status
from tblWine as W
inner
join tblSources as S
on S.WineID = W.ID
and S.Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status )|||But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.|||But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? yep, that is correct

One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.which source?|||any source, doesn't really matter. but as long as only one is shown.|||thanks to you, i've sussed it!

here's an example of what i did (i used a specific wine with many sources, in this case it had an ID of 11)

SELECT
tblSources.*
FROM
tblSources
WHERE
tblSources.WineID = 11
AND tblSources.ID =
(
SELECT
TOP 1
subSources.ID
FROM
tblSources AS subSources
WHERE
subSources.WineID = tblSources.WineID
AND subSources.Format = tblSources.Format
AND subSources.Status = tblSources.Status
AND subSources.CasePrice =
(
SELECT
MIN(sub2Sources.CasePrice)
FROM
tblSourcesAS sub2Sources
WHERE
sub2Sources.WineID = subSources.WineID
AND sub2Sources.Format = subSources.Format
AND sub2Sources.Status = subSources.Status
)
)|||select W.ID as WineID
, W.Name
, S.ID as SourceID
, S.Source
, S.Price
, S.Status
from tblWine as W
inner
join tblSources as S
on S.WineID = W.ID
and S.Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status )
and S.Source =
( select min(Source)
from tblSources
where WineID = S.WineID
and Status = S.Status
and Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status ) )