Join To Select A 'weighted' Column

Mar 28, 2006

Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.

declare @inv table (item varchar(50), supplierid int)

declare @supplier table (supplierid int, weight int)

set nocount on

insert into @inv values ('item1', 1)

insert into @inv values ('item1', 2)

insert into @inv values ('item2', 2)

insert into @inv values ('item2', 3)

insert into @supplier values(1, 30)

insert into @supplier values(2, 20)

insert into @supplier values(3, 10)

-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3

select item, ps2.supplierid from @supplier ps2 join

(select item, min(ps.weight)'weight'

from @inv inv join @supplier ps on inv.supplierid=ps.supplierid

group by item) iw on ps2.weight=iw.weight

Is there a better alternative to this?

Thanks in advance,

Mike

View 3 Replies


ADVERTISEMENT

Join-Problem (select Two Columns Out Of One Column)

Oct 27, 2007

Hi I'd like to select two columns out of one column from another table. Example from source table:





Code Block

ID ArtNr EAN
1 4711 51323135
1 4712 84651213
1 4713 84351322
2 4711 86431313
2 4714 23546853
1 4714 54646545
2 4715 64684353
2 4716 65435132


I want to join this table to a base table with one select, and generate the following output. The ID identifies If the ArtNr has an EAN1 or EAN2:







Code Block

ArtNr EAN1 EAN2
4711 51323135 86431313
4712 84651213
4713 84351322
4714 54646545 23546853
4715 64684353
4716 65435132

View 20 Replies View Related

Select Command - Left Join Versus Inner Join

Aug 9, 2013

Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?

View 4 Replies View Related

Weighted Sum?

Aug 31, 2007

I need to run some statistics on a dataset. I've never used SUM or COUNT(*) before, but I'm pretty sure I need them for this. If this is my table:

GROUP nvarchar(10) QTY tinyint TYPE smallint VALUE decimal(3,2)
g1 q1 t1 v1
g1 q1 t2 v2
g2 q2 t1 v3
g2 q2 t2 v4
g3 q3 t1 v5
g3 q3 t2 v6
g4 q4 t3 v7

and I need this output
v1 * q1 / (q1 + q2 + q3 + q4) + v3 * q2 / (q1 + q2 + q3 + q4) + v5 * q3 / (q1 + q2 + q3 + q4)
v2 * q1 / (q1 + q2 + q3 + q4) + v4 * q2 / (q1 + q2 + q3 + q4) + v6 * q3 / (q1 + q2 + q3 + q4)
v7 * q4 / (q1 + q2 + q3 + q4)

how can I query this? I think that I have to group by TYPE, but other than that I don't know how to do this. Thanks for your help.

View 15 Replies View Related

Weighted Search??

Oct 5, 2006

I have to perform a weighted search.  I have 2 criteria and each will be weighted on a 100 sum(e.g 25/75, 50/50).  I am just wondering if there is an easy way to encompass a weighted value on SELECTS.  Sorry if this is a dumb question. Thanks,Kyle 

View 1 Replies View Related

Weighted Average

Nov 4, 2005

Does anyone know if it's possible to calculate weighted averages on report level, based on 2 items in the report?

View 6 Replies View Related

Weighted Random Numbers

Apr 11, 2007

Hi there... I've got an interesting one, that I can't seem to get my head around. Maybe some legend out there might be able to give me a hand...

I'm looking for a way to produce a weighted set of random numbers. I'm doing some work for a client at the moment, and they want to issue 3 random "reward cards" to their members at certain times. These are a bit like discount vouchers etc. The problem is some cards have need to have a higher frequency than the others. I guess a similar problem to baseball cards, you buy a pack of cards, you get mostly common cards, but every now and then, you get a rare card.

