How to obtain specific data in a SQL WHERE clause

Question

I'm very new to SQL and was wondering if someone could direct me on how to obtain specific rows of data using the WHERE clause of an SQL file. This is my current WHERE clause:

CREATE or REPLACE view V_Status1 
AS
    SELECT 
        A.C0401_aid, B.C0401_description DESCR, 
        A.c0432_date DATEREQUIRED, A.C0432_sampl_value_r
    FROM vhis_data A, T0401_accounts B
    WHERE A.C0401_aid = B.C0401_aid 
      AND (B.C0401_aid = 5486 OR B.C0401_aid = 5489 OR B.C0401_aid = 5490);

5486, 5489 and 5490 represent the accounts that I need data from, but my current clause does not make sense for what I want. I need the data from all three accounts not just one. Please let me know if you need any clarification.

Secondly, post the rest of your query. I suspect the issue lies in your table joins. — Matt, Jul 06 '15 at 19:17
Like already mentioned, you'll want to post your full query, the results you are getting now, and what the result should look like. Also, which database are you using? MySQL or SQL Server? ...so that you can adjust the question's tag. — sstan, Jul 06 '15 at 19:19
I have added the full query. Also sorry for the confusion and correct me if I'm wrong, but I am using Oracle. — rmdlp, Jul 06 '15 at 19:23
The join is posted in the query. `FROM vhis_data A, T0401_accounts B` is a `LEFT JOIN` by default, and the criteria is set in the `WHERE` clause. This is not the recommended `JOIN` syntax, but it does work. — dub stylee, Jul 06 '15 at 19:23
The values I need are telemetry values out in the field. I believe, my FROM clause is correct. I'm just having trouble with the WHERE clause to select the three accounts. I will try your suggestions @Matt — rmdlp, Jul 06 '15 at 19:25
If your query (or the suggested answers) don't work, I suggest you check your data and verify the data you expect actually exists for these 3 accounts. — Matt, Jul 06 '15 at 19:29
@dub stylee: It isn't a left join by default. What makes you think that it is? — sstan, Jul 06 '15 at 19:31
@dub stylee & sstan, technically it's a cartesian join until a connection is specified, either in the JOIN or WHERE clause. — Matt, Jul 06 '15 at 19:32
I thought I had read somewhere that the default JOIN type if none is specified is a LEFT JOIN, but I see in http://stackoverflow.com/a/15892024/3101082 that the default JOIN in Oracle is INNER JOIN. — dub stylee, Jul 06 '15 at 19:43
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Jul 06 '15 at 20:41

score 2 · Accepted Answer · edited Jul 06 '15 at 19:56

2

You can use the in operator

example:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

Your case will looks like :

WHERE A.C0401_aid = B.C0401_aid AND
B.C0401_aid in(5486,5489,5490);

edited Jul 06 '15 at 19:56

sstan

35,425
6
48
66

answered Jul 06 '15 at 19:20

Abder KRIMA

3,418
5
31
54

score 1 · Answer 2 · answered Jul 06 '15 at 19:26

1

Cleaned up the syntax, but this should perform the same operation your current query performs.

CREATE or REPLACE view V_GTAAStatus1 AS
SELECT A.C0401_aid, 
       B.C0401_description DESCR, 
       A.c0432_date DATEREQUIRED, 
       A.C0432_sampl_value_r [column_name]
FROM vhis_data A
LEFT JOIN T0401_accounts B on A.C0401_aid = B.C0401_aid
WHERE B.C0401_aid IN (5486,5489,5490);

answered Jul 06 '15 at 19:26

Matt

1,115
13
29

See comment on @dubstylee's answer. – Matt Jul 06 '15 at 19:30
Well, I think it would be best to remove that. If anything, it looks buggy, because the `WHERE` clause invalidates the `LEFT JOIN` in any case. – sstan Jul 06 '15 at 19:36

score 1 · Answer 3 · answered Jul 06 '15 at 19:27

1

You are using the legacy JOIN syntax. @TinyOS answer is correct, here is your original query in the more accepted current syntax:

CREATE or REPLAC view V_GTAAStatus1 AS
SELECT A.C0401_aid, B.C0401_description DESCR, 
   A.c0432_date DATEREQUIRED, A.C0432_sampl_value_r
FROM vhis_data A
INNER JOIN T0401_accounts B ON A.C0401_aid = B.C0401_aid
WHERE B.C0401_aid IN (5486, 5489, 5490);

answered Jul 06 '15 at 19:27

dub stylee

3,252
5
38
59

@Matt beat me to it. Both queries should have the same result, the only difference being that using `INNER JOIN` will filter out any mismatching rows before the `WHERE` clause, while the `LEFT JOIN` will filter them out with the `WHERE` clause. – dub stylee Jul 06 '15 at 19:29

How to obtain specific data in a SQL WHERE clause

3 Answers3