MySQL sub query or join statement??

[dpalame]

I have ripped my hair out trying to solve my php query issue.  Hope someone can help.  I have a table that looks like this:

 

storeaddressupc

Whole Foods1 road001

Whole Foods1 road002

Whole Foods1 road003

Whole Foods1 road004

 

Now I have no problem when I query like this:

 

SELECT *,GROUP_CONCAT(upc,' --- $',retailprice,' <br/> ') upcs  FROM Records  WHERE upc LIKE '001' OR upc LIKE '003' GROUP BY LAT,LONG

(this returns all the records)

 

Problem is when a want to query AND instead of OR:

 

SELECT *,GROUP_CONCAT(upc,' --- $',retailprice,' <br/> ') upcs  FROM Records  WHERE upc LIKE '001' AND upc LIKE '003' GROUP BY LAT,LONG

 

Of course this second query doesn't return any results because my table has the upcs in individual rows.  Is there a nested query and/or join statement that would put together the upcs in one field and then I could query that one?  Thanks for any and all help.

 

[AyKay47]

1. what is the point of using the LIKE clause if you aren't using a wildcard? why not just use  =

2. yes, you can use a self join, I will post skeleton code that you can use to get the idea.

 

SELECT records1.upc as group1, records2.upc as group2
FROM records records1
JOIN records records2 
ON records2.upc = '001'
WHERE records1.upc = '003'

[SergeiSS]

Is there a nested query and/or join statement that would put together the upcs in one field and then I could query that one?

I think it's my language problem because English is not my native language.... But could you explain more detail your question?

[dpalame]

I'm sorry I am still confused.  I don't know how to loop through this with many variables.  If I query:

 

select * from records where upc='001' or upc='002' that works fine.

 

but if I use select * from records where upc='001' AND upc='002' AND upc='003' then I won't get results because each upc is on a different row.

 

How can I loop through the table looking for rows that contain both upcs and still get a result set?  The store address is a good common key that would be in each row?

 

Any help is greatly appreciated.  Thank you.

[AyKay47]

I'm sorry I am still confused.  I don't know how to loop through this with many variables.  If I query:

 

select * from records where upc='001' or upc='002' that works fine.

 

but if I use select * from records where upc='001' AND upc='002' AND upc='003' then I won't get results because each upc is on a different row.

 

How can I loop through the table looking for rows that contain both upcs and still get a result set?  The store address is a good common key that would be in each row?

 

Any help is greatly appreciated.  Thank you.

did you not read my post at all?

[dpalame]

I apologize I did read it and modified your skeleton code to the following which does work:

 

SELECT * FROM storeCheckRecords AS t1 JOIN storeCheckRecords AS t2 ON t1.e_address = t2.e_address WHERE (t1.upc = '075172079307' AND t2.upc = '075172079659')

 

My question is two fold:

 

a. how do I take it a step further with 3,4,5.....10 variables?

 

b. is there a way to use this same query statement with only one variable?

 

Thanks for your help

[AyKay47]

a. use mysql's IN clause, e.g.

 

select column from table where upc IN (100,150,200,300,400);

 

b. why would you need a self join with only one variable?

[dpalame]

I don't think I would.  I am trying to have one query where I can plug in variables based on 1 or more selections.

 

I tried your IN query and it searches like an OR statement.  How can I find results like an AND query.  For example:

 

select * from storeCheckRecords where upc IN (001,002,003) - returns results for 001 and 002.  I would like to only return results if all three are IN.  Thanks for your help.

[AyKay47]

the only thing i can think of atm is multiple joins

[dpalame]

Thanks for the help here is what I finally went with for anybody interested:

 

//$at1 is an array being passed in from a multiple select box - break it apart

$remove_these = array ( ',' , '.' );

$at1 = str_replace ( $remove_these , ' ' , $at1 );

$at1 = str_replace ( '  ' , ' ' , $at1 );

// After we've cleaned the string, we break it down into separate words

$at1 = explode ( ' ' , $at1 );

 

// Set Variables number at 0

$numberofvariables=0;

 

// Find out how many variables are in my array and set the first one into $onevariable

foreach ($at1 as $val){

$numberofvariables++;

$onevariable = $val;

}

// If there is only one variable do a simple query else build the more complex JOIN and loop for each variable

if ($numberofvariables<2){

 

$query= "SELECT * FROM storeCheckRecords WHERE upc IN ($onevariable)";

 

}else{

 

$rank=1;

$joinrank=1;

$joinrankstart=1;

$joins ="storeCheckRecords AS t" . $joinrank++;

$upcs = "(";

$upcrank=1;

$fields = "t1.*,";

foreach ($at1 as $val){

$fields.="t" . $rank . ".upc as upc". $rank++ . ",";

$joins.=" JOIN storeCheckRecords AS " . "t" . $joinrank++ . " ON t" . $joinrankstart++ . ".e_address = t" . $joinrankstart . ".e_address ";

$upcs.="t" . $upcrank++ . ".upc = '" . $val . "' AND ";

}

$fields = substr($fields, 0, -1);

$joins = substr($joins, 0, -60);

$upcs = substr($upcs, 0, -4) . ')';

$query = "SELECT $fields FROM $joins WHERE $upcs";

}

 

Final queries example:

 

1 variable = SELECT * FROM storeCheckRecords WHERE upc IN (001)

 

multiple variables = SELECT t1.*,t1.upc as upc1,t2.upc as upc2,t3.upc as upc3 FROM storeCheckRecords AS t1 JOIN storeCheckRecords AS t2 ON t1.e_address = t2.e_address JOIN storeCheckRecords AS t3 ON t2.e_address = t3.e_address WHERE (t1.upc = '001' AND t2.upc = '002' AND t3.upc = '003' )

 

Probably not the best solution, but it works considering mySql table and data structure.

 

Thanks again for all your help!

[AyKay47]

If I find a more efficient solution, I will post it on here

[dpalame]

Thanks for your help again.  Next question.  My query looks like this:

 

SELECT t1.brand,t1.biz_name,t1.biz_info,t1.biz_info,t1.e_address,t1.e_city,t1.e_state,t1.e_postal,t1.e_zip_full,t1.loc_LAT_centroid,t1.loc_LONG_centroid,t1.biz_phone,t1.biz_phone_ext,t1.biz_fax,t1.biz_email,t1.web_url,t1.upc as upc1,t1.retailprice as retailprice1,t1.dollar_sales as dollar_sales1,t1.dollar_sales_ly as dollar_sales_ly1,t1.todaydate as todaydate1,t1.datetimesql as datetimesql1,t1.shelfposition as shelfposition1,t1.reg_sale as reg_sale1,t1.representative as representative1,t1.notes as notes1,t2.upc as upc2,t2.retailprice as retailprice2,t2.dollar_sales as dollar_sales2,t2.dollar_sales_ly as dollar_sales_ly2,t2.todaydate as todaydate2,t2.datetimesql as datetimesql2,t2.shelfposition as shelfposition2,t2.reg_sale as reg_sale2,t2.representative as representative2,t2.notes as notes2 FROM storeCheckRecords AS t1 JOIN storeCheckRecords AS t2 ON t1.e_address = t2.e_address WHERE (t1.upc = '075172071639' AND t2.upc = '075172079468' )

 

Now say I want to add where a upc that doesn't exist to this query?  I tried adding (t1.upc = '075172071639' AND t2.upc = '075172079468' AND t1.upc<>'075172079307' AND t2.upc<>'075172079307') at the end but it is not producing the results.  Is there a way to do this?  Thanks for your help.