-4

I want to select/filter by 2 columns status and state from a table of 6 columns . I have written the query below to select cars that are unsold and either new or used but the filter returns both new and used cars. The query ought to return just unsold cars that are new if new is specified or unsold cars that are used if used is specified. What am I doing wrong?

SQL statement using parameterised queries:

export const statusStateQuery = 'SELECT * FROM cars WHERE status = $1 AND (state = $2) OR (state = $3)';

Implementation:

try {
   const { rows, rowCount } = await 
db.query(statusStateQuery, ['unsold', 'new', 'used']);
...
} catch(){...}

If status=unsold and state=new, it should return something like:

[
  {
  id: 1,
  status: 'unsold',
  state: 'new',
  imageurl: 'imageurl here...'
  ...
  },

  {
  id: 2,
  status: 'unsold',
  state: 'new',
  imageurl: 'imageurl here...'
  ...
  }
  ...
]

if status=unsold and state=used, it should return

[
 {
  id: 6,
  status: 'unsold',
  state: 'used',
  imageurl: 'imageurl here...'
  ...
 },

 {
  id: 10,
  status: 'unsold',
  state: 'used',
  imageurl: 'imageurl here...'
  ...
 }
 ...
]

closed as off-topic by mustaccio, dezso, John Eisbrener, Jon of All Trades, Paul White Jun 19 at 7:03

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Jon of All Trades, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Welcome to DBA.SE! If you could provide a Minimal, Complete, and Verifiable Example, that would help immensely. As it stands now, you're question is hard to follow as we don't have a good idea of what your data looks like. If you can recreate your problem with a MCVE, we would be much better equipped to answer your question. – John Eisbrener Jun 18 at 16:40
3

This is rather simple - but I (vaguely :-) ) remember when I could barely JOIN two tables - so I sympathise :-)

You have:

SELECT * FROM cars WHERE status = unsold AND (state = used OR state = new)

I presume that you are using the ; statement terminator?

SELECT * FROM cars WHERE status = unsold AND (state = used OR state = new);

But, that alone won't do the trick. In queries, for fields (status & state) that are strings, you have to delimit (surround) them with single quotes, so your statement should look more like this:

SELECT * FROM cars WHERE status = 'unsold' AND (state = 'used' OR state = 'new');

On my profile, there are several articles on how to ask questions here - you might want to take a look?

Providing us with the table DDL (Data Definition Language) (i.e. CREATE TABLE blah (...); (see below) would have been a big help here for example - always provide the maximum information possible - if in doubt, put it in!

For PostgreSQL, the command \d table_name or \d+ table_name would have been great. Even better would be, table structure(s), sample data (in the form of DML (Data Manipulation Language) - INSERT INTO blah VALUES (....); - checkout the articles I mentioned.

For example, on my test database, I have a table called test_article, so to get its structure, I can do

pg_dump -t test_article -d test --schema-only

-t says it's the table test-article I want,
-d says it's the database/schema test I want, and
--schema-only tells pg_dump that I only want the table structure and not data - this is what you'd want if you don't want any of the table's data - you could construct a sample table and use that - then remove the --schema-only part of the bash command.

Output (important part):

CREATE TABLE public.test_article (
    the_series integer,
    user_id integer,
    article_id integer,
    rating numeric
);

Information like this can be vital in helping us to solve your problems!

You can even run the pg_dump command from within the psql PostgreSQL shell using the \! psql command which invokes the bash shell - i.e.

test=# \! ./bin/pg_dump -t test_article -d test --schema-only;

Same result (and the best solution).

Take a look at a few SQL tutorial sites - pay attention to the ones specific to PostgreSQL, but my fave (and yours) database's SQL is by a long way the most compatible with the ANSII/ISO SQL standards - see here - in particular the Interoperability and Standardisation section.

p.s. welcome to the forum! :-)

  • Thanks for pointing out that I need to add a single quote to the strings but I actually omitted that from my question mistakenly. I was doing that already in my code. But it didn't work either and after searching and trying several things, I decided to ask here. – camelCase Jun 19 at 1:10
  • 4
    @camelCase Aw, c'mon. You should have known that things like single quotes matter in computing. How shall one answer a question that contains the wrong code? – Laurenz Albe Jun 19 at 5:05
  • 1
    I have to agree with @LaurenzAlbe here - we can only answer questions as asked. We are not able to "know" what you meant, only what you write. If you're still having problems with your query, please rephrase your question and put in separate comments for both Laurenz's and my attention or if the question as rephrased is sufficiently different, you can ask a new one with a link back to this one to provide context. A rephrased question will show up in the active list. – Vérace Jun 19 at 7:28
1

The way your query is written, it has to return cars that are new or used.

If you want to have either new or used cars, write the query like this:

SELECT *
FROM car
WHERE status = $1 AND state = $2

Then supply either 'new' or 'used' for $2, depending on which cars you want.

Remark: avoid using * in SQL code. It can lead to problems if the table is modified later. Always use an explicit select list.

  • I have read your modified question, and my answer is the correct one. Your query asks for car that are new or used, so you should not be surprised if you get both. – Laurenz Albe Jun 19 at 10:45
  • I was able to figure it out as I wanted. Thanks for your time, I really appreciate that. – camelCase Jun 19 at 11:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.