Easy MySql question

[Anon]

Hi

I don't know all MySql commands and I'm sure that I only need a simple command that I don't actualy know.

There is my problem...

I have 2 table:
TABLE_A
Id
Name

TABLE_B
Id
Id_A
Item

I want to select all Name that have an Item named 'Coffee' AND and Item named 'Milk'.

I try something stupid like:
"select A.Name
from TABLE_A as A, TABLE_B as B
where A.Id = B.Id_A
AND Item = 'Coffee'
AND Item = 'Milk'"

That can't work because an Item can't have to name as the same time...

Thanks
Bbp

[Anon]

hi Bbp,

"select A.Name
from TABLE_A as A, TABLE_B as B
where A.Id = B.Id_A
AND Item = 'Coffee'
OR(!) Item = 'Milk'"

hth
chris

[Anon]

Thanks Chris

I try to put an OR(!) but it doesn't work... If you just want to say to change the AND with an OR, that's not the good answer. I don't want to select Names that have 'Coffee' or 'Milk', I want to select only Names that have 'Coffee' AND also have 'Milk', the Names that have Both of them.

Bbp

[Anon]

Try this
...
And Item in('Coffee','Tea')

[Anon]

Thanks Noureddine

But that's not the answer...

I try the IN() function and it's act like an OR.

I don't want Name that have 'Coffee' or 'Milk', I want Name that have 'Coffee' AND have 'Milk'.

Something like:
TABLE_A
Id 1, 2, 3, 4
Name Alex, Joe, Marie, Bob

TABLE_B
Id 1, 2, 3, 4, 5, 6
Id_A 1, 1, 2, 3, 4, 4
Item 'Milk', 'Coffee', 'Milk', 'Coffee', 'Milk', 'Coffee'

The select that give only Alex and Bob

Bbp

[Anon]

select A.Name
from TABLE_A as A, TABLE_B as B1, TABLE_B as B2
where A.Id = B1.Id_A
and b1.id_a = b2.id_a
AND b1.Item = 'Coffee'
AND b2.Item = 'Milk'"

[Anon]

YEAH !!!!!!!!!!!!!!!!!!!!!!!!!!

YOU GET IT

Thanks THANKS a lot Lars Berg

You get it!!!

Wow, I so happy...

Now, take a look in the full query, the real query 🙂

$select = "select E.Id, E.Nom, E.NumeroCivique, E.AptCode, E.IdRue,
E.NomContact, E.PrenomContact, E.Telephone, E.Poste,
E.Fax, E.EMail, E.DistanceMin, E.DistanceMax, E.Logo, E.LogoRatio,
E.IdStyle, CP.CodePostal ";

$from = "from Etablissements as E, CodePostal as CP ";

$where = "where E.IdCodePostal = CP.Id ";

$order_by = "";

$group_by = "group by E.Id ";

if (isset($F_Prop))
{
$i=0;
while (list($dummy,$value) = each($F_Prop))
$CheckProp[$i++]=$value;
$fin_check_prop = count($CheckProp);

---------------------------

Your code is here Lars Berg

---------------------------

if ($fin_check_prop)
{

	for ($i=0; $i<$fin_check_prop; $i++)
	{
		$from = $from.", EtabProp as EP".$i." ";
		$where = $where."AND (EP".$i.".IdEtab=E.Id AND EP".$i.".IdProp=$CheckProp[$i])";
	}

if ($F_TypeEtab[0])
{
$from = $from.", EtabType as ET ";
$where = $where."AND ET.IdEtab=E.Id AND (";
$fin_typeetab = count($F_TypeEtab);
for ($i=0; $i<$fin_typeetab; $i++)
{
if ($i)
$where = $where." OR ";
$where = $where."ET.IdType=".$F_TypeEtab[$i];
}
$where = $where.") ";
}

----------- End here -------

if ($Lat_Source || $Long_Source)
{
$select = $select.", 6378ACOS(SIN($A)SIN(CP.Latitude/57.29577951)+COS($A)COS(CP.Latitude/57.29577951)COS($B-(CP.Longitude/57.29577951))) as Distance ";
$order_by = "order by Distance";
}
else
$order_by = "order by E.DistanceMin desc";

$LeEtablissement = @($select.$from.$where.$group_by.$order_by);