mysql - MS-ACCESS How to create query to select all records with fields matching from another query? -

-

i know question may sound confusing, let me try simplify it.

i have query, let's call query1, , larger table of products. here query1:

item_code    description    order_qty    option 1000         prod1          5            blue 1005         prod5          3            brown 1602         prod6          1            red 5620         prod8          6            yellow 9865         prod2          1            brown 1624         prod3          3            brown 9876         prod12         4            blue

now in table, have bigger list of products same format. want make new query contains blue, brown, red, , yellow. works, there duplicate.

i'm not sure how post attempt i'll explain tried. made new query , included table , query1. made relationship between two, include rows "option" equal. reason resulting query come out repeats. such as:

item_code    description    order_qty    option 1000         prod1          5            blue 1009  <--    prod2          6            blue     1009  <--    prod2          6            blue     1010  <--    prod9          7            blue     1010  <--    prod9          7            blue     1011  <--    prod11         9            blue     1011  <--    prod11         9            blue     9876  <--    prod12         4            blue     9876  <--    prod12         4            blue 1005  <--    prod5          3            brown 1005  <--    prod5          3            brown 9865  <--    prod2          1            brown 9865  <--    prod2          1            brown 1624  <--    prod3          3            brown 1624  <--    prod3          3            brown 9877         prod99         7            brown 1111  <--    prod67         8            brown    1111  <--    prod67         8            brown     1602         prod6          1            red 1752         prod56         2            red 5620         prod8          6            yellow

and worst part is, won't repeat. maybe i'm approaching wrong.

i know may case of tldr, if great.

thanks.

sounds need add group by clause or distinct keyword query.

select distinct item_code, description, order_qty, option

(or click on whatever option in access same thing.)

it's impossible diagnose why query returning "duplicate" rows given information you've provided.

the query returning "duplicate" rows when row in all_products matching 2 or more rows query1, copy of row all_products each matching row query1.

you might better performance if got distinct list of option query1. (i don't "do" ms access. brought question because tagged "mysql". jet database engine cool , all, doesn't work in multiuser environment.) in sql server, oracle, mysql, db2, teradata, et al. we'd write query this:

select p.item_code      , p.description      , p.order_qty      , p.option   mytable p   join ( select q1.option            query1 q1           group q1.option        ) q     on q.option = p.option

Comments

Post a Comment

Popular posts from this blog

ios - RestKit 0.20 — CoreData: error: Failed to call designated initializer on NSManagedObject class (again) -

-
this error has shown on many times, seems. prominent answer issue seems restkit 0.20 — coredata: error: failed call designated initializer on nsmanagedobject class however, solution posted doesn't work me. let's start beginning: i want upload file server. want parse response make sure uploaded. if not, try again later. model has boolean variable called nsnumber *senttoserver purpose (in coredata, bool saved nsnumber ) . because don't know whether sending going successful, save entity coredata model first before sending via restkit. when post request successful, server returns json string this: {"id":14,"created":true} notice that, of course, id not set on coredata model yet, both id , created new informations model. here's model: @interface recording : nsmanagedobject @property (nonatomic, retain) nsstring * audiofilepath; @property (nonatomic, retain) nsdate * createdat; @property (nonatomic, retain) nsstring * deviceid; @...
Read more

java - Digest auth with Spring Security using javaconfig -

-
so i'm trying create digest authentication spring following documentation trying translate xml "requirements" in java requirements. let's have xml in docs: <bean id="digestfilter" class= "org.springframework.security.web.authentication.www.digestauthenticationfilter"> <property name="userdetailsservice" ref="jdbcdaoimpl"/> <property name="authenticationentrypoint" ref="digestentrypoint"/> <property name="usercache" ref="usercache"/> </bean> <bean id="digestentrypoint" class= "org.springframework.security.web.authentication.www.digestauthenticationentrypoint"> <property name="realmname" value="contacts realm via digest authentication"/> <property name="key" value="acegi"/> <property name="noncevalidityseconds" value="10"/> </b...
Read more

laravel - PDOException in Connector.php line 55: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) -

-
my .env: app_env=local app_debug=true app_key=myappkey app_url=http://localhost db_connection=mysql db_host=localhost db_port=3306 db_database=gestiontaller db_username=root db_password='root' cache_driver=file session_driver=file queue_driver=sync redis_host=127.0.0.1 redis_password=null redis_port=6379 mail_driver=smtp mail_host=mailtrap.io mail_port=2525 mail_username=null mail_password=null mail_encryption=null i've tried password ", ' , without, restarting server every time. i've tried changing localhost 172.0.0.1. yes, password correct, running mysql -u root -p introducing password root in prompt can go mysql. users table: +------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.sys | localhost | | root | localhost | | root1 | localhost | +------------------+-----------+ i've added homestead.yaml this: database_port: 3306 ...
Read more