CODEWITHSUNDEEP
mysqlsqlrequest
T3ddy
T3ddy

Reputation: 137

SQL Request with mysql

I have two tables order and retailer.

In the order the address is an integer value and represent the id of the retailer.

I need to select the address from the retailer table.

I believe I have to do something like:

Select * from order where (select .....)

I cannot seem to get my syntax right.

EDIT

here is my tables script

CREATE TABLE IF NOT EXISTS `commande` (
  `CO_Id` int(11) NOT NULL,
  `CO_Date` date NOT NULL,
  `CO_Facturation_Adresse` varchar(255) DEFAULT NULL,
  `CO_Livraison_Adresse` varchar(255) DEFAULT NULL,
  `CO_Statut` int(11) DEFAULT NULL,
  `CO_Detaillant` int(11) NOT NULL,
  `CO_Tag` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `CO_Number_Commande` varchar(64) DEFAULT NULL,
  `CO_Amount_Untaxed` float(10,0) DEFAULT NULL,
  `CO_Amount_Tax` float(10,0) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2870 DEFAULT CHARSET=latin1;

and here is my retailer table

CREATE TABLE IF NOT EXISTS `detaillant` (
  `DE_No` int(11) unsigned NOT NULL,
  `DE_Nom` varchar(100) NOT NULL DEFAULT '',
  `DE_DescFR` text,
  `DE_DescEN` text,
  `DE_Site` varchar(100) DEFAULT NULL,
  `DE_Actif` int(11) NOT NULL DEFAULT '1',
  `DE_NoTel` varchar(32) DEFAULT NULL,
  `DE_Adresse` varchar(255) DEFAULT NULL,
  `DE_FkProvince` int(11) unsigned DEFAULT NULL,
  `DE_Ville` varchar(255) DEFAULT NULL,
  `DE_CodePostal` varchar(24) DEFAULT NULL,
  `DE_NoTelSansFrais` varchar(32) DEFAULT NULL,
  `DE_LunDeb` time DEFAULT '08:00:00',
  `DE_LunFin` time DEFAULT '21:00:00',
  `DE_MarDeb` time DEFAULT '08:00:00',
  `DE_MarFin` time DEFAULT '21:00:00',
  `DE_MerDeb` time DEFAULT '08:00:00',
  `DE_MerFin` time DEFAULT '21:00:00',
  `DE_JeuDeb` time DEFAULT '08:00:00',
  `DE_JeuFin` time DEFAULT '21:00:00',
  `DE_VenDeb` time DEFAULT '08:00:00',
  `DE_VenFin` time DEFAULT '21:00:00',
  `DE_SamDeb` time DEFAULT '08:00:00',
  `DE_SamFin` time DEFAULT '21:00:00',
  `DE_DimDeb` time DEFAULT '08:00:00',
  `DE_DimFin` time DEFAULT '21:00:00'
) ENGINE=InnoDB AUTO_INCREMENT=2011 DEFAULT CHARSET=utf8;

CO_Facturation_Adresse,CO_Livraison_Adresse are id retailer, but i want to take back from the retailer the adress and not the id

Upvotes: 1

Views: 32

Answers (2)

BK435
BK435

Reputation: 3174

Try this:

select o.field1, o.field2, r.DE_Adresse from commande AS ord inner join detaillant AS ret ON ord.CO_Facturation_Adresse = ret.DE_No;

I am not sure which fields you are trying to select, but the basic idea is that you select the fields that you want instead of just everything(*). Then join on the the two tables where the ID's match.

Upvotes: 1

Rahul
Rahul

Reputation: 77896

With what you have posted, I think you are trying to use a subquery to get all the retailers id. Something like

Select * from order where address in (
select distinct ID from retailers
)

Upvotes: 0

Related Questions