query assistance: 2012

Friday, March 30, 2012

query help

Hi,
I want to bring up the following data for groups:
Blue, Red, Green and Yellow.
I have the following view in sql 7 which brings up the data i want, How can
i bring up the data for the other groups within the same query.
SELECT DISTINCT
salesorders.srep, SUM(salesitems.sprice) AS Expr1,
delv.dtaxd
FROM dbo.salesorders INNER JOIN
dbo.salesitems ON
dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
dbo.delvitems ON
dbo.salesorders.son = dbo.delvitems.dord AND
dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
dbo.delv ON
dbo.delvitems.delvnoa = dbo.delv.delvno
WHERE (dbo.salesorders.srep = 'blue') AND
(dbo.delv.dedate > CONVERT(DATETIME,
'2008-02-01 00:00:00', 102))
GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
I then need to call this query witin MS Access and use it to output to a
Data sheet.
Thanks
Mohammad
A little more info.
The data i want will look like this as an example:
Team price Date
==== ==== ===
Blue 500 01/01/2008
Green 600 04/02/2008
Yellow 2000 01/02/2008
"mahmad" wrote:

> Hi,
> I want to bring up the following data for groups:
> Blue, Red, Green and Yellow.
> I have the following view in sql 7 which brings up the data i want, How can
> i bring up the data for the other groups within the same query.
> SELECT DISTINCT
> salesorders.srep, SUM(salesitems.sprice) AS Expr1,
> delv.dtaxd
> FROM dbo.salesorders INNER JOIN
> dbo.salesitems ON
> dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
> dbo.delvitems ON
> dbo.salesorders.son = dbo.delvitems.dord AND
> dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
> dbo.delv ON
> dbo.delvitems.delvnoa = dbo.delv.delvno
> WHERE (dbo.salesorders.srep = 'blue') AND
> (dbo.delv.dedate > CONVERT(DATETIME,
> '2008-02-01 00:00:00', 102))
> GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
> I then need to call this query witin MS Access and use it to output to a
> Data sheet.
> Thanks
> Mohammad

I have a table that keeps track of plastic balls in tubs. Based on a bit column the record is either defining balls added to a tub or taken away. When the record is defined as adding balls to a tub it will say how many and what color, but when the flag says they have been taken away from a tub I only know the number removed and not the color.

There are multiple tubs and multiple colors. So say tub1 has 20 balls in it (50% red, %25 green, and %25 blue). Also say tub2 has 10 balls in it (100% red). This is our starting point.

Now on day one, 5 balls from tub two are put into tub one. So we know that 5 balls of 100% red are put into tub one. This means that tub one now has 25 balls in it. By doing some weighted percentages, tub one now has these percentages: red = 60%, green = 20%, and blue = 20%.

Say on day two however, 5 balls are removed from tub one and placed back into tub two. We cannot say anything about the colors, but that they are: .6red, .2green, and .2 blue. So if we want a percentage for tub two on day 2 we now get: .8red, .1green and .1blue.

The math for the new percentage is I believe = ((originalPercent * originalCount) + (addedPercent * addedAmount)) / newTotalBallCount

I need a query that will give me the percentages of the different colors in the tub for any given day. This is really a running percentage that has to take every transaction into account.

This is a complicated query fro me to figure out, but can someone point me in the right direction?

LLeuthard wrote:

Say on day two however, 5 balls are removed from tub one and placed back into tub two. We cannot say anything about the colors, but that they are: .6red, .2green, and .2 blue. So if we want a percentage for tub two on day 2 we now get: .8red, .1green and .1blue.

too superfluous.

is this thing about permutation and combination/? how did u say taht 6r,2g,2b?

|||Are you saying it is not worth my time or that it is impossible? Is there a stored proc I could write that would do this easily?|||

I got .6Red .2Blue and .2Green by saying that:

5 balls of 100% red were moved to tub1. so tub1 originally has a estimate of .5*20 = 10Red balls.

Take the estimate of 10Red and add the estimate of 5Red and we get an estimate of 15Red out of 25 in tub1. This makes for .6Red at the end of day 1.

Do the same with the others.

Query help

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)

-- select statment
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)

--selecting all record
select * from @.test

need helpDo you want to delete all except the last date in each account or only delete the earliest date in each account?|||

Dhaval:

I looked through what you requested. See if below is what you mean

Dave

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)
INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1180, '07/05/2006',5 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1
-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.
/*
--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)
*/
print '-- '
print '-- Records before deletions: -- '
print '--'
select * from @.test order by accountNo, invoiceDate
delete from @.test
from ( select accountNo,
min (invoiceDate) as min_invoiceDate
from @.test
group by accountNo
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.min_invoiceDate = a.invoiceDate
delete from @.test
from (
select accountNo,
invoiceDate,
min (dex_row_id) min_dex_row_id,
count(*) as recCt
from @.test
group by accountNo,
invoiceDate
having count(*) > 1
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.invoiceDate = a.invoiceDate
and x.min_dex_row_id <> a.dex_row_id

-- select statment
/*
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)
*/
--selecting all record
print ' '
print ' '
print '-- '
print '-- Records after deletions: -- '
print '--'
select * from @.test
--need help
-- --
-- -- Records before deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-04-05 00:00:00.000 3
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1180 2006-07-05 00:00:00.000 5
-- 1181 2006-05-05 00:00:00.000 3
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2
-- 1182 2006-06-05 00:00:00.000 1

-- --
-- -- Records after deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2

Query Help

I have this scenario. What will be my query?

Table:

Account#, Name, RMR, Billing_Date, Invoice#

1000,Dave,50, 5/1/2006,10

1000,Dave,50, 6/1/2006,11

1000,Dave,50, 7/1/2006,12

1000,Dave,50, 8/1/2006,13

1000,Dave,50, 9/1/2006,14

2000,Al,50, 5/15/2006,15

2000,Al,50, 6/15/2006,16

2000,Al,50, 7/15/2006,17

2000,Al,50, 8/15/2006,18

3000,Jim,50, 8/10/2006,19

3000,Jim,50, 9/10/2006,20

3000,Jim,50, 10/10/2006,21

I use this query to calculate revenue sharing.

Account hit for revenue sharing after we bill the 4^th billing month. In this case for Account# 1000 qualified after 8/1/2006, 2000 – 8/15/2006 and 3000 will be qualified after 11/10/2006. Each month we will pay 4% of RMR for all qualified accounts.

I want a query result with return for each month and find out how many accounts are qualified for each month.

In this case:

Septmeber-2006 -1000

Septmeber-2006 -2000

Octomber-2006 -1000

Could you define your result again based on the sample data you posted, which will help to understand your question. Thanks.|||

Dhaval:

I was able to mock-up what you described below. Is this more-or-less what you are looking for?

Dave

declare @.rmrMockUp table
( Account# integer not null,
Name varchar (20) not null,
RMR numeric (9,2) not null,
Billing_Date datetime not null,
Invoice# integer not null
)
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '5/1/2006', 10 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '6/1/2006', 11 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '7/1/2006', 12 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '8/1/2006', 13 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '9/1/2006', 14 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '5/15/2006', 15 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '6/15/2006', 16 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '7/15/2006', 17 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '8/15/2006', 18 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '8/10/2006', 19 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '9/10/2006', 20 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '10/10/2006', 21 )
declare @.monthList varchar (250)
set @.monthList = 'January February March April May June July August September October November December '
select Account#,
rtrim (substring (@.monthList,
month (dateadd (month, 1, Billing_date))*10-9, 10)) + '-' +
convert (char (4), year (dateadd (month, 1, Billing_date)))
as RMR_Month
from
( select Account#,
rmr,
Rank() over (partition by Account# order by Billing_Date)
as activeMonths,
Billing_Date
from @.rmrMockUp
) x
where activeMonths >= 4
order by Billing_Date, Account#
-- -
-- Query Output:
-- -
-- Account# RMR_Month
-- --
-- 1000 September-2006
-- 2000 September-2006
-- 1000 October-2006

Query help

I need help with this query.

My query is a lot more complicated than this but I have distilled it down to this in an example. Since this query will involve many tables and many rows I need it to be as efficient as possible.

My query is retrieving "car", each of these "car"s can have N "features", each "feature" can have 1 "car". I need to get a COUNT on the "features".

Here is what my query looks like so far:

SELECT Car_ID, Car_Name, Manufacturer_ID
FROM [Car], [Manufacturer]
WHERE Car_ManID = Manufacturer_ID

I need to add in something like this:

SELECT Car_ID, Car_Name, Manufacturer_ID, Count(Features) as FeatureCount
FROM [Cars], [Manufacturer], [Feature]
WHERE Car_ManID = Manufacturer_ID AND Feature_CarID = CarID AND CarID = '4'

Now, I know this won't work but you get the point.

Basically, I want to have results like this

Car_ID Car_Name Manufactuere_ID FeatureCount
12 Escort Ford 12
34 Tahoe Chevy 0

Then query must account for feature counts of zero, so I assume some sort of outer join will be needed?add a group by


SELECT Car_ID, Car_Name, Manufacturer_ID, Count(Features) as FeatureCount
FROM [Cars], [Manufacturer], [Feature]
WHERE Car_ManID = Manufacturer_ID AND Feature_CarID = CarID AND CarID = '4'
group by Car_ID, Car_Name, Manufacturer_ID

hth|||Thanks for the reply, I actually just found out about MS SQL derived tables. That works perfect!

query help

I have a table with multiple records for an identical person. It is a rolling history of the applications a user has submitted. How can I grab the most current application by the date most closest to todays date. Obviously one of my fields is an applied date.
I need help in creating a query.select * from table t where datefield = (select max(datefield) from table where userid = t.userid)

Nick

Query help

My table are

Customer: customerId ,name

Order: orderId, customerId, product,date

I want to display all of the customer which have order or not. I want display name, product,date . If the customer do not order I want display only customer name.For example:

Name Product Date

John Video 09/20/2007

Mary -- ----

How can I write sql or sp?

I suggest you do some reading on SQL and joins in particular as this is something that you should learn so you can write these queries yourself.

DECLARE @.CUSTOMERTABLE (customeridint IDENTITY(1,1),name varchar(20))DECLARE @.ORDERSTABLE (orderidint IDENTITY(1,1), customeridint, productvarchar(20), orderdatedatetime)INSERT @.CUSTOMERVALUES ('Fred')INSERT @.CUSTOMERVALUES ('Joe')INSERT @.ORDERSVALUES (1,'Video',GetDate())SELECT c.name, o.product, o.orderdateFROM @.CUSTOMER cLEFTOUTER JOIN @.ORDERS oON o.customerid = c.customerid

|||

use left join instead of inner join

select cust.customerId ,cust.name,ord.orderId, ord.customerId, ord.product,ord.date from Customer cust left outer join orders ord on

cust.CustomerId = ord.CusomerId

|||

Hi,

I think you have to create a cross-tab query, it's ilttle tricky but interesting.

Check these following links

http://www.databasejournal.com/features/mssql/article.php/3521101

http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html

or you can adopt the following solution

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html

Regards,

Sandeep

|||

ASP.NET Dev:
I think you have to create a cross-tab query,

That's not necessary as they aren't pivoting any data (or at least that doesn't appear to be the case based on their description).

|||

Ozo:
How can I write sql or sp?

you can write sql as..

select customer.name, order.product, order.orderdate from customer left outer join order on customer.customerid = order.customerid

and sp as..

CREATE PROCEDURE [dbo].[usp_CustomerOrder]
AS
BEGIN
SET NOCOUNT ON
select customer.name, order.product, order.orderdate from customer leftouter join order on customer.customerid = order.customerid
END

|||

Tahnk you for your helping.

|||

Ozo:
Tahnk you for your helping.

You should also mark all the posts that helped by using the "Mark As Answer" link so that future readers with the same problem will know which methods to use.

|||

I have a new question I want to display the latest order from the customer .Can you help me?

|||

Yes, but please start a new question if you have something else to ask as it helps keep the forum tidy and easier to search.

Query help

Hello Everyone,

Please can you help me with this?

I have an audit table, so there are many id's and modified dates... I am looking to get the last updated record for each ID. This is what I have so far... "which still gives duplicate ID's"

I have see many pages about distincta and max/min... I cannot make sense of it... HELP

1Select *2FROM TabelA3WhereExists (SELECT distinct max (id)as id ,max (ModifiedDate)as ModifiedDate4FROM TabelA)5Order by id

I tried to break it down, and still I get duplicate ID's

1Select *2FROM TabelA3Where idIN (SELECT distinct id4FROM TabelA)5Order by id

Try

SELECT *FROM TableAWHERECONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDEXISTS IN (selectCONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDas [key]from TableAgroup by ID)ORDER BY ID

|||

Thanks for your reply,

I get the following error

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'EXISTS'.

|||

SQL Server 2005:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate, row_number()OVER(partitionby idorderby ModifiedDateDESC)as RowNum

FROM TableA) t

WHERE t.RowNum=1

For SQL Server 2000, you can try:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate,(SELECTcount(*)FROM TABLEA aWHERE a.id=a1.idand a1.ModifiedDate<=a.ModifiedDate)as RowNum

FROM TableA a1) t

WHERE t.RowNum=1

|||

That's why your anAll-Star!....Shot for the help... it works very well.

Thanks B

sql

Query Help

Hi All,

I want to display the data in vertical format though the data is stored horizontally in the datatable.Suppose i have table with five columns-id.a,b,c,d.If i use a select statement that will give me the data in the format-ID A B C D

but now i want to display the data as

ID A

ID B

ID C

ID D

Any help on this pls??I m thinking to use the self join but would it take a long time??

Thanks

select id,a

union

select id,b

union

select id,c

union

select id,d

|||It sounds to me more like you require a pivot query rather than a union. If you have Sql Server 2005 you can do pivot queries but, unfortunately, not in the older versions. There a good article you can readhere that explains how to do pivot queries.|||

You can do pivot tables in Sql Server 2000. Just search for "pivot tables" in the Books Online. The example there was greatly helpful to me with the same issue.

Hope this helps

query help

i have a table that stores employee records. I then have a history employee table that stores changes to each particular employee (one to many ). Everytime a changes is made to an employee I record their old record in a history table. I want to be able to query against the history table and pull up the "last entry". How would I write a query for this, I am recording a timestamp.You haven't shared any DDL, so I'm guessing on keys and column names. But you should get the idea from this...
SELECT *
FROM EmployeeHistory eh
WHERE eh.theDate =
(SELECT MAX(eh2.theDate)
FROM EmployeeHistory eh2
WHERE eh2.EmployeeId = eh.EmployeeId)

|||

You might want to match on a identity field instead. It will be more reliable then a date field. The date field could always have multiple records with the same date.
Nick

|||Depends on the business rules. If there are multiple rows thatwere updated at exactly the same time, which one is most recent? Or should both be displayed?

Query Help

I have a query that returns a recordset that contains this kind of data:

Description Rate Amount

item1 2 .32

item2 3 1.34

item3 5 12.33

I want change the query to put all this data in ONE single field. What is the best why to do this?

Thanks!

What the result will look like in the ONE single field?

|||

item1 2 .32 item2 3 1.34 item3 5 12.33

Just all these dumped into one field like above. However id like to use the final query in and update so i cant simply concatenate each field then each row and save it in a variable.

I have the query below but i cant use it in an update for multiple records without using a fetch, or at least I cant figure out a better way.

DECLARE @.blob varchar(2000)

SELECT
@.blob = ISNULL(@.blob,'') + (def.[description] + ' ' +
CAST(sd.amount as varchar(10)) + ' ' +
CAST(sd.minutes as varchar(10)) + CHAR(13) + CHAR(10) )
FROM
section_detail sd,
def_section_detail_description def
WHERE
sd.def_section_detail_descr_id = def.def_section_detail_descr_id
and sd.section_id = 2274893453

Query help

I have a project almost (or so I thought!) completed but now I need to add a column to a table which is throwing everything off. Right now I have TableMain which queries TableA. I need to add a column (bit) to TableMain and then run a query that is basicallyif (bitColumn) query TableB, else query TableA.for each row.

TableA contains a listing, TableB is groups where each group contains multiple items from TableA. The columns I want to pull from each table have the same names (ie. regardless of bitColumn, I want TableA.Name or TableB.Name)

I'm not sure how I can go about doing this, I greatly appreciate any help.

Can you please give following details.

Primary key and Fkey in each table

Example input and output data

|||

Paper (id(pkey), name, code, description, clipID(fkey), etc) <= Think of these as a sheet of paper.

Clip(id(pkey), name, description) <= Think of these as a group of papers paperclipped together.

Cart(id(pkey),user,paperID(fkey),qty)

if someone tries to order a paper that has a clipID, they are forced to buy the entire paperclip. My shopping cart works for regular papers, and I have it notifying the user that they are really ordering the paperclip. What I'm stuck on is displaying the shopping cart, etc.

Shopping Cart: Paper1 + Paper2 + Clip1, where Clip1 has Paper3 and Paper4.

I don't know how to make the cart show Clip1 or whether I should just force add each individual item to the shopping cart (making editing qtys difficult), etc. I was thinking about adding an isClip column to the cart and then the stored procudure returns Paper.name or Clip.name depending on whether isClip is 0 or 1... so that it would be

Cart(id(pkey),user,paperID(fkey),qty,isClip)

I don't know how I would write that query, and I'm not sure its even the best design Sad [:(]

|||

I would design like this.

Category table

Products Table

Product details table

In category table : categories are paper and clip

In products table: Different papers(only papers not associated with clips) and clips

In product Details table: Papers that goes with clips

Papers that are associated to clips are only sold as a bunch

When user click on paper, show paper details and when user clicks on clip, show all the papers associated to clip by querying from product details table.

|||A better design would be to have ALL papers clipped, even if it is only clipping a single paper. Then the user can only buy clips.|||

With all of the code already written, I'd like to avoid a complete design restructuring.

Motley: this is what I started working on last night until I ran into a problem. Each paper belongs to a certain topic, and in the catalog are listed under topic headings as the sp returns with order by topic. The papers in a clip do not need to (and rarely will) belong to the same topic. I could have a Clips topic that I display first and then the individual topics, but it would involve checking each clip and seeing how many papers reference it; or perhaps adding a Count field to the Clips table? I would still then need to figure out a way to return the correct ordering...I'm open to hearing other suggestions...

|||

edit...yeah, that doesn't work, never mind...

SELECT

clip.id, clip.name, clip.description,

ISNULL(paper.topic,'CLIP')

FROM

clips clip

LEFT JOIN

papers paper

clip.id = paper.kitID

ORDER BY

topic

|||

Basic query:

SELECT c.id as ClipID,c.Name as ClipName,c.Description as ClipDescription,p.id as PaperID,p.name as PaperName, p.code as PaperCode, p.Description as PaperDescription, etc

FROM clip c

JOIN paper p ON (c.PaperID=p.id)

Return clips and how many papers are attached:

SELECT c.id AS ClipID,c.name,c.description,COUNT(*)

FROM clip c

JOIN paper p ON (c.PaperID=p.id)

GROUP BY c.id,c.name,c.description

Of course this assumes that each paper has a clip associated with it.

With your original structure:

SELECT s.*,name,description

FROM cart s

JOIN clip c ON (s.ID=c.id and s.isClip=1)

UNION

SELECT s.*,name,description

FROM cart s

JOIN paper p ON (s.ID=p.id and s.isClip=0)

SELECT s.*,CASE WHEN c.id IS NOT NULL THEN c.name ELSE p.name END as Name, CASE WHEN c.id IS NOT NULL THEN c.description ELSE p.description END as Description

FROM cart s

LEFT JOIN clip c ON (c.id=s.id and s.isClip=1)

LEFT JOIN paper p ON (p.id=s.id and s.isClip=0)

sql

Query Help

SQL DB Query Help
I have several suppliers of products.
When a customer does a Search
I want to return only the Lowest cost items by Part Number.
The query could return many different Part Numbers.
I also need to return Description, Part Number, Qty on Hand, Supplier etc.
These are all in the table.
Note that Description, Qty and Supplier are usually different.
Example items in DB
Part Desc Cost Qty Supplier
123 Widget 1.00 10 1
123 A Widget 2.00 5 2
123 Widget A 3.00 20 3
567 B Widget 9.00 3 1
567 Widget B 8.00 17 2
567 Widget 12.00 8 3

I would like to return
Part Desc Cost Qty Supplier
123 Widget 1.00 10 1
567 Widget B 8.00 17 2

Thanks in advance

Friday, March 30, 2012

query help

Query Help

Query Help

Query Help

Query help

Query help

query help

query help

query help

Query help

Query Help

Query Help

query help

Query Help

query help

Query help

Query Help

Query help

query help

Query help

Query help

Query Help

query help

Query Help

Query help

Query Help

Query help

Query Help

Query Help

Query Help

Query Help

query assistance

Blog Archive

About Me