Select multiple values from same column based on 2 columns

Related searches

Best explained using an example. My trials and results are bellow the example.

There are two tables (in reality I have multiple tables)

TABLE: Products
ID    name
-----------
1    apple
2    orange
3    pear
TABLE: ATTRIBUTES
ID  prod_ID   attr_id   value
----------------------------
1   1       101     20
2   1       102     red
3   1       103     sweet
4   2       101     30
5   2       102     orange
6   2       103     sour
6   3       101     40 
7   3       102     green
8   3       103     sweet
DESIRED OUTPUT
name   attr_id 101 AS 'price'   attr_id 102 AS 'taste'
------------------------------------------------------
apple        20           sweet
orange       30           sour
pear         40           sweet

I have managed SQL till now but recently I have had to call 3 tables and combine column values like shown above. I just can't get my head wrapped around this. Help would be greatly appreciated.

You have to query for each column separately...

SELECT 
  p.name,
  (SELECT value FROM Attributes a WHERE attr_id=101 AND a.prod_ID=p.ID) AS price,
  (SELECT value FROM Attributes a WHERE attr_id=102 AND a.prod_ID=p.ID) AS taste
FROM Products p

... or ... If you are using MSSQL 2008 R2 you can use PIVOT: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Select Multiple Values from Same Column; one sql statement and , The way you are describing the expected result; I am concerned that what you are looking for won't get you what you want. Taking the example� How to count three consecutive records with same values in a column based on the value of another column in PostgreSQL? 1 SELECT multiple values from one table having matching record in another table in one row

[Solved] Select same column twice from a table with two different , If a value is missing for either A or B, then the result is NULL. in a single column to individual fields by using joins, the fields being related by� COUNT () function and SELECT with DISTINCT on multiple columns You can use the count () function in a select statement with distinct on multiple columns to count the distinct rows. Here is an example: SELECT COUNT(*) FROM (SELECT DISTINCT agent_code, ord_amount, cust_code FROM orders WHERE agent_code ='A002');

You can use CASE statement for that: (This will work in MySQL)

SELECT p.name
   ,GROUP_CONCAT(CASE WHEN attr_id = 101 THEN value else NULL END) AS price
   ,GROUP_CONCAT(CASE WHEN attr_id = 102 THEN value else NULL END) AS color
   ,GROUP_CONCAT(CASE WHEN attr_id = 103 THEN value else NULL END) AS taste
FROM Products p JOIN Attributes a
ON p.id = a.prod_id
GROUP BY p.name;

Or you can also do the same with join: (This will work in both MySQL and SQL Server)

SELECT Name
  ,CASE WHEN a.attr_id = 101 THEN a.value ELSE NULL END AS price
  ,CASE WHEN b.attr_id = 102 THEN b.value else NULL END AS color
  ,CASE WHEN c.attr_id = 103 THEN c.value else NULL END AS taste
FROM Products p
  LEFT JOIN Attributes a ON p.id = a.prod_id
  LEFT JOIN Attributes b ON p.id = b.prod_id AND a.attr_id = 101 AND b.attr_id = 102
  LEFT JOIN Attributes c ON p.id = c.prod_id AND a.attr_id = 101 AND c.attr_id = 103
WHERE a.attr_id IS NOT NULL and b.attr_id IS NOT NULL AND c.attr_id IS NOT NULL

I have also added a column for color.

Output

|   NAME | PRICE |  COLOR | TASTE |
-----------------------------------
|  apple |    20 |    red | sweet |
| orange |    30 | orange |  sour |
|   pear |    40 |  green | sweet |
See this SQLFiddle
Demo for SQL Server

SQL Server SELECT DISTINCT, The query returns only distinct values in the specified column. In other words, it the other. In other words, the DISTINCT clause treats all NULL “values” as the same value. SQL Server SELECT DISTINCT - multiple columns example before. 1. Specify the key column that you want to combine other column based on; 2. Choose one calculation for your combined data.

Using PIVOT, easier to adjust for extra attributes.

SELECT NAME, [101],[102],[103]
             FROM (
                    SELECT P.NAME as NAME, A.ATTR_ID as ATTR_ID, A.VALUE as VALUE
                    FROM PRODUCTS as P, ATTRIBUTES as A
                    WHERE A.PROD_ID = P.ID  
             ) SrcTable
            PIVOT 
            (
                MAX(VALUE)
                for ATTR_ID in ([101],[102],[103])
            ) PivotTable;

Or a dynamic statement could be generated to adjust for a variable number of attributes (since this is the only thing that changes in the above statement)

The column string could be obtained with somethings like:

DECLARE @columns AS NVARCHAR(MAX);
set @columns = STUFF((SELECT distinct ',' + QUOTENAME(ATTR_ID) 
             FROM ATTRIBUTES
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'');

Next, this could variable could be used to generate a string like the static PIVOT

DECALARE @stmt AS NVARCHAR(MAX);
set @stmt ='SELECT NAME, ' + @columns + '
             FROM (
                    SELECT P.NAME as NAME, A.ATTR_ID as ATTR_ID, A.VALUE as VALUE 
                    FROM PRODUCTS as P, ATTRIBUTES as A 
                    WHERE A.PROD_ID = P.ID 
             ) SourceTable
             PIVOT ( 
                MAX(VALUE)
                for ATTR_ID in (' + @columns + ') 
            ) PivotTable';

EXECUTE(@stmt);

Can we apply DISTINCT to a SELECT query with multiple columns , It is true that there are duplicated values in the course_id and exercise_id , but every row is unique (there are no two rows with the same value in course_id and � Select DataFrame Rows Based on multiple conditions on columns. Select rows in above DataFrame for which ‘Sale’ column contains Values greater than 30 & less than 33 i.e. filterinfDataframe = dfObj[(dfObj['Sale'] > 30) & (dfObj['Sale'] < 33) ] It will return following DataFrame object in which Sales column contains value between 31 to 32,

5 easy ways to VLOOKUP and return multiple values, Rows will be hidden and if a condition is on the same row it will be hidden as well . I created headers on row 2, see image above. Enter the� Let’s discuss all different ways of selecting multiple columns in a pandas DataFrame. Method #1: Basic Method Given a dictionary which contains Employee entity as keys and list of those entity as values.

Example 2. Count dates with multiple conditions. In the same manner, you can use a COUNTIFS formula to count the number of dates in different columns that meet 2 or more conditions. For instance, the below formula will find out how many products were purchased after the 20 th of May and delivered after the 1 st of June:

1) Remove duplicate rows based on two columns (Using ‘Remove Duplicates’ feature) Check out the following data. Two columns are holding some names and their locations. What I want is: only unique rows will be on the list; all the duplicate rows will be deleted/removed from the list. Follow these steps. 1) Select a cell in the range. Open

Comments
  • What RDBMS you are using? RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc...
  • Thanks JW. It's MySQL. I have got the code working using the bellow method. I will try using MAX(CASE too through your SQLFiddle.
  • joins are much faster than IN in mysql. you can see it though EXPLAIN youSelectStatement. do you want to know the dynamic version of this?