How to Query

SaSaSQL · August 9, 2023, 2:08pm

I have 2 tables that are connected via a recordnumber field. The problem is these tables are anything but normalized. Each table contains 5 rows. The first column stores the recordnumber field while columns 2 to 4 contain fields that I need to specify to get the value in the 5th column.

So, each value (5th column) that I need to search for, requires the recordnumber and the next 4 columns to be specified. The first table contains numerical values in column 5 and the second table column 5 contains alphanumeric values.

How would I go about writing a query to get multiple values from each table for each record number? I have tried multiple self joins but nothing works.

Ifor · August 9, 2023, 4:34pm

SaSaSQL · August 9, 2023, 8:53pm

Thanks Ifor,

Please see the SQL query that I wrote followed by the two dummy table create statements that represent the tables I am querying.

SELECT NUMERIC.RecordNumber
,NUMERIC.Value
,Numeric2.Value
,Numeric3.Value
,AlphaNumeric.Value
,AlphaNumeric1.Value
,AlphaNumericValue3.value
FROM NUMERIC
INNER JOIN NUMERIC AS Numeric1 ON NUMERIC.RecordNumber = Numeric1.RecordNumber
INNER JOIN NUMERIC AS Numeric2 ON NUMERIC.RecordNumber = Numeric2.RecordNumber
INNER JOIN NUMERIC AS Numeric3 ON NUMERIC.RecordNumber = Numeric3.RecordNumber
INNER JOIN AlphaNumeric ON NUMERIC.RecordNumber = AlphaNumeric.RecordNumber
INNER JOIN AlphaNumeric AS AlphaNumeric1 ON AlphaNumeric.RecordNumber = AlphaNumeric1.RecordNumber
INNER JOIN AlphaNumeric AS AlphaNumeric2 ON AlphaNumeric.RecordNumber = AlphaNumeric2.RecordNumber
INNER JOIN AlphaNumeric AS AlphaNumeric3 ON AlphaNumeric.RecordNumber = AlphaNumeric3.RecordNumber
WHERE NUMERIC.Col1 = "1"
AND NUMERIC.Col2 = "1"
AND NUMERIC.Col3 = "1"
AND NUMERIC.Col4 = "1"
AND Numeric1.Col1 = "1"
AND Numeric1.Col2 = "1"
AND Numeric1.Col3 = "1"
AND Numeric1.Col4 = "2"
AND Numeric2.Col1 = "1"
AND Numeric2.Col2 = "1"
AND Numeric2.Col3 = "1"
AND Numeric2.Col4 = "4"
AND Numeric3.Col1 = "1"
AND Numeric3.Col2 = "1"
AND Numeric3.Col3 = "1"
AND Numeric3.Col4 = "5"
AND
WHERE AlphaNumeric.Col1 = "1"
AND AlphaNumeric.Col2 = "1"
AND AlphaNumeric.Col3 = "1"
AND AlphaNumeric.Col4 = "2"
AND AlphaNumeric1.Col1 = "1"
AND AlphaNumeric1.Col2 = "1"
AND AlphaNumeric1.Col3 = "1"
AND AlphaNumeric1.Col4 = "4"
AND AlphaNumeric2.Col1 = "1"
AND AlphaNumeric2.Col2 = "1"
AND AlphaNumeric2.Col3 = "1"
AND AlphaNumeric2.Col4 = "5"
AND AlphaNumeric3.Col1 = "1"
AND AlphaNumeric3.Col2 = "1"
AND AlphaNumeric3.Col3 = "1"
AND AlphaNumeric3.Col4 = "7";

CREATE TABLE Numeric(
RecordNumber INTEGER NOT NULL PRIMARY KEY
,Col1 BIT NOT NULL
,Col2 BIT NOT NULL
,Col3 BIT NOT NULL
,Col4 INTEGER NOT NULL
,Value INTEGER NOT NULL
);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,1,5);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,2,750);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,3,5);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,4,3);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,5,1000000);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,6,3500);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,7,4);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,8,99);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,9,75);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,10,3);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,1,5);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,2,154);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,3,441);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,4,3);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,5,23);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,6,34);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,7,45);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,8,66);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,9,789);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,10,50);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,1,1);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,2,7);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,3,5);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,4,88);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,5,74);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,6,35);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,7,45);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,8,54);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,9,25);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,10,35);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,1,35);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,2,22);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,3,15);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,4,99999);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,5,2588);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,6,3752);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,7,7588);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,8,1);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,9,0);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,10,3);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,1,565);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,2,5986);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,3,350);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,4,12580);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,5,2500);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,6,3500);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,7,145);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,8,25);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,9,35);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,10,75);

