Solved

join products and orderitems

Posted on 2011-03-25
13
318 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
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
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
Comment Utility
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
Comment Utility
select products.title where
orderitems.orderid=4 and orderitems.itemid = (select max(O.itemid) from orderitems O)
join by products.productid=orderitems.orderid
0
 
LVL 24

Expert Comment

by:jimyX
Comment Utility
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
Comment Utility

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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:rgb192
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
Post some sample data with expected result.
0
 
LVL 24

Expert Comment

by:jimyX
Comment Utility
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
Comment Utility
itemid  title
56        title1
57        title2
58        title3



result is


itemid  title
58        title3
0
 
LVL 24

Expert Comment

by:jimyX
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now