Solved

join products and orderitems

Posted on 2011-03-25
13
326 Views
Last Modified: 2012-06-22
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=orderitems.orderid
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35217915
Try this query
SELECT P.TITLE, MAX(O.ITEMID) ITEMID
FROM PRODUCTS P
INNER JOIN ORDERITEMS O ONP.PRODUCTID = O.PRODUCTID
WHERE O.ORDERID  = 4
GROUP BY P.TITLE

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35217925
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

Open in new window

0
 
LVL 24

Expert Comment

by:jimyX
ID: 35217933
select products.title where
orderitems.orderid=4 and orderitems.itemid = (select max(O.itemid) from orderitems O)
join by products.productid=orderitems.orderid
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 24

Expert Comment

by:jimyX
ID: 35217976
Final version:
select products.title from products
join orderitems on products.productid=orderitems.orderid
where orderitems.orderid=4 and orderitems.itemid = (select max(O.itemid) from orderitems O)
0
 

Author Comment

by:rgb192
ID: 35218133

select products.title from products
join orderitems on products.productid=orderitems.orderid
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
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35218250
Try this one please:

select products.title from products
join orderitems on orderitems.orderid = 4 and products.productid=orderitems.orderid
where orderitems.itemid = (select max(O.itemid) from orderitems O)
0
 

Author Comment

by:rgb192
ID: 35218360
select products.title from products
join orderitems on orderitems.orderid = 4 and products.productid=orderitems.orderid
where orderitems.itemid = (select max(O.itemid) from orderitems O)

no results
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35218378
Post some sample data with expected result.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35218419
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=orderitems.orderid
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
0
 

Author Comment

by:rgb192
ID: 35218610
itemid  title
56        title1
57        title2
58        title3



result is


itemid  title
58        title3
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35218673
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)
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 35218871
Try this one:
SELECT TOP 1 P.TITLE
FROM PRODUCTS P
INNER JOIN ORDERITEMS O
ON P.ProductID = O.ProductiID
INNER JOIN 
( 
 SELECT MAX(O.ITEMID) ITEMID
 FROM ORDERITEMS O
 WHERE O.ORDERID = 4
) M
ON  M.ITEMID = O.ITEMID;

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 35233623
thanks
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question