none
SQL Query help please - ordering based off a different table

    Question

  • Hello,

    I'm not an experienced SQL coder so please excuse me if this is not possible or simple. I have 2 tables. The first with a bunch of product names, prices, codes, descriptions, etc. The second table has the product inventory counts. What I want to do in the sample below is sort the results based off of the product name first but only if there is inventory of it then the out of stock items both alphabetical.

    Thanks for any help its much appreciated...

    select * from Product where (select count(where brand and name match the * from the parent select)) from inventory>0) order by Brand, Name union select * from Product where (select count(where brand and name match the * from the parent select)) from inventory=0) order by Brand, Name

    TableName: Product

    Brand | Name | Price | Image

    Brand1 | Item1 | 9.99 | Image1

    Brand1 | Item2 | 9.99 | Image2

    Brand2 | Item3 | 9.99 | Image3

    Brand4 | Item2 | 9.99 | Image 4

    TableName: Inventory

    Brand | Name | Status (1 is in stock)

    Brand1 | Item1 | 1

    Brand1 | Item1 | 1

    Brand1 | Item1 | 1

    Brand1 | Item1 | 1

    Brand2 | Item2 | 1

    Brand2 | Item2 | 1


    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

    Wednesday, December 25, 2013 3:56 PM

Answers

All replies

  • Hope this helps.

    IF OBJECT_ID('tempdb..#Product') IS NOT NULL
       DROP TABLE #Product 
    
    
    IF OBJECT_ID('tempdb..#Inventory') IS NOT NULL
       DROP TABLE #Inventory 
    
    CREATE TABLE #Product
    	   (
    		Brand VARCHAR(10)
    	   ,NAME VARCHAR(10)
    	   ,Price FLOAT
    	   ,Image VARCHAR(10)
    	   )
    
    INSERT INTO #Product
    	SELECT
    		'Brand1'
    	   ,'Item1'
    	   ,9.99
    	   ,'Image1'
    	UNION
    	SELECT
    		'Brand1'
    	   ,'Item2'
    	   ,9.99
    	   ,'Image2'
    	UNION
    	SELECT
    		'Brand2'
    	   ,'Item3'
    	   ,9.99
    	   ,'Image3'
    	UNION
    	SELECT
    		'Brand4'
    	   ,'Item2'
    	   ,9.99
    	   ,'Image4'
    
    CREATE TABLE #Inventory
    	   (
    		Brand VARCHAR(10)
    	   ,NAME VARCHAR(10)
    	   ,STATUS INT
    	   )
    
    INSERT INTO #Inventory
    	SELECT
    		'Brand1'
    	   ,'Item1'
    	   ,1
    	UNION ALL
    	SELECT
    		'Brand1'
    	   ,'Item1'
    	   ,1
    	UNION ALL
    	SELECT
    		'Brand1'
    	   ,'Item1'
    	   ,1
    	UNION ALL
    	SELECT
    		'Brand1'
    	   ,'Item1'
    	   ,1
    	UNION ALL
    	SELECT
    		'Brand2'
    	   ,'Item2'
    	   ,1
    	UNION ALL
    	SELECT
    		'Brand2'
    	   ,'Item2'
    	   ,1 
    
    
    
    SELECT
    	p.*
       ,x.STATUS
      FROM
    	#Product p
    	OUTER APPLY (
    				 SELECT TOP 1
    					i.STATUS
    				  FROM
    					#Inventory i
    				  WHERE
    					p.Brand = i.Brand
    					AND p.NAME = i.NAME
    				) x
      ORDER BY
    	ISNULL(x.STATUS,0) DESC
       ,p.Brand
       ,p.NAME
    

    • Proposed as answer by coskun.arif Wednesday, December 25, 2013 4:25 PM
    Wednesday, December 25, 2013 4:24 PM
  • wow thanks for the quick reply. I'll give this a try.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

    Wednesday, December 25, 2013 5:30 PM
  • >> I'm not an experienced SQL coder so please excuse me if this is not possible or simple. <<

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    You have gotten bad advice. 
    We do not use the 1970's UNION-ed insertion kludge. The correct syntax is “INSERT INTO <table> VALUES <row list>;”

    Never use FLOAT for a price! It is illegal and has bad math. 

    Status has to be associated with a time frame by definition. I will ignore this error. There is not a generic “generic status” in RDBMS so what you have is not a status. It is a flag and we do not use flags in RDBMS; that was 1950's assembly language. An SQL programmer would use the on-hand quantity rather than destroy data like you have. 

    Your whole design is wrong. 
    Table names are collective or plural because model sets. They have to have a key.
    Column names are <attribute>_<attribute property>, a data type and constraints. 

    CREATE TABLE Products --- you do have more than one? 
    (brand_name VARCHAR(12) NOT NULL,
     product_name VARCHAR(12) NOT NULL,
     PRIMARY KEY (brand_name, product_name), 
     unit_price DECIMAL (8,2) NOT NULL
     CHECK (unit_price >= 0.00), 
     image_url VARCHAR(255) NOT NULL);

    CREATE TABLE Inventory ---collective name!
    (brand_name VARCHAR(12) NOT NULL,
     product_name VARCHAR(12) NOT NULL,
     PRIMARY KEY (brand_name, product_name),
     FOREIGN KEY (brand_name, product_name)
     REFERENCES Products, --- this is important! 
     onhand_qty INTEGER DEFAULT 0 NOT NULL --- the facts
     CHECK (onhand_qty >= 0));

    >> What I want to do in the sample below is sort the results based off of the product name first but only if there is inventory of it then the out of stock items both alphabetical. <<

    Sorting?? We hate that in SQL because we use a set-oriented model. You will have to create a cursor with the ORDER BY clause. The query is done first, then the result set is sorted in this implicit cursor and passed to a presentation layer. 

    SELECT P.brand_name, P.product_name, 
           CASE WHEN I.onhand_qty > 0 THEN 'In' ELSE 'Out' END
           AS onhand_flag
      FROM Products AS P, Inventory AS I
     WHERE P.brand_name = I.brand_name
       AND P.product_name = I.product_name 
     ORDER BY I.onhand_flag, P.brand_name, P.product_name;

    I used to say that it takes 1 year of full time SQL programming to learn enough SQL to be almost useful as a junior programmer. I think it might be more like 2-3 years now :(  Just keep working and do our reading. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, December 25, 2013 9:25 PM
  • You have gotten bad advice. 
    We do not use the 1970's UNION-ed insertion kludge. The correct syntax is ?INSERT INTO <table> VALUES <row list>;?

    You may observe that Altuko only used SELECT UNION for the sample data. And that is the correct syntax to use! Kilimanj99 did not specify which version of SQL Server is using, so Altuko used syntax which runs on SQL 2005, not to cause confusion in this case.

    And to Kilimanj99, please ignore this Joe Celko person. He has very little to offer to inexperienced SQL programmers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 25, 2013 10:35 PM