Select multiple values from same column based on 2 columns
Best explained using an example. My trials and results are bellow the example.
There are two tables (in reality I have multiple tables)
ID name ----------- 1 apple 2 orange 3 pear
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
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
| NAME | PRICE | COLOR | TASTE | ----------------------------------- | apple | 20 | red | sweet | | orange | 30 | orange | sour | | pear | 40 | green | sweet |
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, ,, 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 (,,) ) 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
- What RDBMS you are using?
RDBMSstands 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.
joinsare much faster than
INin mysql. you can see it though
EXPLAIN youSelectStatement. do you want to know the dynamic version of this?