Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2016
    Posts
    2

    Answered: query to select multiple rows from single column

    Hello everybody,
    I'm trying to get multiple values from one column in table with foreign key. Let's say that I have a table:

    CREATE TABLE currencies (
    'id' int not null auto_increment,
    'user_id' not null (with foreign key),
    'currency' varchar(20) not null
    )

    I want to select values (EUR, USD, NOK) from 'currency' column with user_id 2.
    I tried a query like 'SELECT user_id FROM currencies WHERE currency IN ('EUR', 'USD', 'NOK') AND user_id = 2' , but I need the rresult to be returned only if there are all three values, not only (EUR and USD) or just (USD). Any help is appreciated. I hope you apprehend what I want. I'm not able to explain in better. Thanks in advance for your help.

  2. Best Answer
    Posted by healdem

    "
    Quote Originally Posted by Vladis View Post
    But you probably understood that values in my table is written like below:

    ...
    but it's not the case. Data in the table are written like this:
    ID | USER_ID | CURRENCY
    ---------------------------------------
    1 | 38 | EUR
    2 | 38 | USD
    3 | 38 | NOK
    4 | 42 | EUR
    5 | 42 | NOK
    6 | 50 | USD

    oops my bad
    try
    Code:
    SELECT USER_id, COUNT(CURRENCY)	FROM CURRENCIES
    where currencY in ('USD', 'NOK','EUR')
    GROUP BY USER_id
    HAVING COUNT(CURRENCY) = 3
    "


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Best solution
    Design your db properly using the rules of normalisation
    Or pay the price for flaky design and do something like
    Code:
    .... where currency like '%EUR%' AND currency like '%USD%' AND currency like '%NOR'
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Mar 2016
    Posts
    2
    Quote Originally Posted by healdem View Post
    Best solution
    Design your db properly using the rules of normalisation
    Or pay the price for flaky design and do something like
    Code:
    .... where currency like '%EUR%' AND currency like '%USD%' AND currency like '%NOR'

    Hi, thanks for your reply.
    But you probably understood that values in my table is written like below:

    ID | USER_ID | CURRENCY
    ---------------------------------------
    1 | 38 | EUR, USD, NOK
    2 | 42 | EUR, NOK


    but it's not the case. Data in the table are written like this:
    ID | USER_ID | CURRENCY
    ---------------------------------------
    1 | 38 | EUR
    2 | 38 | USD
    3 | 38 | NOK
    4 | 42 | EUR
    5 | 42 | NOK
    6 | 50 | USD

    I'm trying to do search filter based on checkboxes with names of currency and display which users have all three currencies (eur, usd, nok). The query with WHERE ... IN('eur', 'usd', 'nok') returns all user_id and it would return only user_id 38.

  5. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you would have to use multiple occurrences of the table, one for each currency. Would probably perform best with an EXISTS subselect for each.
    Dave

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Vladis View Post
    But you probably understood that values in my table is written like below:

    ...
    but it's not the case. Data in the table are written like this:
    ID | USER_ID | CURRENCY
    ---------------------------------------
    1 | 38 | EUR
    2 | 38 | USD
    3 | 38 | NOK
    4 | 42 | EUR
    5 | 42 | NOK
    6 | 50 | USD

    oops my bad
    try
    Code:
    SELECT USER_id, COUNT(CURRENCY)	FROM CURRENCIES
    where currencY in ('USD', 'NOK','EUR')
    GROUP BY USER_id
    HAVING COUNT(CURRENCY) = 3
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •