Ebay API - How to combine two selects with same value but not the same INNER JOIN

valbrlt
August 20, 2021
209 views
0 votes
2 Answers

I have to need a project school, a site like ebay
with bid.
I have to make a select who select

the name of the seller
the name of the last "customer" (bid)

BUT THE PROBLEM is that there two value was in the same table, so for access to the value I need to make 2 select with different INNER JOIN (look the diag for understand)

I have made two selects but I don’t understand how to make a single select.

    SELECT nom AS nameSeller
    FROM ENCHERES e
    INNER JOIN  ARTICLES_VENDUS  ac ON ac.no_article = e.no_article 
    INNER JOIN UTILISATEURS u ON u.no_utilisateur = ac.no_utilisateur
    WHERE nom_article LIKE '%ta%'
    
    SELECT nom AS nameCustomer
    FROM ENCHERES e
    INNER JOIN UTILISATEURS u ON e.no_utilisateur = u.no_utilisateur
    WHERE e.no_utilisateur= 57

what i have with two select

First select

nameCustomer
john

Second select

nameSeller
bryan

What i want in one select

nameSeller	nameCustomer
bryan	john

Fil to the script to create table and bdd

-- Script de création de la base de données ENCHERES
--   type :      SQL Server 2012
--

CREATE DATABASE BDDTEST2
GO
USE BDDTEST2
GO

CREATE TABLE CATEGORIES (
    no_categorie   INTEGER IDENTITY(1,1) NOT NULL,
    libelle        VARCHAR(30) NOT NULL
)

ALTER TABLE CATEGORIES ADD constraint categorie_pk PRIMARY KEY (no_categorie)

CREATE TABLE ENCHERES (
    no_utilisateur   INTEGER NOT NULL,
    no_article       INTEGER NOT NULL,
    date_enchere     datetime NOT NULL,
    montant_enchere  INTEGER NOT NULL

)

ALTER TABLE ENCHERES ADD constraint enchere_pk PRIMARY KEY (no_utilisateur, no_article)

CREATE TABLE RETRAITS (
    no_article         INTEGER NOT NULL,
    rue              VARCHAR(30) NOT NULL,
    code_postal      VARCHAR(15) NOT NULL,
    ville            VARCHAR(30) NOT NULL
)

ALTER TABLE RETRAITS ADD constraint retrait_pk PRIMARY KEY  (no_article)

CREATE TABLE UTILISATEURS (
    no_utilisateur   INTEGER IDENTITY(1,1) NOT NULL,
    pseudo           VARCHAR(30) NOT NULL,
    nom              VARCHAR(30) NOT NULL,
    prenom           VARCHAR(30) NOT NULL,
    email            VARCHAR(20) NOT NULL,
    telephone        VARCHAR(15),
    rue              VARCHAR(30) NOT NULL,
    code_postal      VARCHAR(10) NOT NULL,
    ville            VARCHAR(30) NOT NULL,
    mot_de_passe     VARCHAR(30) NOT NULL,
    credit           INTEGER NOT NULL,
    administrateur   bit NOT NULL
)

ALTER TABLE UTILISATEURS ADD constraint utilisateur_pk PRIMARY KEY (no_utilisateur)


CREATE TABLE ARTICLES_VENDUS (
    no_article                    INTEGER IDENTITY(1,1) NOT NULL,
    nom_article                   VARCHAR(30) NOT NULL,
    description                   VARCHAR(300) NOT NULL,
    date_debut_encheres           DATE NOT NULL,
    date_fin_encheres             DATE NOT NULL,
    prix_initial                  INTEGER,
    prix_vente                    INTEGER,
    no_utilisateur                INTEGER NOT NULL,
    no_categorie                  INTEGER NOT NULL
)

ALTER TABLE ARTICLES_VENDUS ADD constraint articles_vendus_pk PRIMARY KEY (no_article)

ALTER TABLE ARTICLES_VENDUS
    ADD CONSTRAINT encheres_utilisateur_fk FOREIGN KEY ( no_utilisateur ) 
    REFERENCES UTILISATEURS ( no_utilisateur )
ON DELETE NO ACTION 
    ON UPDATE no action 

ALTER TABLE ENCHERES
    ADD CONSTRAINT encheres_articles_vendus_fk FOREIGN KEY ( no_article )
        REFERENCES ARTICLES_VENDUS ( no_article )
ON DELETE NO ACTION 
    ON UPDATE no action 

ALTER TABLE RETRAITS
    ADD CONSTRAINT retraits_articles_vendus_fk FOREIGN KEY ( no_article )
        REFERENCES ARTICLES_VENDUS ( no_article )