CREATE TABLE AlphaNumeric(
RecordNumber INTEGER NOT NULL PRIMARY KEY
,Col1 BIT NOT NULL
,Col2 BIT NOT NULL
,Col3 BIT NOT NULL
,Col4 INTEGER NOT NULL
,Value VARCHAR(17) NOT NULL
);
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,1,'123 Any Street');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,2,'Yes');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,3,'Chapter 5');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,4,'Default');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,5,'Now');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,6,'Green');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,7,'Similar');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,8,'Required');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,9,'Good Condition');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100001,1,1,1,10,'Fire');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,1,'751 Any Street');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,2,'Maybe');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,3,'Chapter 5');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,4,'New');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,5,'Past Due');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,6,'Joke');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,7,'Inferior');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,8,'Not Available');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,9,'Fair Condition');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100002,1,1,1,10,'Water');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,1,'859 Any Street');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,2,'Yes');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,3,'Chapter 4');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,4,'No Good');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,5,'When');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,6,'Silver');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,7,'Superior');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,8,'Available');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,9,'Avg Condition');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100003,1,1,1,10,'Wind');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,1,'7561 Any Street');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,2,'No');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,3,'Chapter 2');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,4,'Find');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,5,'Where');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,6,'Purple');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,7,'Slightly Superior');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,8,'Fine');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,9,'Good Condition');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100004,1,1,1,10,'Earth');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,1,'954 Any Road');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,2,'Sometimes');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,3,'Chapter 10');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,4,'Default');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,5,'Now');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,6,'Brown');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,7,'Not Similar');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,8,'Not Required');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,9,'N/A Condition');
INSERT INTO mytable(RecordNumber,Col1,Col2,Col3,Col4,Value) VALUES (100005,1,1,1,10,'Fire');

robert_volk · August 9, 2023, 10:45pm

What's the CREATE TABLE structure for myTable? It's not defined, and the INSERT statements for it could cause an error based on the values you've posted.

Also, you are INSERTing bit values using double quotes, which is unnecessary and could also cause a syntax error.

robert_volk · August 9, 2023, 11:58pm

On the assumption that myTable refers to the relevant Numeric and AlphaNumeric tables for each block of INSERT statements, then the following should provide the results you're asking for:

;WITH numeric_CTE AS(
 SELECT RecordNumber,[Number1],[Number2],[Number4],[Number5]
 FROM (SELECT n.RecordNumber,n.Value,CONCAT('Number',n.Col4) name 
 FROM Numeric n 
 WHERE n.Col1=1 AND n.Col2=1 AND n.Col3=1 AND n.Col4 IN(1,2,4,5)) x
 PIVOT(MAX(value) FOR name IN ([Number1],[Number2],[Number4],[Number5])) y)
,alpha_CTE AS(
 SELECT RecordNumber,[Alpha2],[Alpha4],[Alpha5],[Alpha7]
 FROM (SELECT a.RecordNumber,a.Value,CONCAT('Alpha',a.Col4) name 
 FROM AlphaNumeric a 
 WHERE a.Col1=1 AND a.Col2=1 AND a.Col3=1 AND a.Col4 IN(2,4,5,7)) b
 PIVOT(MAX(value) FOR name IN ([Alpha2],[Alpha4],[Alpha5],[Alpha7])) c)
SELECT nc.*,ac.Alpha2,ac.Alpha4,ac.Alpha5,ac.Alpha7 
FROM numeric_CTE nc 
INNER JOIN alpha_CTE ac ON nc.RecordNumber=ac.RecordNumber

The columns are named somewhat differently, and there's an extra from each table that was JOINed, but its columns weren't included in the SELECT clause.

SaSaSQL · August 10, 2023, 1:55pm

First of all, thank you. And, yes you are correct in your assumption regarding myTable. I used convertCSV and then failed to rename the INSERT statements.

While I am going to try and see if the query works, I am interested in learning how it works and how to write complex queries. Do you know of any course I can take that would help me learn?

robert_volk · August 10, 2023, 9:05pm

PIVOT is explained here:

It allows you to categorize/GROUP data into new columns, specified by the FOR...IN... segment of the PIVOT clause. Since you are condensing one or more rows into a single value, you must PIVOT with an aggregate function like SUM, AVG, MIN or MAX. Since some of your data is non-numeric, and since you only have 1 value for each combination of RecordNumber and Col4 category, you can use MIN() or MAX() as your aggregate.

CONCAT is used to combine the Col4 value with a Number or Alpha prefix, which then becomes the PIVOTed column names in each Common Table Expression (CTE). Any columns in the SELECT clause that are not specified in the PIVOT column groups are essentially GROUPed, in this case, just RecordNumber.

The final SELECT statements pulls from both CTEs and JOINs them on RecordNumber.

If you want to simplify querying this data, you might consider creating a view using this query, but extending it to cover all the Col4 values, and possibly creating better column names for the relevant IDs. Then you can SELECT from the view and it does all the JOIN/PIVOT/etc. for you. Alternately, you could also have a table-valued user defined function that can accept parameters for RecordNumber, that might perform better than the view.

I can't really recommend any courses, online, books, or anything else to learn SQL. I have found Manning and O'Reilly to be excellent publishers though, and they have digital subscriptions that allow you to get updates to any ebooks they publish.