rgb192
asked on
join products and orderitems
CREATE TABLE [dbo].[products](
[productid] [int] IDENTITY(1,1) NOT NULL,
[folderid] [smallint] NULL,
[archive] [int] NULL,
[title] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[titleinternal] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[price] [decimal](10, 2) NULL,
[pricesalesman] [decimal](10, 2) NULL,
[estimatedcost] [decimal](10, 2) NULL,
[site] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[orderable] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[leadtime] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[country] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[condition] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[manufacturer] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[model] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[mpn] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[active] [tinyint] NULL,
[discontinued] [int] NULL,
[important] [tinyint] NULL,
[commision] [smallint] NULL,
[commissionamount] [smallint] NULL,
[spiff] [smallint] NULL,
[spiffqualifier] [decimal](10, 2) NULL,
[spiffamount] [decimal](10, 2) NULL,
[sundesc] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[dsndesc] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[pyxisdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[hdewdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[searchpriority] [int] NULL,
[autotxtname] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[currentfeed] [int] NULL,
[pgfeed] [int] NULL,
[internalsku] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[asin] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[inwarehouse] [smallint] NULL,
[sentquantity] [smallint] NULL,
[date] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[estimateddate] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[date1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[inwarehouse2] [smallint] NULL,
[inwarehouse3] [smallint] NULL,
[cost1] [decimal](10, 2) NULL,
[cost2] [decimal](10, 2) NULL,
[cost3] [decimal](10, 2) NULL,
[internalsku2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[internalsku3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[ourinwarehouse] [smallint] NULL,
[ourinwarehouse2] [smallint] NULL,
[ourinwarehouse3] [smallint] NULL,
[sentquantity2] [smallint] NULL,
[sentquantity3] [smallint] NULL,
[date2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[date3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[estimateddate2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[estimateddate3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[g1] [smallint] NULL,
[g2] [smallint] NULL,
[g3] [smallint] NULL,
[e1] [smallint] NULL,
[e2] [smallint] NULL,
[e3] [smallint] NULL,
[skucount] [smallint] NULL,
[skucount2] [smallint] NULL,
[skucount3] [smallint] NULL,
[startprice] [decimal](8, 2) NULL,
[buyitnowprice] [decimal](8, 2) NULL,
[s1] [smallint] NULL,
[s2] [smallint] NULL,
[s3] [smallint] NULL,
[s0] [smallint] NULL,
[oldstartprice] [decimal](7, 2) NULL,
[auk] [smallint] NULL,
[auk2] [smallint] NULL,
[auk3] [smallint] NULL,
[ny] [smallint] NULL,
[ny2] [smallint] NULL,
[ny3] [smallint] NULL,
[ebaytitle] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[internalsku1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[inwarehouse1] [smallint] NULL,
[ourinwarehouse1] [smallint] NULL,
[sentquantity1] [smallint] NULL,
[skucount1] [smallint] NULL,
[auk1] [smallint] NULL,
[ny1] [smallint] NULL,
[cost] [decimal](10, 2) NULL,
[internalsku4] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[inwarehouse4] [smallint] NULL,
[ourinwarehouse4] [smallint] NULL,
[sentquantity4] [smallint] NULL,
[auk4] [smallint] NULL,
[ny4] [smallint] NULL,
[cost4] [decimal](10, 2) NULL,
[s4] [smallint] NULL,
[skucount4] [smallint] NULL,
[e4] [smallint] NULL,
[g4] [smallint] NULL,
[date4] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[orderitems](
[itemid] [int] IDENTITY(1,1) NOT NULL,
[dateadded] [datetime] NOT NULL CONSTRAINT [DF_orderitems_dateadded] DEFAULT (getdate()),
[orderid] [int] NOT NULL,
[productid] [int] NOT NULL,
[quantity] [int] NOT NULL,
[priceper] [money] NOT NULL,
[subtotal] [money] NOT NULL,
[soldby] [int] NOT NULL,
[scanned] [int] NOT NULL CONSTRAINT [DF_orderitems_scanned] DEFAULT ((0)),
[serial] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL CONSTRAINT [DF_orderitems_serial] DEFAULT ((0)),
[paidquantity] [int] NULL,
[is1q] [smallint] NULL,
[is2q] [smallint] NULL,
[is3q] [smallint] NULL,
[neededquantity] [smallint] NULL,
[is1n] [smallint] NULL,
[is2n] [smallint] NULL,
[is3n] [smallint] NULL,
[sku] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[skucount] [smallint] NULL,
[used] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[ebayquantity] [smallint] NULL,
[addon] [smallint] NULL,
CONSTRAINT [PK__orderitems__09DE7BCC] PRIMARY KEY CLUSTERED
(
[itemid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
select products.title where
orderitems.orderid=4 and orderitems.itemid is the highest
join by products.productid=orderit ems.orderi d
[productid] [int] IDENTITY(1,1) NOT NULL,
[folderid] [smallint] NULL,
[archive] [int] NULL,
[title] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_
[titleinternal] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_
[price] [decimal](10, 2) NULL,
[pricesalesman] [decimal](10, 2) NULL,
[estimatedcost] [decimal](10, 2) NULL,
[site] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_
[orderable] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_
[leadtime] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_
[country] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_
[condition] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
[manufacturer] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[model] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[mpn] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[active] [tinyint] NULL,
[discontinued] [int] NULL,
[important] [tinyint] NULL,
[commision] [smallint] NULL,
[commissionamount] [smallint] NULL,
[spiff] [smallint] NULL,
[spiffqualifier] [decimal](10, 2) NULL,
[spiffamount] [decimal](10, 2) NULL,
[sundesc] [text] COLLATE SQL_Latin1_General_CP1_CI_
[dsndesc] [text] COLLATE SQL_Latin1_General_CP1_CI_
[pyxisdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_
[hdewdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_
[searchpriority] [int] NULL,
[autotxtname] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[currentfeed] [int] NULL,
[pgfeed] [int] NULL,
[internalsku] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[asin] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[inwarehouse] [smallint] NULL,
[sentquantity] [smallint] NULL,
[date] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[estimateddate] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[date1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[inwarehouse2] [smallint] NULL,
[inwarehouse3] [smallint] NULL,
[cost1] [decimal](10, 2) NULL,
[cost2] [decimal](10, 2) NULL,
[cost3] [decimal](10, 2) NULL,
[internalsku2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[internalsku3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[ourinwarehouse] [smallint] NULL,
[ourinwarehouse2] [smallint] NULL,
[ourinwarehouse3] [smallint] NULL,
[sentquantity2] [smallint] NULL,
[sentquantity3] [smallint] NULL,
[date2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[date3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[estimateddate2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[estimateddate3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[g1] [smallint] NULL,
[g2] [smallint] NULL,
[g3] [smallint] NULL,
[e1] [smallint] NULL,
[e2] [smallint] NULL,
[e3] [smallint] NULL,
[skucount] [smallint] NULL,
[skucount2] [smallint] NULL,
[skucount3] [smallint] NULL,
[startprice] [decimal](8, 2) NULL,
[buyitnowprice] [decimal](8, 2) NULL,
[s1] [smallint] NULL,
[s2] [smallint] NULL,
[s3] [smallint] NULL,
[s0] [smallint] NULL,
[oldstartprice] [decimal](7, 2) NULL,
[auk] [smallint] NULL,
[auk2] [smallint] NULL,
[auk3] [smallint] NULL,
[ny] [smallint] NULL,
[ny2] [smallint] NULL,
[ny3] [smallint] NULL,
[ebaytitle] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_
[internalsku1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[inwarehouse1] [smallint] NULL,
[ourinwarehouse1] [smallint] NULL,
[sentquantity1] [smallint] NULL,
[skucount1] [smallint] NULL,
[auk1] [smallint] NULL,
[ny1] [smallint] NULL,
[cost] [decimal](10, 2) NULL,
[internalsku4] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[inwarehouse4] [smallint] NULL,
[ourinwarehouse4] [smallint] NULL,
[sentquantity4] [smallint] NULL,
[auk4] [smallint] NULL,
[ny4] [smallint] NULL,
[cost4] [decimal](10, 2) NULL,
[s4] [smallint] NULL,
[skucount4] [smallint] NULL,
[e4] [smallint] NULL,
[g4] [smallint] NULL,
[date4] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[orderitems](
[itemid] [int] IDENTITY(1,1) NOT NULL,
[dateadded] [datetime] NOT NULL CONSTRAINT [DF_orderitems_dateadded] DEFAULT (getdate()),
[orderid] [int] NOT NULL,
[productid] [int] NOT NULL,
[quantity] [int] NOT NULL,
[priceper] [money] NOT NULL,
[subtotal] [money] NOT NULL,
[soldby] [int] NOT NULL,
[scanned] [int] NOT NULL CONSTRAINT [DF_orderitems_scanned] DEFAULT ((0)),
[serial] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[paidquantity] [int] NULL,
[is1q] [smallint] NULL,
[is2q] [smallint] NULL,
[is3q] [smallint] NULL,
[neededquantity] [smallint] NULL,
[is1n] [smallint] NULL,
[is2n] [smallint] NULL,
[is3n] [smallint] NULL,
[sku] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_
[skucount] [smallint] NULL,
[used] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[ebayquantity] [smallint] NULL,
[addon] [smallint] NULL,
CONSTRAINT [PK__orderitems__09DE7BCC]
(
[itemid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
select products.title where
orderitems.orderid=4 and orderitems.itemid is the highest
join by products.productid=orderit
Joining columns are productid with productid, right ? you mentioned productid with orderid ?
SELECT P.TITLE, MAX(O.ITEMID) ITEMID
FROM PRODUCTS P
INNER JOIN ORDERITEMS O ON P.PRODUCTID = O.PRODUCTID
WHERE O.ORDERID = 4
GROUP BY P.TITLE
select products.title where
orderitems.orderid=4 and orderitems.itemid = (select max(O.itemid) from orderitems O)
join by products.productid=orderit ems.orderi d
orderitems.orderid=4 and orderitems.itemid = (select max(O.itemid) from orderitems O)
join by products.productid=orderit
Final version:
select products.title from products
join orderitems on products.productid=orderit ems.orderi d
where orderitems.orderid=4 and orderitems.itemid = (select max(O.itemid) from orderitems O)
select products.title from products
join orderitems on products.productid=orderit
where orderitems.orderid=4 and orderitems.itemid = (select max(O.itemid) from orderitems O)
ASKER
select products.title from products
join orderitems on products.productid=orderit
where orderitems.orderid=4 and orderitems.itemid = (select max(O.itemid) from orderitems O)
No results
SELECT P.TITLE, MAX(O.ITEMID) ITEMID
FROM PRODUCTS P
INNER JOIN ORDERITEMS O ON P.PRODUCTID = O.PRODUCTID
WHERE O.ORDERID = 4
GROUP BY P.TITLE
shows all rows, I only want the title that has productid of max orderitems.itemid
Try this one please:
select products.title from products
join orderitems on orderitems.orderid = 4 and products.productid=orderit ems.orderi d
where orderitems.itemid = (select max(O.itemid) from orderitems O)
select products.title from products
join orderitems on orderitems.orderid = 4 and products.productid=orderit
where orderitems.itemid = (select max(O.itemid) from orderitems O)
ASKER
select products.title from products
join orderitems on orderitems.orderid = 4 and products.productid=orderit ems.orderi d
where orderitems.itemid = (select max(O.itemid) from orderitems O)
no results
join orderitems on orderitems.orderid = 4 and products.productid=orderit
where orderitems.itemid = (select max(O.itemid) from orderitems O)
no results
Post some sample data with expected result.
You haven't provide much details except the initial query so we will be guessing here.
Except this line:
> "I only want the title that has productid of max orderitems.itemid"
OK, how about this one:
select products.title from products
join orderitems on products.productid=orderit ems.orderi d
where orderitems.itemid = (select max(O.itemid) from orderitems O)
It will be much better and easier if you provided sample data and the expected results, such as:
Sample data:
Table1
Col1, Col2, Col3
1 A A1
2 B B2
3 C C3
Required result:
Col1, Col2, Col3
3 C C3
Except this line:
> "I only want the title that has productid of max orderitems.itemid"
OK, how about this one:
select products.title from products
join orderitems on products.productid=orderit
where orderitems.itemid = (select max(O.itemid) from orderitems O)
It will be much better and easier if you provided sample data and the expected results, such as:
Sample data:
Table1
Col1, Col2, Col3
1 A A1
2 B B2
3 C C3
Required result:
Col1, Col2, Col3
3 C C3
ASKER
itemid title
56 title1
57 title2
58 title3
result is
itemid title
58 title3
56 title1
57 title2
58 title3
result is
itemid title
58 title3
Are you joining any data from any other table?
If not then:
select products.title from products
where products.itemid = (select max(O.itemid) from products O)
If not then:
select products.title from products
where products.itemid = (select max(O.itemid) from products O)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Open in new window