Here is the table setup:
CREATE TABLE [dbo].[Cards](
[CardID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Cards_CardID] DEFAULT (newid()),
[CardName] [nvarchar](50) NOT NULL,
[InsertRatio] [float] NULL,
CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED
(
[CardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 1', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 2', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 3', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 4', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 5', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 6', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 7', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 8', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 9', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 1', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 2', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 3', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Very Rare 1', 0.005) /* 1:200 ratio */



So what I need to do, is have a Stored Proc that I can execute and it returns back 3 random rows. Now in that single run, a card can't be duplicated.

Notice the Insert Ratio column? This has the ratio of the probability, eg a 1:50 insert ratio is equal to 0.02. For the common cards, a NULL value indicates it is a common.

Eventually, this table would have about 1000 rows in it, and about 200 of those would have various ratios (eg 1:50, 1:200, 1:1000, 1:8000 etc)

Any ideas or comments?

Thanks in advance.

Leddo
Brisbane, Australia

View 6 Replies View Related

Transact SQL :: Select From A Select Using Row Number With Left Join

Aug 20, 2015

The select command below will output one patient’s information in 1 row:

Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2

[code]...

This works great if there is at least one OP coverage.   There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.   The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.  

select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,

code]...

View 6 Replies View Related

Weighted Search On Fulltext Index

Aug 20, 2007

SQL Server 2005:
Anyone know how best to rewrite this SQL string to perform a weighted search on my table?
here's my code:
SQL="SELECT RecipeName FROM recipeList WHERE FREETEXT(*,'ISABOUT " & ing01 & " WEIGHT (.1) or " & ing02 & " WEIGHT (.2) or " & ing03 & " WEIGHT (.3) or " & ing04 & " WEIGHT (.4) or " & ing05 & " WEIGHT (.5)')"
the idea is to get a list of recipes most pertinant to the ingredients entered.
table 'recipeList' contains the ingredients in several columns. so column 2 might say '5oz of flour', column 2 - '2oz butter' etc.

at the moment it returns the correct recipes but not in the right order.
If I use 'CONTAINS' it returns nothing.
hope that's not too vague!

View 10 Replies View Related

Script For SQL Agent Weighted Job Duration

Jul 20, 2005

Hi,Does anyone know of a script that will give "weighted job duration"?I want to use it, to identify which jobs are hogging the CPU. That isfor a given server, list the sql agent jobs ordered by:(avg job duration in minutes) times (avg num of times job runs in agiven day).

View 2 Replies View Related

Prediction Query For A Weighted Clustering Model

Dec 12, 2006

I have a question about writing a prediction query against a clustering model that has the same column added more than once.

Per Jamie, I can accomplish some crude weighting by adding a column to my model multiple times. See this post for an explnation... Now that I have that worked out, I was wondering how my DM query would look? If I have Input_A1, Input_A2 , & Input_A3 all being source from the same column in my structure do I have to reference all three when writing my prediction query?

View 1 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

Power Pivot :: Formula For Row Wise Weighted Average

Apr 22, 2015

I have a table in `PowerPivot` which contains the logged data of a traffic control camera mounted on a road. This table is filled the velocity and the number of vehicles that pass this camera during a specific time(e.g. 14:10 - 15:25). Now I want to know that how can I get the average velocity of cars for an specific hour and list them in a separate table with 24 rows(hour 0 - 23) where the second column of each row is the weighted average velocity of that hour?

A sample of my stat_table data is given below:
 
count    vel          hour  
-----    --------   ----  
133    96.00237    15  
117    91.45705    21  
81      81.90521    6  
2        84.29946    21  
4        77.7841      18  
1        140.8766    17  

[Code] ....

In a separate `PowerPivot` table I have 24 rows and 2 columns(column1 is for hours and column 2 for weighted averages) and when I enter my formula, the whole rows get updated with the same number. My formula is:   

=sumX(FILTER(stat_table, stat_table[hour]=[hour]), stat_table[count] * stat_table[vel])/sumX(FILTER(stat_table, stat_table[hour]=[hour]), stat_table[count])

View 3 Replies View Related

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?

Jul 20, 2005

Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View 5 Replies View Related

Select Or Join

Oct 4, 2000

What is the difference from performance point of view, when you select from 3 different tables to show fieldnames across the 3 tables based on one common key, OR using Join between the 3 tables.

Thanks

View 1 Replies View Related

SELECT INNER JOIN SUM

Jun 14, 2008

I have three tables.


Quote
QuoteID, QuoteNumber,


Transportation

TransportationID, QuoteID, Item, Description, Cost

OptionalCharges

OptionalChargesID, QuoteID, Item, Description, Cost



What I want to do is SUM(Cost) for the Transportation and the Optional Charges table. If I do a normal INNER JOIN, with the SUM for Cost on the table Transportation and Optional Charges, it will SUM each twice. For example if Transportation has three rows with a cost of 10, 20, 30, it will SUM a total of 120 instead of 60.

So I came up with:

Select
Quote.QuoteID, QuoteDate, t.TransportationTotalCost

FROM
Quote.Quote
INNER JOIN
(

SELECT
QuoteID, SUM(COST) AS TransportationTotalCost
FROM
Quote.Transportation
GROUP BY
QuoteID
) t
on Quote.QuoteID = t.QuoteID
WHERE Quote.QuoteID = 135

Which gives me the total for the Transportation Table, but how do I go about adding in the Optional Charges Table?

Thanks,

marly

View 2 Replies View Related

SQL Select/Join Help

Jun 22, 2008

Hi,

My SQL is a little rusty, and I need a little help for a client that I'm helping.

I'm simply trying to do create the following output:

HEADINGS: Sale Order | Customer Number | Customer Name | # of Pallets | (etc)
DATA: 456188 | 12355890 | Acme Customer | 4 | other stuff I have figured out that is irrelevant


This data comes from 3 tables:
1) SOE_HEADER
SALE_ORDER_NO | CUSTOMER_NO | (etc)
456188 | 12355890

2) CUST_NAME
CUSTOMER_NO | CUSTOMER_NAME | (etc)
456188 | Acme Customer