ON DELETE NO ACTION 
    ON UPDATE no action 

ALTER TABLE ARTICLES_VENDUS
    ADD CONSTRAINT articles_vendus_categories_fk FOREIGN KEY ( no_categorie )
        REFERENCES CATEGORIES ( no_categorie )
ON DELETE NO ACTION 
    ON UPDATE no action 

ALTER TABLE ARTICLES_VENDUS
    ADD CONSTRAINT ventes_utilisateur_fk FOREIGN KEY ( no_utilisateur )
        REFERENCES UTILISATEURS ( no_utilisateur )
ON DELETE NO ACTION 
    ON UPDATE no action

Link to insert value

USE BDDTEST2
GO

INSERT INTO [dbo].[UTILISATEURS]
           ([pseudo]
           ,[nom]
           ,[prenom]
           ,[email]
           ,[telephone]
           ,[rue]
           ,[code_postal]
           ,[ville]
           ,[mot_de_passe]
           ,[credit]
           ,[administrateur])
     VALUES
           ('zorg'
           ,'john'
           ,'john'
           ,'[email protected]'
           ,'15454'
           ,'hjh'
           ,'hkk'
           ,'hjgjh'
           ,'hjkjg'
           ,0
           ,0 )
GO

INSERT INTO [dbo].[UTILISATEURS]
           ([pseudo]
           ,[nom]
           ,[prenom]
           ,[email]
           ,[telephone]
           ,[rue]
           ,[code_postal]
           ,[ville]
           ,[mot_de_passe]
           ,[credit]
           ,[administrateur])
     VALUES
           ('zorg'
           ,'bryan'
           ,'bryan'
           ,'[email protected]'
           ,'15454'
           ,'hjh'
           ,'hkk'
           ,'hjgjh'
           ,'hjkjg'
           ,0
           ,0 )
GO

INSERT INTO [dbo].[CATEGORIES]
           ([libelle])
     VALUES
           ('enfant')
GO


INSERT INTO [dbo].[ARTICLES_VENDUS]
           ([nom_article]
           ,[description]
           ,[date_debut_encheres]
           ,[date_fin_encheres]
           ,[prix_initial]
           ,[prix_vente]
           ,[no_utilisateur]
           ,[no_categorie])
     VALUES
           ('Jouet',
           'desc',
           '2002-01-01',
           '2003-01-01',
           0,
           0,
           2,
           1)
GO


INSERT INTO [dbo].[ENCHERES]
           ([no_utilisateur]
           ,[no_article]
           ,[date_enchere]
           ,[montant_enchere])
     VALUES
           (1,
          1,
           '2002-02-02',
           50)
GO

Thanks

Answers

lead will move the current row to 1 place above
Union will join data in single column
you need to have a common factor like date for order by clause

https://www.mssqltips.com/sqlservertutorial/9127/sql-server-window-functions-lead-and-lag/

with main_data as (

    SELECT nom AS nameSeller,
    'Seller' as category_seller_or_customer
    FROM ENCHERES e
    INNER JOIN  ARTICLES_VENDUS  ac ON ac.no_article = e.no_article 
    INNER JOIN UTILISATEURS u ON u.no_utilisateur = ac.no_utilisateur
    WHERE nom_article LIKE '%ta%'

    union

    SELECT nom AS nameCustomer,
   'Customer' as category_seller_or_customer
    FROM ENCHERES e
    INNER JOIN UTILISATEURS u ON e.no_utilisateur = u.no_utilisateur
    WHERE e.no_utilisateur= 57
),
getting_seller_and_buyer AS (

select 
*,
 lead(category_seller_or_customer) 
 over(order by 
       [some date factor or something that both have in common]
     ) as current_seller_or_customer

from main_data
)

select 

*,
case 
when category = 'Seller' then current_seller_or_customer else end as seller,

case 
when category = 'Customer' then current_seller_or_customer else end as customer

from 

getting_seller_and_buyer

- JMabee
- August 20, 2021 at 3:08 pm
- 0 votes
0
You can use a CROSS APPLY to pull back the buyer for each of the sellers. Consider the following:
```
 SELECT u.nom AS nameSeller, x.nom nameBuyer
    FROM ENCHERES e
    INNER JOIN  ARTICLES_VENDUS  ac ON ac.no_article = e.no_article 
    INNER JOIN UTILISATEURS u ON u.no_utilisateur = ac.no_utilisateur
CROSS APPLY(
        select u.nom 
        from  UTILISATEURS u WHERE  u.no_utilisateur = e.no_utilisateur
    ) X
```
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

Ebay API – How to combine two selects with same value but not the same INNER JOIN

Answers