General discussion

Locked

Help with SQL statement

By Mr. Origami ·
I aksed a similar question a while ago, but need my SQL to do 1 more thing.
1 table (T1) and 2 fields (c1 and c2). Table lists matching colors, so rows are:
red blue
red white
blue red
black red
blue green

Original SQL filtered out the doubles - so in this case 4 rows would be returned (red,blue is the same as blue,red). The additional step is to [a] prompt user to enter color, then display all matching colors. So if user enters "red" a list is returned showing "blue, white, black". If user entered "blue", the returned list would be "red, green".
Do not want to MAKE a table, but a temporary table is fine. If it helps, I'll paste the original SQL below.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Help with SQL statement

by Mr. Origami In reply to Help with SQL statement

Here's the current SQL that filters doubles, but offers no prompts nor does it return just the "matching" color to what was entered:
----------------------
SELECT DISTINCT IIf(myT1_1.C1<myT1_1.C2,myT1_1.C1,myT1_1.C2) As myC1, IIf(myT1_1.C1<myT1_1.C2,myT1_1.C2,myT1_1.C1) As myC2
FROM T1 As myT1_1 LEFT JOIN T1 As myT1_2 ON (myT1_1.C1 = myT1_2.C2) AND (myT1_1.C2 = myT1_2.C1)
WHERE myT1_2.C1 Is Not Null
UNION SELECT DISTINCT myT1_1.C1 As myC1, myT1_1.C2 As myC2
FROM T1 AS myT1_1 LEFT JOINT1 AS myT1_2 ON (myT1_1.C1 = myT1_2.C2) AND (myT1_1.C2 = myT1_2.C1)
WHERE myT1_2.C1 Is Null;

Collapse -

Help with SQL statement

by igp In reply to Help with SQL statement

SELECT * FROM T1 WHERE c1 like "%red%"

SELECT * FROM T1 WHERE c1 like "%blue%"

%blue% - any line that contain word "blue"
blue% - any line that begin from word "blue"
%blue - any line that ends on word "blue"
etc....

Collapse -

Help with SQL statement

by Mr. Origami In reply to Help with SQL statement

Thanks - but how do I "merge" your SELECT statements into my above SQL?

Collapse -

Help with SQL statement

by smart_santanu In reply to Help with SQL statement

Just use the union to the two SQL statements given by igp

Collapse -

Help with SQL statement

by SQL Man In reply to Help with SQL statement

Here is a way to do it in 2 seperate queries. Assuming the query you listed above is named q1, this will give you what you are looking for... if I understand you problem.

SELECT q1.myC1
FROM q1
WHERE (((q1.myC2)=[Enter Color]))
UNION
SELECT q1.myC2
FROM q1
WHERE (((q1.myC1)=[Enter Color]));

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums