mysql - SQL query to select distinct rows from left table after inner join to the right table -


i have 2 mysql tables. first 1 [products] (primary = id):

[id] | title | description

and second [sizes] (primary = id & size):

[id] | [size]

size can have values [1,2,3,4,5,6].

i have php array has size values. reshape comma-separated values string this:

$size_list = implode(",", $sizes);

for not familiar php, above code generate string this: "1,4,5" , query database this:

$query = "select t1.id,t1.title,t1.description,t2.size products t1 inner join sizes t2 on t1.id=t2.id size in(".$size_list .")";

but query replicates products each size have in sizes table. want to:

return records products table have @ least 1 available size in sizes table, without duplicate

and of course

want sizes in variable show client

for example:

products:

1 | product1 | description1 2 | product2 | description2 3 | product3 | description3 4 | product4 | description4

sizes:

1 | 1 1 | 2 1 | 4 1 | 5 2 | 1 2 | 5

given $sizes_list="1,2", want output is:

1 | product1 | description1 | 1,2,4,5 2 | product2 | description2 | 1,5

your query should like:

$query = "     select t1.id, t1.title, t1.description, group_concat(t2.size separator ",") sizes     products t1        inner join sizes t2 on t1.id=t2.id     t1.id in (select t3.id sizes t3 t3.size in (".$size_list .")     group t1.id, t1.title, t1.description "

a bit of explanation. when join 2 tables, rows table sizes id table products, id = 1 joined 4 records , id = 2 joined 2 records. have aggregate numbers 1 record.


Comments

Post a Comment

Popular posts from this blog

c++ - End of file on pipe magic during open -

i have c++ application in starting process(wireshark) following. if (fp == null){ fp = popen(processpath, "r"); //processpath process want start if (!fp){ throw std::invalid_argument("cannot start process"); } fprintf(fp, d_msg);//d_msg input want provide process } else if(fp != null){ fprintf(fp, d_msg); } the problem when execute c++ application, start wireshark error end of file on pipe magic during open what should avoid that? also tried using mkfifo create named pipe , execute it. used this: if (fp == null){ system("mkfifo /tmp/mine.pcap"); fp = popen("wireshark -k -i /tmp/mine.pcap", "r"); if (!fp){ dout << "cannot start wireshark"<<std::endl; throw std::invalid_argument("cannot start wireshark"); } input = fopen("/tmp/mine.pcap", "wb"); fprintf(input , d_msg); fclose(input)...
Read more

basic authentication with http post params android -

i doing basic authentication passing username , password , using basicnamevaluepair sending post params response service. my method: public stringbuilder callservicehttppost(string username, string password, string type) { // create new httpclient , post header httpclient httpclient = new defaulthttpclient(); httppost httppost = new httppost(webservice + type); httpresponse response = null; stringbuilder total = new stringbuilder(); try { url url = new url(webservice + type); /*string base64encodedcredentials = base64.encodetostring((username + ":" + password).getbytes(), base64.url_safe | base64.no_wrap);*/ string base64encodedcredentials = "basic " + base64.encodetostring( (username + ":" + password).getbytes(), base64.no_wrap); httppost.setheader("auth...
Read more

data.table making a copy of table in R -

i doing this: myfun <- function(inputvar_vec){ # inputvar_vec input vector # # result = output vector return(result) } dt[, result := lapply(.sd, myfun), = byvar, .sdcols = inputvar] i getting following warning: warning message: `in `[.data.table`(df1, , `:=`(prop, lapply(.sd, propeventinlastk)), : invalid .internal.selfref detected , fixed taking copy of whole table, := can add new column reference. @ earlier point, data.table has been copied r (or been created manually using structure() or similar). (and more stuff) .... ` my guess because stacking result vectors (after operation), copy being made? can suggest method remove warning? have done using apply functions , thought should extendable here too. my other question is: can pass chunk of rows data frame (subsetted using statement), , call function myfun operate on that? adding example requested # generate data n = 10000 default=na value = 1 df = data.table(id = sample(1:5000, n, replace=true), ...
Read more