3) PALLETS_SALES_ORD
SALE_ORDER_NO | PALLET_ID | (etc)
456188 | 12345
456188 | 67890
456188 | 13579

I have 2 queries that independently pull the right data. 1 query joins the customer info to pull in the customer name, and the 2nd query calculates the # of pallets per sales order.

BUT I CAN'T GET THEM TO WORK WHEN I DO A JOIN!!! Can someone please help me? Here are the queries as I currently have them. This returns zero rows, but when I run my queries independently, the both return multiple rows with the correct data. PLEASE HELP ME!

SELECT
SOE.INSIDE_ROUTE,
SOE.SALESMAN_NO,
SOE.SALE_ORDER_NO,
CUST.CUST_NAME,
SOE.CUSTOMER_NO,
(SOE.SALES_AMT/100) AS SALES,
(SOE.COST_GOODS_SOLD/100) AS COGS,
(SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN,
COUNT(PALLET.SALE_ORDER_NO) AS NumOccurrences
FROM
SOE_HEADER SOE,
CUST_NAME CUST,
PALLET_SALES_ORD PALLET
WHERE
SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO
AND SOE.CUSTOMER_NO = CUST.CUSTOMER_NO
GROUP BY
PALLET_SALES_ORD.SALE_ORDER_NO
HAVING (COUNT(PALLET_SALES_ORD.SALE_ORDER_NO) > 1 )

THANKS. This will be a big help for the work I need to get done tomorrow, and I can't take it anymore. I've tweaked this all weekend, to no avail.

View 17 Replies View Related

Select Min Value In An Inner Join

Nov 28, 2006

Hello,

I'd appreciate any help with the following problem.

I'm trying to update a table using an inner self join.

I've a list of historical records with start dates, and I need to add end dates to the records, using the start date of the next record.

The table I'm using looks like this
CREATE TABLE [dbo].[IbesEstimateHist](
[IbesEstimateHistId] [int] IDENTITY(1,1) NOT NULL,
[IbesEstimateId] [int] NULL,
[EstimateDate] [datetime] NULL,
[EstimateEndDate] [datetime] NULL CONSTRAINT [DF_IbesEstimateHist_EstimateEndDate] DEFAULT ('9999-12-31 00:00.000'),
[Value] [decimal](13, 4) NULL,
CONSTRAINT [PK_IbesEstimateHist] PRIMARY KEY CLUSTERED
(
[IbesEstimateHistId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

and here's some example data

insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value])
values(1,'2006-01-01','9999-12-31',100)
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value] )
values (1,'2006-02-01','9999-12-31',100)
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value])
values (1,'2006-03-01','9999-12-31',100)

These are three historical records for the same estimate, I want to set the end dates of the earlier records to the start date of the next record that was recieved.

This is the SQL that I've tried using but I can't seem to get it right

select esth1.IbesEstimateId, esth1.EstimateEndDate,min(next.estimatedate)
from IbesEstimateHist esth1
inner join
(
select esth2.EstimateDate as estimatedate, esth2.IbesEstimateId
from IbesEstimateHist esth2
) as next
on esth1.IbesEstimateId = next.IbesEstimateId
and esth1.EstimateDate < next.estimatedate
group by esth1.IbesEstimateId, esth1.EstimateEndDate

I'd be grateful for any help, thanks.




















Sean_B

View 3 Replies View Related

Select With Join

Jan 29, 2007

hi all,

i have a doubt regarding a select operation performed on two tables with join statement.

The table structure is as follows


table name- application
---------------------- ------------------------------------------
appid appname version
------------------------------------------------------------------
1 Test 10
2 Test 11
3 Sample 5

table name- app_users
-----------------------

app_users_id user_id appid version date_downloaded
------------------------------------------------------------
1 250 1 10 1/29/2007




Now i want a grid which should show data like this

AppName LastDownloadedVersion Date_Downloaded
----------------------------------------------------------
TestV11 10 1/30/2007
SampleV5 -- ------


Here the value for appname should be created by appname+'V'+latest version of that appname(eg. Application name- Test Ltest version- 11 thus appname becomes 'TestV11')

Could anybody help me to solve this problem..
Thanx in advance..

View 1 Replies View Related

How To Select With Inner Join

Aug 5, 2007

Hi:

I have a record that has location1, price1, location2, price2

How would I do an inner join or "how would I " get the name of the location?

location table -- locationid, locationname
producttable -- location1, location2 is the locationid in location table

View 1 Replies View Related

Problem With The Sql Select On Join

Dec 26, 2006

My database with 300.000 records , uses 350 MB RAM when I send this query.
 is there any way I can avoid this? 
 
SELECT * FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS,(_NAME,ESTATEOTHERPROPERTIES),'FORSALE') as KEY_TBL
ON KEY_TBL.[KEY]= ADTBL._ID
Where _DELETIONSTATUS=0

View 1 Replies View Related

SELECT, JOIN And UPDATE

May 30, 2007

I need to Update a table with information from another table.  Below is my psuedo code - need help with the syntax needed for Sql2000 server.
JOIN tblStateLoc ON tblCompanies.LocationID = tblStateLoc.LocationIDUPDATE tblCompaniesSET tblCompanies.StoreType = tblStateLoc.StoreTypeWHERE tblCompanies.LocationID = tblStateLoc.LocationID

View 2 Replies View Related

SELECT ... JOIN On Two Databases In VWD (?)

May 26, 2006

My environment:XP Home, VWD, SQLEXPRESS.A purely local setting, no network, no remote servers.
I try to do a JOIN query between tables in the membership ASPNETDB.mdf and one table in a self created 3L_Daten.mdf.
After dragging the tables into the Query Design window and connecting them VWD creates this query (here I added the control declaration):
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAspNetDB %>"            SelectCommand="SELECT aspnet_Users.UserName,                                 aspnet_Membership.Password,                                 aspnet_Membership.Email,                                 aspnet_Membership.PasswordQuestion,                                  aspnet_Membership.PasswordAnswer,                                 aspnet_Membership.CreateDate,                                 aspnet_Membership.LastLoginDate,                                 aspnet_Roles.RoleName,                                 [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Age,                                [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Sex,                                [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Area                            FROM [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten                            INNER JOIN                                aspnet_Users                            ON                                 [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.User_ID = aspnet_Users.UserId                            LEFT OUTER JOIN                                aspnet_Roles                            INNER JOIN                                aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId                            ON                                 aspnet_Users.UserId = aspnet_UsersInRoles.UserId                            LEFT OUTER JOIN                                aspnet_Membership                            ON aspnet_Users.UserId = aspnet_Membership.UserId"></asp:SqlDataSource>
THIS WORKS, BUT:
As you can see the database 3L_Daten.mdf is inserted with its full path, which is not feasible for deployment reasons.
My question: How can I address both databases purely by their database names ? Both have been created within VWD and lie under App_Data.
(I tried almost everything, I practiced with the SQL Server 2005 Management Studio Express Edition, I tried linked servers, all without success).
Thank you for your consideration.
 
 

View 3 Replies View Related

Select MAX In JOIN Query

Nov 27, 2001

Here is the working query, shortened for the example:

SELECT a.SalesMan,a.CustomerName,b.Entry_Comments,b.Entry _Date
FROM MyMaster a LEFT OUTER JOIN MyDetail b ON a.id = b.id WHERE blah ORDER
BY blah

This works fine and I get all my detail reocrds for each master. Now I need
to be able to select only a single most recent b.Entry_Date. How can I do this, Ive played with MAX but cannot get the sytax correct?

Thanks,Adrian

View 4 Replies View Related

Better To Join Tables In DB Then In SELECT?

Feb 20, 2006

In my new job I have to administer an existing SQL-database with approx. 50 tables. In this database are no joins :confused: defined between the tables. We use a Visual Basic 6 application to create a GUI and within this VB6 app. there are several SELECT statements to retrieve the required data. In these SELECT statements are all the INNER and OUTER JOINS between the tables defined.
My question: is this a correct way to work with or is it better to create all the JOINs between the tables on the database itself? Or should I create different views and define the JOINs in there? My main concern is the speed to retrieve data and second the required time to administer this database.

View 3 Replies View Related

Select Statement With Join?

Apr 26, 2006

Hi all. I'm selecting all customers and trying to count alll the orders where at least one item has the itemstatus of "SHIPPED" on their order. Each customer will have only one order. I'm trying to see if I can do this in one query. Is it possible?? Is it something like below?

SELECT customers.id,COUNT( orders.id) AS 'total',
from customers
LEFT JOIN orders ON customers.id=orders.id AND orders.itemstatus="SHIPPED"

View 2 Replies View Related

Join 2 Select Statements

Dec 22, 2014

how can i join these 2 queries to produce 1 result

Query 1:
select R.Name, T.Branchid, t.TradingDate,t. TransactionDate,
convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber,
t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt
from TransactionHeader T
join SalesRep R on
R.SalesRepid = T.SalesRepid
where T.SalesRepid is not null

Query 2 :
select C.TradingName,C.AccountNo
From Sale S
Join ClMast c on
C.Clientid = s.CustomerAccountID

The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno..Field Saleid is present in Transactionheader Table and Sale table

View 5 Replies View Related

Select Query - Join

Feb 28, 2008

i have select query....

select distinct duo.messageid_ from [detected unique opens] duo

left outer join (select MailingID, count(*) as cnt
from lyrCompletedRecips
where mailingid = duo.messageid_
and FinalAttempt is not null
AND FinalAttempt >= '1945-09-10 00:00:00'
group by MailingID) ad
on ad.mailingid = duo.messageid_

i m getting error like:

The column prefix 'duo' does not match with a table name or alias name used in the query.

can anyone tell me what's the reason?
thanks.

View 3 Replies View Related

Select JOIN Problem

Jul 23, 2005

The query:SELECT BTbl.PKey, BTbl.ResultFROM BTbl INNER JOINATbl ON BTbl.PKey = ATbl.PKeyWHERE (ATbl.Status = 'DROPPED') AND (BTbl.Result <> 'RESOLVED')Returns no rows.If I do:SELECT BTbl.PKey, BTbl.ResultFROM BTbl INNER JOINATbl ON BTbl.PKey = ATbl.PKeyWHERE (ATbl.Status = 'DROPPED')Returns:PKeyResult125127RESOLVEDI want the first query to return the row with PKey: 125 because it'sresult field does not equal 'RESOLVED'Any ideas what I'm doing wrong?My tables:CREATE TABLE [dbo].[ATbl] ([PKey] [int] NOT NULL ,[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[BTbl] ([PKey] [int] NOT NULL ,[Result] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO

View 2 Replies View Related

SELECT DISTINCT With JOIN

Jul 20, 2005

Hi everyoneHave a problem I would areally appreciate help with.I have 3 tables in a standard format for a Bookshop, egProductsCategoriesCategories_Productsthe latter allowing me to have products in multiple categories.Everthing works well except for one annoying little thing.When an individual product (which is in more than one topcategory) is addedto the Shopping Cart it displays twice, because in my select statement Ihave the Category listed. I realise I could remove the TopCategory from thestatement and that makes my DISTINCT work as I wanted, but Id prefer to havethe TopCategory as it saves me later having to another SQL query (Im alreadydoing one to allow me not to list category in the Statement .... but If Ican overcome this one ... then I can remove this as well).Here is my table structure (the necessary bits)productsidProduct int....categoriesidcategory intidParentCategory inttopcategory int...categories_productsidCatProd intidProduct intidCategoryWhen I run a query such asSELECT DISTINCT a.idProduct, a.description,a.descriptionLong,a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,c.topcategoryFROM products a, categories_products b, categories cWHERE active = -1 AND homePage = -1AND a.idProduct = b.idProductAND c.idcategory=b.idcategoryAND prodType = 1 ORDER BY a.idProduct DESCThis will return all products as expected, as well as any products which arein more than one TopCategory.Any ideas how to overcome this would be greatly appreciated.CheersCraig

View 14 Replies View Related

SQL Looping, Join, And Max Select

Nov 12, 2007

I'm having trouble finding the correct way to proceed. My object is to have a selection set of the most recent log entry for each user. I want to display this info in an ASP.Net grid. I have a user table related to a datetime stamped log table. I have the stored proceedure that finds the latest log for a specific user, but I'm having a problem constructing a statement that will produce records for all users.

This is (basically) what I use to get a single User's data.



Code Block
SELECT First, Last FROM UserDetails INNER JOIN UserStatusLog ON UserDetails.UserID = UserStatusLog.UserID WHERE DateTimeStamp IN
(SELECT max(DateTimeStamp) FROM serStatusLog WHERE UserID = @UserID)

I'm using SQL Server 2005 Express and somewhat of a newbie to SQL.

View 10 Replies View Related

SELECT Column Aliases: Refer To Alias In Another Column?

Apr 9, 2008

Using SQL Server 2000.  How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.

View 11 Replies View Related

SELECT Column Aliases: Refer To Alias In Another Column?

Apr 10, 2008

Using SQL Server 2000. How can I refer to one alias in another column?

E.g., (this a contrived example but you get the idea)

SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data

Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved